Posted by Erland Sommarskog on 05/03/07 22:30

Jane T (janet@nospam.net) writes:
> Can someone educate me as to the difference between specifying a condition
> on a join and a condition in a where clause.

As long as you are doing only inner joins it does not matter. When you
do outer joins it matters a lot.

Let's look at this:

> FROM extract a
> LEFT OUTER JOIN allocation b ON a.e_reg_ = b.reg_no_
AND b.period_ = 3
> WHERE a.period_ = 3

You take all rows from extract. Then we add the columns from the
allocation table and fill data into these where there is a match
on the condition

a.e_reg_ = b.reg_no_ AND b.period_ = 3

For non-matching rows, you leave those columns NULL. Finally, you filter
this with the WHERE clause.

> FROM extract a
> LEFT OUTER JOIN allocation b ON a.e_reg_ = b.reg_no_
> WHERE a.period_ = 3
AND b.period_ = 3

Again, you take all rows from extract, and add the columns from allocation.
Now you fill in the data for the rows that match the condition:

a.e_reg_ = b.reg_no_

and leave NULL in the columns from allocation where there is no match. Now
you filter with the WHERE clause. But the WHERE clause has the condition:

b.period_ = 3

And the rows with NULL in b.period_ does not fulfil this condition,
and thus rows are filtered out.

More generally, the FROM JOIN clauses builds a table, and then the
WHERE clause filters that table.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]