FIltering on the join?

Question

Is there any argument, performance wise, to do filtering in the join, as opposed to the WHERE clause?

For example,

SELECT blah FROM TableA a
INNER JOIN TableB b
ON b.id = a.id
AND b.deleted = 0
WHERE a.field = 5

As opposed to

SELECT blah FROM TableA a
INNER JOIN TableB b
ON b.id = a.id
WHERE a.field = 5
  AND b.deleted = 0

I personally prefer the latter, because I feel filtering should be done in the filtering section (WHERE), but is there any performance or other reasons to do either method?

duplicate of [WHERE Clause vs ON when using JOIN](https://stackoverflow.com/questions/10297231/where-clause-vs-on-when-using-join) (yes, that involves time travel but at a glance has slightly better answers) — underscore_d, Nov 22 '17 at 10:52

score 46 · Accepted Answer · answered Feb 09 '11 at 03:14

46

If the query optimizer does its job, there is no difference at all (except clarity for others) in the two forms for inner joins.

That said, with left joins a condition in the join means to filter rows out of the second table before joining. A condition in the where means to filter rows out of the final result after joining. Those mean very different things.

answered Feb 09 '11 at 03:14

btilly

43,296
3
59
88

1

The original poster uses an inner join, not a left join. As he wrote it, there is no problem. – Chogg Mar 27 '18 at 18:54
4

@Chogg The use of an inner join in the example does not necessarily mean that the question was only about inner joins. – btilly Mar 27 '18 at 20:30

score 1 · Answer 2 · edited Aug 14 '16 at 21:34

No there is no differences between these two, because in the logical processing of the query, WHERE will always go right after filter clause(ON), in your examples you will have:

Cartesian product (number of rows from TableA x number of rows from TableB)
Filter (ON)
Where.

Your examples are in ANSI SQL-92 standard, you could also write the query with ANSI SQL-89 standard like this:

SELECT blah FROM TableA a,TableB b
WHERE b.id = a.id AND b.deleted = 0 AND a.field = 5

THIS IS TRUE FOR INNER JOINS, WITH OUTER JOINS IS SIMILAR BUT NOT THE SAME

score 1 · Answer 3 · answered Feb 09 '11 at 21:34

With inner joins you will have the same results and probably the same performance. However, with outer joins the two queries would return different results and are not equivalent at all as putting the condition in the where clause will in essence change the query from a left join to an inner join (unless you are looking for the records where some field is null).

FIltering on the join?

3 Answers3