Sql Excel : NOT IN Operator

The NOT IN operator can also use subqueries and correlated subqueries. Consider
the following question: Which zip codes in the Orders table are not in the
ZipCensus table? Once again, there are different ways to answer this question. The
first uses the NOT IN operator:
SELECT o.ZipCode, COUNT(*) as NumOrders
FROM Orders o
WHERE ZipCode NOT IN (SELECT zcta5
FROM ZipCensus zc
)
GROUP BY o.ZipCode
This query is straightforward as written, choosing the zip codes in Orders with no
matching zip code in ZipCensus, then grouping them and returning the number of
purchases in each.
An alternative uses the LEFT OUTER JOIN operator. Because the LEFT OUTER JOIN
keeps all zip codes in the Orders table—even those that dont match—a filter
afterwards can choose the non-matching set:
SELECT o.ZipCode, COUNT(*) as NumOrders
FROM Orders o LEFT OUTER JOIN
ZipCensus zc
ON o.ZipCode = zc.zcta5
WHERE zc.zcta5 IS NULL
GROUP BY o.ZipCode
ORDER BY NumOrders DESC
This query joins the two tables using a LEFT OUTER JOIN and only keeps the results
rows do not match (because of the WHERE clause). This is essentially equivalent to
using NOT IN; whether one works better than the other depends on the underlying
optimization engine.