For starters, a link to an old article in my blog on how NOT IN
predicate works in SQL Server
(and in other systems too):
You can rewrite it as follows:
SELECT *
FROM Orders o
WHERE NOT EXISTS
(
SELECT NULL
FROM HeldOrders ho
WHERE ho.OrderID = o.OrderID
)
, however, most databases will treat these queries the same.
Both these queries will use some kind of an ANTI JOIN
.
This is useful for SQL Server
if you want to check two or more columns, since SQL Server
does not support this syntax:
SELECT *
FROM Orders o
WHERE (col1, col2) NOT IN
(
SELECT col1, col2
FROM HeldOrders ho
)
Note, however, that NOT IN
may be tricky due to the way it treats NULL
values.
If Held.Orders
is nullable, no records are found and the subquery returns but a single NULL
, the whole query will return nothing (both IN
and NOT IN
will evaluate to NULL
in this case).
Consider these data:
Orders:
OrderID
---
1
HeldOrders:
OrderID
---
2
NULL
This query:
SELECT *
FROM Orders o
WHERE OrderID NOT IN
(
SELECT OrderID
FROM HeldOrders ho
)
will return nothing, which is probably not what you'd expect.
However, this one:
SELECT *
FROM Orders o
WHERE NOT EXISTS
(
SELECT NULL
FROM HeldOrders ho
WHERE ho.OrderID = o.OrderID
)
will return the row with OrderID = 1
.
Note that LEFT JOIN
solutions proposed by others is far from being a most efficient solution.
This query:
SELECT *
FROM Orders o
LEFT JOIN
HeldOrders ho
ON ho.OrderID = o.OrderID
WHERE ho.OrderID IS NULL
will use a filter condition that will need to evaluate and filter out all matching rows which can be numerius
An ANTI JOIN
method used by both IN
and EXISTS
will just need to make sure that a record does not exists once per each row in Orders
, so it will eliminate all possible duplicates first:
NESTED LOOPS ANTI JOIN
and MERGE ANTI JOIN
will just skip the duplicates when evaluating HeldOrders
.
- A
HASH ANTI JOIN
will eliminate duplicates when building the hash table.