SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 IN vs EXISTS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dwdwone
Yak Posting Veteran

USA
68 Posts

Posted - 02/12/2013 :  16:46:40  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 02/12/2013 :  17:25:49  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/13/2013 :  05:09:05  Show Profile  Reply with Quote
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/

Go to Top of Page

dwdwone
Yak Posting Veteran

USA
68 Posts

Posted - 02/13/2013 :  13:27:56  Show Profile  Reply with 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?

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

Thanks,
Dan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/13/2013 :  13:31:01  Show Profile  Reply with Quote
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/

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 02/13/2013 :  14:34:39  Show Profile  Reply with Quote
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

Edited by - James K on 02/13/2013 14:37:38
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/13/2013 :  15:13:26  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000