Which SQL statement is more efficient joins or exists

Question

I have 2 tables, orderData and stagingOrderData. I need to see if there are any rows in the stagingOrderData which exists in the orderData so I can delete them from the stagingOrderData table before I import.

Test cases - I tried a join

select * from 
dbo.stagingOrderData s
inner join dbo.OrderData o
on s.productid = o.productid
and s.barcode = o.barcode
and s.orderid = o.orderid

and then and "exists"

select * from 
dbo.stagingOrderData s
where exists(dbo.OrderData o
select * from 
    where o.productid = s.productid
and o.barcode = s.barcode
and o.orderid = s.orderid )

The statement with the "exists" seems to be much faster.

Duplicate - see this question here for instance: http://stackoverflow.com/questions/227037/can-i-get-better-performance-using-a-join-or-using-exists — marc_s, Aug 13 '09 at 19:47

score 1 · Answer 1 · answered Aug 13 '09 at 19:48

An exists will generally be faster. It stops after finding the first match after all where the join must consider all possible matches.

You may wish to ask if you need to do it this way at all though. Instead of deleting the rows, you may be better off doing the insert with a "where not exists..." restriction. If you are using SQL Server 2008, you can do even better than that by using a merge statement.

score 1 · Accepted Answer · answered Aug 13 '09 at 20:59

My experience is that it is a toss-up. If you have good indexing on the two tables (you need an index with productid, barcode, and orderid), I'll bet these two queries will perform about the same. So I'd use whichever you consider easier to read/maintain.

Plus, if all you are doing is querying to determine if you need to delete, you might be better off skipping the select and just doing the delete. If there is nothing to delete, the delete statement will discover that and will do nothing. In other words, doing the select requires you to process the data twice if you find anything needs to be deleted. Instead, do only the delete and you will only process the data once no matter what.

Which SQL statement is more efficient joins or exists

2 Answers2