Where to put conditions in Multiple Joins?

Question

Say if I have this query

SELECT TableA.Id, TableA.Number, TableA.Name, TableA.HOl, TableB.Contact, TableC.activity
FROM TableA
left JOIN TableB on TableA.Id = TableB.TableA_Id 
left join TableC on TableB.userid = TableC.userid
where TableA.hol = 50 
order By TableA.Id

Is it better to but the TableA.Hol in the where or in the ON clause?

I am not sure if it makes a difference, I am trying to determine why it slow. Maybe it something else with my joins?

Possible duplicate of [Conditions in LEFT JOIN (OUTER JOIN) vs INNER JOIN](https://stackoverflow.com/questions/30977119/conditions-in-left-join-outer-join-vs-inner-join) — philipxy, Mar 07 '18 at 22:00
How can we know whether "it is better"? All you give is a query & all you say is you want "multiple joins". Read & act on [mcve]. If your query returns the correct answer then re moving around your where condition: You really need to find out what left join returns--inner join rows plus unmatched left table rows extended by nulls. With a series of inner joins you can move conditions between on & where (assuming names are in scope.) But not if there are outer joins since it affects what null-extended rows are returned. Re "slow": That is a different question--for after you get a correct query. — philipxy, Mar 07 '18 at 22:08

score 0 · Accepted Answer · answered Mar 07 '18 at 21:20

0

This is your query:

select TableA.Id, TableA.Number, TableA.Name, TableA.HOl, TableB.Contact, TableC.activity
from TableA left join
     TableB
     on TableA.Id = TableB.TableA_Id left join
     TableC
on TableB.userid = TableC.userid
where TableA.hol = 50 
order By TableA.Id;

A left join keeps all rows in the first table, regardless of what the on clause evaluates to. This means that a condition on the first table is effectively ignored in the on clause. Well, not exactly ignored -- the condition is false so the columns from the second table will be NULL for those rows.

So, filters on the first table in a left join should be in the where clause.

Conditions on subsequent tables should be in the on clause. Otherwise, those conditions will turn the outer join into an inner join.

answered Mar 07 '18 at 21:20

Gordon Linoff

1,242,037
58
646
786

ok, so right now my query is correct then? Any tips on how to make it faster? It takes a long time to run. – chobo2 Mar 07 '18 at 21:23
Also, does the group by always go outside of the joins as well? – chobo2 Mar 07 '18 at 21:39
@chobo2 . . . presumably, you need an index on `tableA(hol, id)`, and on the join columns in the other tables. – Gordon Linoff Mar 08 '18 at 04:11

score 0 · Answer 2 · edited Mar 10 '18 at 07:43

0

SELECT A.Id, A.Number, A.Name, A.HOl, B.Contact, C.activity
FROM TableA A
LEFT OUTER JOIN TableB B
ON (A.Id = B.TableA_Id)
LEFT OUTER JOIN TableC C
ON (B.userid = C.userid)
AND A.hol = 50
ORDER BY A.Id

If you are referencing more than one table you can use an alias which improves readability. But this has nothing to do with performance.

edited Mar 10 '18 at 07:43

philipxy

14,867
6
39
83

answered Mar 08 '18 at 10:33

Sas

278
1
12

Index all the predicates in JOIN, WHERE, ORDER BY clauses. remove unnecessary columns this will help you in query optimization – Sas Mar 08 '18 at 10:38
Please explain in your post how it answers the question. Here you are moving a test from a where to an on but in what way does this answer the question, which is about *why* & *whether* this should be done. So this is a poor answer. PS Please read the edit help & look at the formatted version below the edit box. Click on 'edited' to see how I edited your post to format code. Edit clarifications/additions seamlessly into your posts, not comments. – philipxy Mar 10 '18 at 07:42

score 0 · Answer 3 · answered Mar 10 '18 at 16:54

Regardless of whether you are using JOIN or LEFT JOIN, use ON to specify how the tables are related and use WHERE to filter.

In the case of JOIN, the it does not matter where you put the filtering; it is for readability that you should follow the above rule.

In the case of LEFT JOIN, the results are likely to be different.

If you do

EXPLAIN EXTENDED SELECT ...
SHOW WARNINGS;

you can see what the SQL parser decided to do. In general, it moves ON clauses are to WHERE, indicating that it does not matter (to the semantics) which place they are. But, for LEFT JOIN, some things must remain in the ON.

Note another thing:

FROM a ...
LEFT JOIN b ...
WHERE b.foo = 123

effectively throws out the LEFT. The difference between LEFT and non-LEFT is whether you get rows of b filled with NULLs. But WHERE b.foo = 123 says you definitely do not want such rows. So, for clarity for the reader, do not say LEFT.

So, I agree with your original formulation. But I also like short aliases for all tables. Be sure to qualify all columns -- the reader may not know which table a column is in.

Your title says "multiple" joins. I discussed a single JOIN; the lecture applies to any number of JOINs.

Where to put conditions in Multiple Joins?

3 Answers3