Referencing outer query's tables in a subquery

Question

Is it possible to reference an outer query in a subquery with MySQL? I know there are some cases where this is possible:

SELECT *
FROM table t1
WHERE t1.date = (
    SELECT MAX(date)
    FROM table t2
    WHERE t2.id = t1.id
);

But I'm wondering if something like this could work:

SELECT u.username, c._postCount
FROM User u
INNER JOIN (
    SELECT p.user, COUNT(*) AS _postCount
    FROM Posting p
    --# This is the reference I would need:
    WHERE p.user = u.id
    GROUP BY p.user
) c ON c.user = u.id
WHERE u.joinDate < '2009-10-10';

I know I could achieve the same using a GROUP BY or by pulling the outer WHERE clause into the sub-query, but I need this for automatic SQL generation and cannot use either alternative for various other reasons.

UPDATE: Sorry, the question led to some confusion: The first query is just a working example, to demonstrate what I don't need.

UPDATE 2: I need both u.id = p.user comparisons: The first counts users that joined before '2009-10-10', while the other one is a join condition that associates table rows correctly.

why the update 2? sql will parse the derived table 'c' and get a complete list of all user ids and their post counts. the join `ON c.user = u.id` will then only return users who satisfy the joinDate constraint on `u`. — chris, Apr 15 '10 at 16:19

Jeremy · Answer 1 · 2010-04-15T16:34:28.360

27

Isn't this what you're after?

SELECT u.username, c._postCount
FROM User u
INNER JOIN (
    SELECT p.user, COUNT(*) AS _postCount
    FROM Posting p
    GROUP BY p.user    
) c ON c.user = u.id
WHERE u.joinDate < '2009-10-10';

The reason this will work is that the nature of the join itself will filter on user. You don't need to have a WHERE clause explictly filtering on user.

edited Apr 15 '10 at 16:34

answered Apr 15 '10 at 13:20

Jeremy

4,708
2
20
24

Both joins are are mandatory and serve different purposes - updated the question – soulmerge Apr 15 '10 at 15:19
1

I guess I still don't understand. You don't need the where clause in the middle at all, because the join between c and u filters out all users with no posts, and correctly correlates the records based on userid. I updated the post to reflect what I mean. – Jeremy Apr 15 '10 at 16:35
1

+1 You are right, that was my mistake (oedo was faster, though) – soulmerge Apr 15 '10 at 16:49

score 22 · Accepted Answer · answered Apr 15 '10 at 13:18

22

i think that won't work, because you're referencing your derived table 'c' as part of a join.

however, you could just take out the WHERE p.user = u.id though and replace with a GROUP BY p.user in the derived table, because the ON c.user = u.id will have the same effect.

answered Apr 15 '10 at 13:18

chris

9,335
1
25
26

Thx for the answer. But your query would do something else: It would count all users regardless of join date. *edit*: sorry, looks like I had forgotten the inner group by – soulmerge Apr 15 '10 at 13:25
What if the subquery needed some different (non-`id`) column from the `user` table to do the counting? – mae Aug 31 '18 at 14:02

Rajat Bhatnagar · Answer 3 · 2018-02-12T09:54:17.707

2

This solution is for postgresql. You could use LATERAL JOIN which is available in postgresql. Here is how you could use it in your query.

SELECT u.username, c._postCount
FROM User u
INNER JOIN LATERAL (
    SELECT p.user, COUNT(*) AS _postCount
    FROM Posting p
    WHERE p.user = u.id
    GROUP BY p.user
) c ON c.user = u.id
WHERE u.joinDate < '2009-10-10';

Here is a reference you could use. https://medium.com/kkempin/postgresqls-lateral-join-bfd6bd0199df

edited Feb 12 '18 at 09:54

answered Dec 04 '17 at 08:07

Rajat Bhatnagar

133
4

7

"You could use LATERAL JOIN which is available in postgresql" - how does this help in MySQL? – dchayka Aug 20 '18 at 15:20
6

For all of us arriving from Google where this question matched 90% of our question except for platform. – 4AM Dec 10 '20 at 21:23
1

I came for postgresql too :P – delphaber Mar 04 '21 at 10:47

Shawn Kelly · Answer 4 · 2019-07-05T22:21:21.293

2

This is probably better:

SELECT u.username,
(SELECT COUNT(*) FROM Posting WHERE user = u.id) as _postCount
FROM User u WHERE u.joinDate < '2009-10-10';

edited Jul 05 '19 at 22:21

answered Jul 05 '19 at 16:23

Shawn Kelly

21
2

Lukasz Szozda · Answer 5 · 2020-12-19T12:07:12.530

Is it possible to reference an outer query in a subquery with MySQL?

Yes, it is definitely possible. MySQL 8.0.14 and above:

13.2.11.9 Lateral Derived Tables

A derived table cannot normally refer to (depend on) columns of preceding tables in the same FROM clause. As of MySQL 8.0.14, a derived table may be defined as a lateral derived table to specify that such references are permitted.

SELECT u.username, c._postCount
FROM User u,
LATERAL (
    SELECT p.user, COUNT(*) AS _postCount
    FROM Posting p
    --# This is the reference I would need:
    WHERE p.user = u.id  
    GROUP BY p.user
) c
WHERE u.joinDate < '2009-10-10';

And minified version(removing unnecessary grouping):

SELECT u.username, c._postCount
FROM User u,
LATERAL (
    SELECT COUNT(*) AS _postCount
    FROM Posting p
    WHERE p.user = u.id  
) c
WHERE u.joinDate < '2009-10-10';

Related reading: CROSS/OUTER APPLY in MySQL

score 0 · Answer 6 · answered Dec 19 '20 at 11:45

This is how you do it to expand on the accepted answer

SELECT u.username, c._postCount
FROM User u
INNER JOIN (
    SELECT p.user, COUNT(*) AS _postCount
    FROM Posting p
    --# This is the reference I would need:
    --WHERE p.user = u.id ####REMOVE THIS####
    GROUP BY p.user
) c ON c.user = u.id
WHERE u.joinDate < '2009-10-10'
AND c.user = u.id -- ####ADD THIS####

score -1 · Answer 7 · edited Nov 08 '20 at 15:12

-1

This would work fine

SELECT u.id as userid,u.username, c._postCount
FROM User u
INNER JOIN (
    SELECT p.user, COUNT(*) AS _postCount
    FROM Posting p
    --# This is the reference I would need:
    WHERE p.user = userid
    GROUP BY p.user
) c ON c.user = u.id
WHERE u.joinDate < '2009-10-10';

edited Nov 08 '20 at 15:12

Hugo Elhaj-Lahsen

4,009
3
11
30

answered Nov 07 '20 at 13:49

Aftab Falak

1
3

Referencing outer query's tables in a subquery

7 Answers7

Linked