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?

Junejuneau

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... – Ocasio

Answer 1

Change the WHERE to another JOIN condition

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

Brownnose

Answer 2

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.

Pledget

@Junejuneau I can confirm. Run the explain query and you'll see. – Pledget

This is the better answer. – Michaelmas

Answer 3

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.

Engagement

Answer 4

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

Lyndell

Answer 5

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;

Nourish

Answer 6

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

Klaipeda

Answer 7

you can do

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

Sip

Answer 8

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.

Legumin