About Join OR Where

[mariohiga]

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

I appreciate any idea
Thanks a lot

[Hugo Kornelis]

On 14 Feb 2006 14:09:09 -0800, mariohiga wrote:

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

I appreciate any idea
Thanks a lot

Hi mariohiga,

For an INNER JOIN, there's no difference. Use what you prefer best.

I'd choose the latter, because I like to keep the join conditions in the
JOIN clauses and the filter conditions in the WHERE clauses - but that's
purely personal preference.

--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

mariohiga (ma*******@gmai l.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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[mariohiga]

Thanks MVPs!! really thanks!