JOINS and WHERE clause

hello,
I was curious and wanted to know if there was a difference between the
following statements.
SELECT FirstName, LastName
FROM Person
INNER JOIN Address on Person.PersonID = Address.PersonID AND
Address.AddressType = 'Home'
and:
SELECT FirstName, LastName
FROM Person
INNER JOIN Address on Person.PersonID = Address.PersonID
WHERE
Address.AddressType = 'Home'
thank you for your help
BenDid you compare the execution plans? Did you trace the execution of each
query in SQL Profiler? (Use dbcc freeproccache between each take.)
ML
http://milambda.blogspot.com/|||You will get the same results, as for the performance:
Is Person a table or a view? If a view, how complex is it?|||The answer is yes, and no. Yes, these two queries are mathematically
equivalent. However, they are asking two different questions. Not as bad
as what color is the sky and what color is a Marge Simpson's hair, but
different questions.
The FROM clause builds a working set of data based on the JOINS. The WHERE
clause critieria is applied to each row from that set individually and rows
removed accordingly.
In your two queries:
* The first is building a set of rows by joining person and address,
looking for rows where the personIds match and address.addressType is home.
* The second is building a set of rows by joining person and address,
looking for rows where the personIds match. Then it is going back over this
set and tossing out any where Address.AddressType is not home.
Why this is significant is not so much for queries like this, in completely
INNER JOIN queries the results will always be the same. (someone correct me
if there is a case where it can be different) However, when OUTER JOINs are
involved, things get much more interesting. Say this was and OUTER JOIN:
SELECT FirstName, LastName
FROM Person
LEFT OUTER JOIN Address on Person.PersonID = Address.PersonID
WHERE Address.AddressType = 'Home'
So when the set is assembled, all rows in the Person table will be included
in the output, and only address rows where there is a match.
Well, when the WHERE clause is applied to the results of the FROM clause,
person rows that did not have addresses would fall out of the set, since the
addressType of the null row is null, not home.
So the point is that you need to query the table like you mean it, if you
are shaping the set of data with your criteria, it goes in the FROM clause,
if it is eliminating rows from the set, put it in the WHERE clause.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
<dreamer@.gmail.com> wrote in message
news:1138136404.172038.122140@.g47g2000cwa.googlegroups.com...
> hello,
> I was curious and wanted to know if there was a difference between the
> following statements.
> SELECT FirstName, LastName
> FROM Person
> INNER JOIN Address on Person.PersonID = Address.PersonID AND
> Address.AddressType = 'Home'
> and:
> SELECT FirstName, LastName
> FROM Person
> INNER JOIN Address on Person.PersonID = Address.PersonID
> WHERE
> Address.AddressType = 'Home'
> thank you for your help
> Ben
>