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.
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.egSELECT 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 rowsSELECT O.OrderID FROM #tblOrders AS OWHERE O.CustID IN (SELECT * FROM #tblCustomers AS C WHERE C.CustID = 1000)-- this returns all the rows in #tblOrdersSELECT O.OrderID FROM #tblOrders AS OWHERE 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 isSELECT O.OrderID FROM #tblOrders AS OWHERE O.CustID IN (SELECT C.CustID FROM #tblCustomers AS C WHERE C.CustID = 1000)-- this returns all the rows in #tblOrdersSELECT O.OrderID FROM #tblOrders AS OWHERE 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 MVPhttp://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 MVPhttp://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 rowsSELECT O.OrderID FROM #tblOrders AS OWHERE O.CustID NOT IN (SELECT * FROM #tblCustomers AS C WHERE C.CustID = 1000)-- returns 3 rowsSELECT O.OrderID FROM #tblOrders AS OWHERE 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. |
|
|
|
|
|
|
|