Watch those join clauses.

One of the statements I’ve heard a couple of times over the last couple of years, is that it doesn’t matter where you place your additional on-clauses when joining, meaning either in “on” or in “where”. Well it does, depending on what you want to extract of information from your database. Performance wise and result wise.

An easy way to show this is to take two tables.
Suppose I have table (“TableOne”) consisting of a TableOnePK (primary key) and a value, and a table two “TableTwo” consisting of TableOneFK (foreign key) and a value.

If I then do a left join like:

SELECT *
FROM TableOne T1
LEFT JOIN TableTwo T2 on T1.TableOnePK = T2.TableOneFK AND T2.TableTwoValue = <target value>

This will get you all the rows from TableOne with nulls for the left join where it didn’t match anything in TableTwo. Pretty much as you would expect.

Now change the statement to

SELECT *
FROM TableOne T1
LEFT JOIN TableTwo T2 on T1.TableOnePK = T2.TableOneFK
WHERE T2.TableTwoValue = <target value>

And now you’ll filter TableTwo prior to the left join and then left join that result into your TableOne, meaning you’ll get a vastly different result then the first one.

Thus one need to consider such things when doing joins as if not careful, it is possible to get a vastly different join result then otherwise intended.