THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Five Different Ways To Return Data From One Table Which Doesn't Exists In another Table

There are at least 5 ways to return data from one table which is not in another table. Two of these are SQL Server 2005 and greater only. This is a post mostly for beginners but hopefully everyone will get something out of it.

Here are the 5 different ways

NOT IN
NOT EXISTS
OUTER JOIN
OUTER APPLY (2005+)
EXCEPT (2005+)

Let's see how this all works
First create these two tables with the Celko approved naming convention.

 

CREATE TABLE #testnulls (ID INT)

INSERT INTO #testnulls VALUES (1)

INSERT INTO #testnulls VALUES (2)

INSERT INTO #testnulls VALUES (null)

 

CREATE TABLE #testjoin (ID INT)

INSERT INTO #testjoin VALUES (1)

INSERT INTO #testjoin VALUES (3)

NOT IN
Run the following Code

 

SELECT * FROM #testjoin WHERE ID NOT IN(SELECT ID FROM #testnulls)

What happened? Nothing gets returned! The reason is because the subquery returns a NULL and you can't compare a NULL to anything

Now run this

SELECT * FROM #testjoin

WHERE ID NOT IN(SELECT ID FROM #testnulls WHERE ID IS NOT NULL)

That worked because we eliminated the NULL values in the subquery

This also works

SELECT * FROM #testjoin j

WHERE j.ID NOT IN(SELECT ID FROM #testnulls n WHERE n.ID = j.ID)

 


NOT EXISTS
NOT EXISTS doesn't have the problem that NOT IN has. Run the following code

 

SELECT * FROM #testjoin j

WHERE NOT EXISTS (SELECT 1

FROM #testnulls n

WHERE n.ID = j.ID)

Everything worked as expected


LEFT and RIGHT JOIN
Plain vanilla LEFT and RIGHT JOINS

 

SELECT j.* FROM #testjoin j

LEFT OUTER JOIN #testnulls n ON n.ID = j.ID

WHERE n.ID IS NULL

With a RIGHT Join you just switch the tables around

SELECT j.* FROM #testnulls n

RIGHT OUTER JOIN #testjoin j ON n.ID = j.ID

WHERE n.ID IS NULL

 

And we can also do a full outer join

SELECT j.* FROM #testnulls n

FULL OUTER JOIN #testjoin j ON n.ID = j.ID

WHERE n.ID IS NULL

AND j.ID IS NOT NULL


You might wonder why we have LEFT and RIGHT Joins, here is why:
<AttemptToBeFunny>LEFT joins are for people who tend to vote for the democrats, RIGHT joins are for people who tend to vote for Republicans. FULL Joins are for independents/undecided people. </AttemptToBeFunny>

You can be real silly and do a subquery LEFT join
 

SELECT j.* FROM #testjoin j

LEFT OUTER JOIN (SELECT ID FROM #testnulls ) n ON n.ID = j.ID

WHERE n.ID IS NULL

 

Now let's talk about SQL 2005 and up

OUTER APPLY (SQL 2005 +)
OUTER APPLY is something that got added to SQL 2005

SELECT j.* FROM #testjoin j

OUTER APPLY

(SELECT id FROM #testnulls n

WHERE n.ID = j.ID) a

WHERE a.ID IS NULL

 

EXCEPT(SQL 2005 +)
EXCEPT is something that got added to SQL 2005. It basically returns everything from the top table which is not in the bottom table

 

SELECT * FROM #testjoin

EXCEPT

SELECT * FROM #testnulls


I am also mentioning INTERSECT since some people might not have seen it before. INTERSECT returns what ever is in both tables(like a regular join)

 

SELECT * FROM #testjoin

INTERSECT

SELECT * FROM #testnulls

 

So there you have it, most likely you already know all these types of joins. If you learned something from this post that is a good thing also.

Published Wednesday, February 20, 2008 11:36 AM by Denis Gobo
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Vamsi said:

Is there any performance gain by using EXCEPT instead of NOT EXISTS on large tables with millions of rows?

February 21, 2008 8:54 AM
 

Denis Gobo said:

Vamsi,

No there is no performance gain at all.

EXCEPT is more expensive because a sort happens, EXISTS does not do a sort

February 21, 2008 10:10 AM
 

Alejandro Mesa said:

In order to compare both statements, the one using EXCEPT and the one using NOT EXISTS, we need to add DISTINCT to the one using NOT EXISTS, so we can have same result. EXCEPT return disintct rows.

USE tempdb

GO

DECLARE @t1 TABLE (c1 INT)

DECLARE @t2 TABLE (c1 INT)

INSERT INTO @t1(c1) VALUES(1)

INSERT INTO @t1(c1) VALUES(1)

INSERT INTO @t1(c1) VALUES(2)

INSERT INTO @t1(c1) VALUES(2)

INSERT INTO @t1(c1) VALUES(3)

INSERT INTO @t1(c1) VALUES(3)

SELECT c1

FROM @t1 AS t1

WHERE NOT EXISTS (SELECT * FROM @t2 AS t2 WHERE t2.c1 = t1.c1)

SELECT DISTINCT c1

FROM @t1 AS t1

WHERE NOT EXISTS (SELECT * FROM @t2 AS t2 WHERE t2.c1 = t1.c1)

(SELECT c1 FROM @t1)

EXCEPT

(SELECT c1 FROM @t2)

GO

AMB

February 21, 2008 11:28 AM
 

Srikanth said:

February 23, 2008 11:26 AM
 

jerryhung said:

I like EXCEPT for quick easy debug

it's essentially the MINUS in Oracle which was useful

But overall I like NOT EXISTS the best

1st step to optimize slow code if it has NOT IN

February 28, 2008 3:01 PM
 

Elad said:

That is all good. I like the "except" the most. But there is another problem. If i want to get all the rows that exist (Table1 - Table2) without the answer being distinct, what do i do?

Example:

Table1:

Products: A, B, C, D, D, D, E

Table2:

Products: A, C

select * from Table1

except

select * from table2

will give me: B, D, E

But i want it to give me: B, D, D, D, E

Any way that would work will be helpful. Thanks in advance.

March 12, 2008 6:02 AM
 

Denis Gobo said:

Wow, it has been already a year since I wrote A year in review, The 21 + 1 best blog posts on SQLBlog

December 31, 2008 10:37 AM
 

wajid said:

nice i like joins

April 12, 2012 3:49 AM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement