IN vs EXISTS - 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

General SQL Server Forums

New to SQL Server Programming

IN vs EXISTS

Author

Topic

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2013-02-12 : 16:46:40

Am wondering what is the difference between these two? They both seem like subselects but since they are seperate operations, am thinking there must be a difference.

eg

SELECT O.OrderID FROM tblOrders AS O
WHERE O.CustID IN (SELECT * FROM tblCustomers AS C WHERE C.CustID = 1000)

SELECT O.OrderID FROM tblOrders AS O
WHERE EXISTS (SELECT * FROM tblCustomers AS C WHERE C.CustID = 1000)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-12 : 17:25:49

They are logically different.

CREATE TABLE #tblOrders(orderid INT, custid INT);
CREATE TABLE #tblCustomers( custid INT );

INSERT INTO #tblOrders VALUES (1,50),(2,60);
INSERT INTO #tblCustomers VALUES (1000);

-- this returns no rows
SELECT O.OrderID FROM #tblOrders AS O
WHERE O.CustID IN (SELECT * FROM #tblCustomers AS C WHERE C.CustID = 1000)

-- this returns all the rows in #tblOrders
SELECT O.OrderID FROM #tblOrders AS O
WHERE EXISTS (SELECT * FROM #tblCustomers AS C WHERE C.CustID = 1000)

DROP TABLE #tblOrders,#tblCustomers

Perhaps you are thinking about correlated subqueries?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 05:09:05

I think what you're looking at is


SELECT O.OrderID FROM #tblOrders AS O
WHERE O.CustID IN (SELECT C.CustID FROM #tblCustomers AS C WHERE C.CustID = 1000)

-- this returns all the rows in #tblOrders
SELECT O.OrderID FROM #tblOrders AS O
WHERE EXISTS (SELECT 1 FROM #tblCustomers AS C WHERE O.CustID=C.CustID AND C.CustID = 1000)

as this will relate the customers table to Orders based on your required values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2013-02-13 : 13:27:56

Well, I was really wondering about when to uses IN with a subselect and when to use EXISTS. Are there any real differences between the two?

Also, now that I'm reading your reply, am wondering, SELECT 1 is the same as WHERE LIMIT = 1?

Thanks,
Dan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 13:31:01

quote:
Originally posted by dwdwone

Well, I was really wondering about when to uses IN with a subselect and when to use EXISTS. Are there any real differences between the two?

Also, now that I'm reading your reply, am wondering, SELECT 1 is the same as WHERE LIMIT = 1?

Thanks,
Dan

The above illustration shows how IN and EXISTS can be used for same scenario. EXISTS looks for boolean results whereas In looks for individual values.
SELECT 1 is just a way to check if there's a resultset returned or not ie boolean result. LIMIT 1 is analogous to SELECT TOP 1 ... in SQL Server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-13 : 14:34:39

quote:
Well, I was really wondering about when to uses IN with a subselect and when to use EXISTS. Are there any real differences between the two?

In many cases IN clause and EXISTS clause (or NOT IN and NOT EXISTS) will generate the same query plans and will perform exactly the same. But there are some cases where EXISTS/NOT EXISTS is better. For example, when you need to check conditions against more than one column in the table in the inner select. This thread started a few minutes ago happens to be a perfect example of this: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=182956 In this case, the EXISTS clause will perform better than the IN clause because of the nature of the logic required.

Editing: Also, I forgot to mention that when there are nulls involved, EXISTS and IN may not be logically the same. See the example below:

CREATE TABLE #tblOrders(orderid INT, custid INT);
CREATE TABLE #tblCustomers( custid INT );

INSERT INTO #tblOrders VALUES (1,50),(2,60),(3,1000),(4,NULL);
INSERT INTO #tblCustomers VALUES (1000),(NULL);

-- returns 2 rows
SELECT O.OrderID FROM #tblOrders AS O
WHERE O.CustID NOT IN (SELECT * FROM #tblCustomers AS C WHERE C.CustID = 1000)

-- returns 3 rows
SELECT O.OrderID FROM #tblOrders AS O
WHERE NOT EXISTS (SELECT * FROM #tblCustomers AS C WHERE C.CustID = 1000
	AND c.CustId = o.custid)

DROP TABLE #tblOrders,#tblCustomers

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-13 : 15:13:26

With the newer optimizer (2008+ if memory serves me) will/should produce the same query plan for an IN/Exists. Basically it turns the IN into an EXISTS so it'll stop processing when a match is found. There are other small differences though, like when dealing with NULL values. So, you should be aware of that. So, you may want to use your favorite search engine to get more details as this has been discussed many times.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources