Saturday, September 23, 2006

NOT IN vs NOT EXISTS vs OUTER JOIN

3 ways Missing Data to locate missing data.
All are executed indentically according to the execution plan.
The third (outer join with null checking) is probably the most accedemically correct, though (IMHO) the least readable..

-- not in
select * from Sales.SalesOrderDetail
where SalesOrderID not in (select SalesOrderID from Sales.SalesOrderHeader)

-- not exists
select * from Sales.SalesOrderDetail SalesOrderDetail
where not exists (select * from Sales.SalesOrderHeader SalesOrderHeader
where SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID)

-- outer join and NULL checking
select * from Sales.SalesOrderDetail SalesOrderDetail
left outer join Sales.SalesOrderHeader SalesOrderHeader
on SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
where SalesOrderHeader.SalesOrderID is null

No comments: