EASY T-SQL exclusion question - 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)

EASY T-SQL exclusion question

Author

Topic

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-05-20 : 15:04:07

I have two different views which I've linked together based on 3 different columns. Currently they're linked w/ an INNER JOIN.

What I need to do now, though, is figure out what records exist in one view and not the other. How is this done? I believe I did something like this before where I used an OUTER JOIN and then a "____ is NULL" condition in my WHERE clause, but I can't remember exactly what was going on.

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-20 : 15:11:53

SELECT t1.*
FROM Table1 t1
LEFT OUTER JOIN Table2 t2
ON t1.Column1 = t2.Column1
WHERE t2.Column1 IS NULL

To do the reverse, just swap the tables or use this to do both:

SELECT t1.Column1, t2.Column1
FROM Table1 t1
FULL OUTER JOIN Table2 t2
ON t1.Column1 = t2.Column1
WHERE t1.Column1 IS NULL OR t2.Column1 IS NULL

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-05-20 : 16:32:49

Wouldn't this be the reverse of what you did?

SELECT t1.*
FROM Table1 t1
RIGHT OUTER JOIN Table2 t2
ON t1.Column1 = t2.Column1
WHERE t1.Column1 IS NULL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-20 : 16:35:41

I make it a point to not use right joins as I don't want Jeff Smith to tell me how bad my SQL is!

He's got a blog on this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources