MySQL using Inner joins on an alias of a calculated column

Question

I have a query like so:

SELECT User.id, 10*10 as distance
FROM USERS
INNER JOIN
(
    SELECT Location.user_id,
    min(10 * 10) as mindistance
    FROM Location
    GROUP BY Location.user_id
 ) L ON Users.id = Location.user_id AND distance = L.mindistance

If I leave it as is, I keep getting:

Unknown column 'distance' in 'on clause'

But if I put User.distance instead of just distance, I get:

MySQL syntax error near....

Can I not use alias' this way on a calculated field? The 10 * 10 is just a simple placeholder as the calculation is much more complex.

possible duplicate of [Using column alias in WHERE clause of MySQL query produces an error](http://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error) — Joni, Jul 27 '12 at 09:59

Joni · Answer 1 · 2016-05-13T10:05:39.747

You are trying to use a column alias in the ON clause. The MySQL documentation has this to say about this situation:

The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause.

And also:

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined. For example, the following query is illegal:

That said, you can use variables to calculate the expression in the ON clause and then use the value in the column list, like this:

SELECT User.id, @dist as distance
FROM USERS
INNER JOIN
(
    SELECT Location.user_id,
    min(10 * 10) as mindistance
    FROM Location
    GROUP BY Location.user_id
 ) L ON Users.id = Location.user_id AND (@dist := 10*10) = L.mindistance

There's no WHERE clause in the author's SQL. – John Factorial May 12 '16 at 23:18 — John Factorial, May 12 '16 at 23:18

score 10 · Answer 2 · answered Jul 27 '12 at 10:00

To avoid having to make the calculation three times in the query, you can wrap the outer calculation in a FROM subselect, which will give you access to the aliased field name (where it wasn't accessible in your original query):

SELECT a.*
FROM
(
    SELECT id, 10*10 AS distance
    FROM USERS
) a
INNER JOIN
(
    SELECT user_id,
    MIN(10 * 10) AS mindistance
    FROM Location
    GROUP BY user_id
) L ON a.id = L.user_id AND a.distance = L.mindistance

Here, the calculation is only done two times instead of three.

score 1 · Accepted Answer · answered Jul 27 '12 at 09:52

1

SELECT User.id, 10*10 as distance 
FROM USERS 
INNER JOIN 
( 
    SELECT Location.user_id, 
    min(10 * 10) as mindistance 
    FROM Location 
    GROUP BY Location.user_id 
 ) L ON User.id = Location.user_id AND L.mindistance =10*10

answered Jul 27 '12 at 09:52

Madhivanan

13,470
1
24
29

score 1 · Answer 4 · answered Jul 27 '12 at 09:58

You can't used derived values in a query where clause - where is used to restrict records and which indexes to use - derived values can't be used by the optimizer so you need to filter the final results.

not quite sure what you're doing but try something like:

SELECT User.id, 10*10 as distance 
FROM USERS 
INNER JOIN 
( 
    SELECT Location.user_id, 
    min(10 * 10) as mindistance 
    FROM Location 
    GROUP BY Location.user_id 
) L ON User.id = Location.user
HAVING USERS.distance = L.mindistance

MySQL using Inner joins on an alias of a calculated column

4 Answers4