What is the most effective way to write a select statement with a & ldquo; Not in & rdquo; Subquery?


What is the most efficient way to write a select statement similar to the below.

FROM Orders
WHERE Orders.Order_ID not in (Select Order_ID FROM HeldOrders)

The gist is you want the records from one table when the item is not in another table.

"Most efficient" is going to be different depending on tables sizes, indexes, and so on. In other words it's going to differ depending on the specific case you're using.

There are three ways I commonly use to accomplish what you want, depending on the situation.

1. Your example works fine if Orders.order_id is indexed, and HeldOrders is fairly small.

2. Another method is the "correlated subquery" which is a slight variation of what you have...

FROM Orders o
WHERE Orders.Order_ID not in (Select Order_ID
                              FROM HeldOrders h
                              where h.order_id = o.order_id)

Note the addition of the where clause. This tends to work better when HeldOrders has a large number of rows. Order_ID needs to be indexed in both tables.

3. Another method I use sometimes is left outer join...

FROM Orders o
left outer join HeldOrders h on h.order_id = o.order_id
where h.order_id is null

When using the left outer join, h.order_id will have a value in it matching o.order_id when there is a matching row. If there isn't a matching row, h.order_id will be NULL. By checking for the NULL values in the where clause you can filter on everything that doesn't have a match.

Each of these variations can work more or less efficiently in various scenarios.