|
|
Posted by Erland Sommarskog on 02/15/06 00:37
mariohiga (mariohiga@gmail.com) writes:
> Hello
> I've a really big doubt :) I've this two alternatives
>
> SELECT *
> FROM T1 a
> INNER JOIN T1 b
> ON a.F1 = b.F1
> AND a.F2 = 1
>
> SELECT *
> FROM T1 a
> INNER JOIN T1 b
> ON a.F1 = b.F1
> WHERE a.F2 = 1
>
> I don't know when I've use a.F2 = 1
> 1) In the INNER JOIN statement OR
> 2) In the WHERE condition
So in this example, it's only a matter of esthetics. The result will
always be the same. However consider this pair of queries:
SELECT ...
FROM A
LEFT JOIN B ON A.col = B.col
AND B.othercol = 1
SELECT ...
FROM A
LEFT JOIN B ON A.col = B.col
WHERE B.othercol = 1
Here it matters a lot, and you will get different result. Here is why:
The FROM clause runs all the way from FROM to WHERE, and by applying
join opertors it builds a virtual table, and then the WHERE clause
filters that table.
In both these queries, the virtual tables inlucdes all rows from A.
In the first query the virtual table includes all rows from B where
B.col = A.col AND b.othercol = 1. For other rows in A, there is a
NULL in the columns from B.
Whereas in the second query, the virtual table has more rows with
data in B, to wit all where B.col is = A.col, no matter the value
of B.othercol. But then we filter that table on B.othercol = 1.
Which means all rows with B.othercol NULL goes out the window -
and with them all rows in A that did not have a matching row in
B! This is a very common error with the LEFT JOIN operator, that about
everyone makes - at least I did it when I started to use this operator!
The same computation rules apply to your original queries with INNER
JOIN, but here the actual result is the same.
Note also that the computation rules I have described are logical only.
The optimizer may recast computation order as long as it does not
change the result.
--
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]
|