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

122

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

42,742
16
96
123

10

@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. – Mahdi Yusuf Dec 24 '15 at 02:21

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

36

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,440
1
30
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

889
2
17
23

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

261
3
6

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,750
4
36
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.

score 0 · Answer 8 · edited Jul 03 '23 at 01:20

order of conditions in the ON clause has no impact. SQL query optimizer will evaluate and execute conditions in an optimized manner, regardless of their order

So

SELECT t1.column1, t1.column2, t2.column3
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.user = 'bob' AND t2.age = 25 AND t1.id = t2.id;

OR

SELECT t1.column1, t1.column2, t2.column3
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.id = t2.id AND t1.user = 'bob' AND t2.age = 25;

ARE SAME in terms of result and the query execution

WHERE clause before INNER JOIN

8 Answers8

Linked