Sql Excel : EXISTS and NOT EXISTS Operators

EXISTS and NOT EXISTS are similar to IN and NOT IN with subqueries. The operators
return true when any row exists (or no row exists) in a subquery. They are often
used with correlated subqueries.
The query to return all the orders whose zip code is not in ZipCensus could be
written as:
SELECT o.ZipCode, COUNT(*)
FROM Orders o
WHERE NOT EXISTS (SELECT 1
FROM ZipCensus zc
WHERE zc.zcta5 = o.ZipCode)
GROUP BY o.ZipCode
The “1” in the subquery has no importance, because NOT EXISTS is really
determining if any rows are returned. It doesnt care about the particular value in
any of the columns. In fact, some databases accept a nonsensical value, such as 1 /
0 (although this is not recommended).
EXISTS has several advantages over IN. First, EXISTS is more expressive—the
comparison could be made on more than one column. IN only works for
comparing one column to a list (although some databases extend this functionality
to multiple columns). If, for instance, the query were comparing both state name
and country name, then it would be easier to write using NOT EXISTS.
A second advantage is more subtle and applies only to NOT EXISTS. If the list of
values returned by NOT IN contains a NULL value, then all rows fail the test. Why?
SQL treats a comparison to NULL as unknown. So if the comparison were ‘X’ NOT
IN (‘A’, ‘B’, ‘X’, NULL) then the result is false, because ‘X’ is, in fact, in the list.
If the comparison were ‘X’ NOT IN (‘A’, ‘B’, NULL), then the result is unknown,
because it is unknown whether or not X matches the NULL. The important point:
neither version returns true. The equivalent NOT EXISTS query behaves more
intuitively. The second example—using NOT EXISTS—would return true.
The final advantage is practical. In many databases, EXISTS and NOT EXISTS are
optimized to be more efficient than the equivalent IN and NOT IN. One reason is
that IN essentially creates the entire underlying list and then does the comparison,
whereas EXISTS can simply stop at the first matching value.