WHERE clause before INNER JOIN

Question

If I have

SELECT * FROM Table1 t1 
LEFT JOIN Table2 t2 ON t1.id = t2.id 
WHERE t1.user='bob';

Does the WHERE clause run after the two tables are JOINED?

How do I make it so it runs prior to the JOIN?

The answers given are correct, but it's worth delving a little deeper. Why are you looking for this behavior? I'd imagine the query optimizer would handle the obvious case for you... — Dan J, Apr 12 '12 at 23:47

Mosty Mostacho · Answer 1 · 2015-07-03T17:39:01.660

114

The where clause will be executed before the join so that it doesn't join unnecessary records. So your code is fine the way it is.

edited Jul 03 '15 at 17:39

answered Apr 12 '12 at 23:50

Mosty Mostacho

41,294
14
93
121

9

@user1124535 I can confirm. Run the `explain` query and you'll see. – Mosty Mostacho Apr 13 '12 at 04:04
3

This is the better answer. – myusuf3 Dec 24 '15 at 02:21

score 33 · Accepted Answer · answered Apr 12 '12 at 23:41

33

Change the WHERE to another JOIN condition

LEFT JOIN Table2 t2 on t1.id = t2.id AND t1.user='bob'

answered Apr 12 '12 at 23:41

hkf

4,312
1
28
44

score 2 · Answer 3 · answered Sep 27 '12 at 17:00

In my experience in a left join you cannot exclude records in the 'left' (t1) table in the ON-statement since - by definition - all t1 records will be included. The where statement does work as it will be applied to the result of the join afterwards.

I do not exactly know what you want to achieve but most probably an inner join suits your needs as well and then you can add the t1.user='bob' condition to the ON-statement.

But if Mosty Mostacho is correct, the location (WHERE vs ON) of the condition is not relevant for speed of execution.

score 1 · Answer 4 · answered Jan 14 '19 at 08:05

1

You should just add t1.user='bob' condition to ON clause before other condition and it will be evaluated first:

SELECT * FROM Table1 t1 
LEFT JOIN Table2 t2
ON t1.user='bob' AND t1.id = t2.id;

answered Jan 14 '19 at 08:05

Ivan Olshansky

774
1
17
19

score 1 · Answer 5 · answered Apr 19 '19 at 10:14

1

What you may use is table expression after FROM like this:

SELECT *
FROM (SELECT
        id
    FROM Table1
    WHERE user = 'bob') AS t1
LEFT JOIN Table2 t2
    ON t1.id = t2.id

answered Apr 19 '19 at 10:14

Georgi Mirchev

211
2
5

g3rv4 · Answer 6 · 2012-10-02T19:20:21.800

0

you can do

SELECT * 
    FROM Table1 t1 
    LEFT JOIN Table2 t2
        ON t1.id=t2.id AND t1.user='bob';

edited Oct 02 '12 at 19:20

answered Apr 12 '12 at 23:42

g3rv4

19,460
4
33
58

score 0 · Answer 7 · answered Mar 08 '14 at 02:59

RIGHT JOIN was the solution:

SELECT cars.manufacturer, cars.year FROM cars 
RIGHT JOIN (SELECT m.manufacturer FROM cars AS m ORDER BY m.year DESC LIMIT 3) subq 
ON cars.manufacturer=subq.manufacturer

Haven't put it through the full rigors yet, but seems to work.

WHERE clause before INNER JOIN

7 Answers7

Linked