Help me understand this Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help me understand this Query

Author

Topic

JacobPressures
Posting Yak Master

112 Posts

Posted - 2009-04-17 : 18:07:53

Help me understand the differences in these two queries. With the first query the restriction is after the JOIN...ON clause. The other restriction is in the WHERE clause. Both restrictions are supposed to provide the same results but the WHERE clause returns 1843 records. The restriction in the JOIN...ON clause returns exactly the right number 4484. I was always taught to restrict using the WHERE clause. I thought that this JOIN...ON variation was a equal alternative--but maybe not as standard. So what is going on? What is the difference? Scary after all these years and all the books I've read, I've never been taught this!

SELECT
sc.StudentID,
scp.LastName,
scp.FirstName,
ROW_NUMBER() OVER(PARTITION BY sc.StudentID ORDER BY scp.LastName, scp.FirstName) AS rn

FROM dbo.StudentContact sc
INNER JOIN dbo.Person scp ON scp.PersonID = sc.ContactID
LEFT OUTER JOIN dbo.Address addr ON addr.AddressID = scp.MailingAddressID
LEFT OUTER JOIN dbo.PersonTelephone pt ON pt.PersonID = scp.PersonID AND pt.Description = 'Home'
WHERE sc.IsCustodian = 1 --AND pt.Description = 'Home'

SELECT
sc.StudentID,
scp.LastName,
scp.FirstName,
ROW_NUMBER() OVER(PARTITION BY sc.StudentID ORDER BY scp.LastName, scp.FirstName) AS rn

FROM dbo.StudentContact sc
INNER JOIN dbo.Person scp ON scp.PersonID = sc.ContactID
LEFT OUTER JOIN dbo.Address addr ON addr.AddressID = scp.MailingAddressID
LEFT OUTER JOIN dbo.PersonTelephone pt ON pt.PersonID = scp.PersonID --AND pt.Description = 'Home'
WHERE sc.IsCustodian = 1 AND pt.Description = 'Home'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-17 : 18:15:55

If that was an INNER JOIN, then it wouldn't matter where the restriction was palced. However, because it is an OUTER JOIN it can (and does) make a difference.

Take a look at this post I made a long while back. It should help to illistrate the difference between those two cases: [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98481#374748[/url]

JacobPressures
Posting Yak Master

112 Posts

Posted - 2009-04-17 : 18:18:44

Basically What I'm saying is I started out with 4484 records. I did a left join and was expecting the same number of records with the restriction in the WHERE clause. But what i got instead was 1843 records instead! I placed the same restriction on the JOIN...ON clause and got teh right number -- 4484.

JacobPressures
Posting Yak Master

112 Posts

Posted - 2009-04-17 : 18:19:34

Thanks Lamprey! I'll check it out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources