MySQL referencing nested query result in the SELECT?

Question

Say you have two tables with columns:

table1: id, data
table2: id, t1_ref (which is a FK to table1), some_other_column

Now I can write something like (I know that this can be written differently, more efficient, etc, not looking for those):

SELECT t1.data,
   (SELECT count(*)
    FROM table2 t2
    WHERE t2.t1_ref = t1.id) AS nested_result
FROM table1 t1;

My question is, where can I use 'nested_result' in the rest of the main query? Can I use it in the FROM (in another nested select for instance)? Or in the WHERE? Or in a GROUP BY? Or on a ORDER BY? Anywhere else?

For example MySQL doesn't seem to like:

SELECT t1.data,
   (SELECT count(*)
    FROM table2 t2
    WHERE t2.t1_ref = t1.id) AS nested_result
FROM table1 t1
WHERE nested_result > 100;

but what are the general rules here?

Gordon Linoff · Accepted Answer · 2013-09-12 22:28:26Z

2

nested_result is a column alias.

You can use it in the group by, having, and order by clauses.

You can put this whole statement in a subquery, and use it in the outer query.

Here is the reference in the documentation:

The following list provides additional information about other SELECT clauses:

A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses. For example:
SELECT CONCAT(last_name,', ',first_name) AS full_name
FROM mytable
ORDER BY full_name;

EDIT:

For your particular example, you can change the where to having:

SELECT t1.data,
       (SELECT count(*)
        FROM table2 t2
        WHERE t2.t1_ref = t1.id
       ) AS nested_result
FROM table1 t1
HAVING nested_result > 100;

This is a MySQL extensions and doesn't work in other databases. As much as I don't like it, I have to admit that it is convenient.

edited Sep 12, 2013 at 22:28

answered Sep 12, 2013 at 22:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ivaylo_iliev Over a year ago

But not in the WHERE?

Gordon Linoff Over a year ago

@pepper_bg . . . and not in the WHERE.

GarethD Over a year ago

Not the WHERE, and as far as I know it is not the SQL-Standard to be able use a column alias in the group by or having clauses, it is only MySQL that allows this (nothing against the answer as the question is about MySQL, just thought it worth noting since the question asks for general rules). General rules would be as suggested, use a subquery, or wait for MySQL to implement LATERAL JOIN (or APPLY in SQL-Server)

ivaylo_iliev Over a year ago

I think Gordon got what I meant by "general rules". Thanks for the complete answer, Gordon! Even guessed my main motivation (using it in the WHERE). I had a little trepidation doing a HAVING without a GROUP BY but for once thank MySQL for being permissive...

Collectives™ on Stack Overflow

MySQL referencing nested query result in the SELECT?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related