By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,573 Members | 1,223 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,573 IT Pros & Developers. It's quick & easy.

About Join OR Where

P: n/a
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

Feb 14 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Feb 14 '06 #2

P: n/a
mariohiga (ma*******@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, es****@sommarskog.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
Feb 14 '06 #3

P: n/a
Thanks MVPs!! really thanks!

Feb 15 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.