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

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

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:

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.