Is a JOIN more/less efficient than EXISTS IN when no data is needed from the second table?

Question

I need to look up all households with orders. I don't care about the data of the order at all, just that it exists. (Using SQL Server)

Is it more efficient to say something like this:

SELECT HouseholdID, LastName, FirstName, Phone 
FROM Households 
INNER JOIN Orders ON Orders.HouseholdID = Households.HouseholdID

or this:

SELECT HouseholdID, LastName, FirstName, Phone 
FROM Households 
WHERE EXISTS 
    (SELECT HouseholdID 
     FROM Orders 
     WHERE Orders.HouseholdID = Households.HouseholdID)

Answer 1

Unless this is a fairly rigid 1:1 relationship (which doesn't seem to make much sense given a the wider meaning of households and orders), your queries will return different results (if there are more matching rows in the Orders table).

On Oracle (and most DBMS), I would expect the Exists version to run significantly faster since it only needs to find one row in Orders for the Households record to qualify.

Regardless of the DBMS I would expect the explain plan to show the difference (if the tables are significantly large that the query would not be resolved by full table scans).

Have you tried testing it? Allowing for caching?

C.