CODEWITHSUNDEEP
mysqlsqlaliasselfambiguity
Rob
Rob

Reputation: 37

How do you resolve ambiguous aliases in MySQL to the current table's definitions

I was wondering if MySQL has some kind of default alias for the current table you are building (like 'self' in SmallTalk or 'this' in Java). For instance:

SELECT 
SUM(revenue) AS revenue,
SUM(units) AS units,
SUM(revenue)/SUM(units) AS revPerUnit,
-- I want to replace the above line with the below line:
self.revenue/self.units AS revPerUnit2
FROM inputTable;

I know that I could just use unabiguous aliases, but I want to know if there is a way to specify that you want the current table's alias when there exists an ambiguous definition.

Upvotes: 2

Views: 2794

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

What you describe is not possible in SQL for a very specific reason. In a SELECT list, all columns are to de calculated (created, resolved) at the same time or as if they were calculated (created, resolved) at the same time. Or in other words, produce the same result in whatever order they are calculated.

Which means no column alias can be used (referenced) in another column's calculation.

If what you describe was possible, we could have circular references of the type:

SELECT SUM(a)*sb AS sa
     , SUM(b)*sa AS sb 
FROM aTable

which off course should be rejected as an error. And that would be rather easy by adding a level in the query parser that checks for circular references (exists circular reference: reject query)

But how should the result of this be calculated: 

SELECT SUM(a) AS b
     , SUM(b) AS a
FROM aTable

or why should it be rejected?

If there are no columns a and b in the table, OK, that would be easy, (exists circular reference: reject query). But if there are columns a and b, is it a circular reference or not? Is b in SUM(b) an ambiguous reference? Does it refer to column b or to alias b ?

The second is a valid SQL query, by the way. Because SQL assumes that all calculations are done (SUM(a) and SUM(b) here) and then the aliases are assigned to the results. It's the same with:

UPDATE aTable
SET a=b
  , b=a

which actaully swaps data in the two columns. When you write it this way, it's not very clear, until you try it out. There's an alternative prettier way that gives better the idea of what is happening in the background (not all RDBMS support this yet):

UPDATE aTable
SET (a,b)=(b,a)

Upvotes: 1

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

Usually, this is what I do on these cases:

SELECT revenue, 
       units, 
       revenue / units AS revperunit2 
FROM   (SELECT SUM(revenue) AS revenue, 
               SUM(units)   AS units 
        FROM   inputtable) subsel

Sometimes you have complicated queries and re-declaring the fields is not good for readability neither is for performance. Sub queries is the way to go.

Upvotes: 3

Related Questions