|
|
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
Navigation:
[Reply to this message]
|