Sql Excel : Subqueries and IN

IN and NOT IN were introduced earlier as convenient shorthand for complicated
WHERE clauses. There is another version where the “in” set is specified by a
subquery, rather than by a fixed list. For example, the following query gets the list
of all zip codes in states with fewer than 100 zip codes:
SELECT zc.zcta5, zc.stab
FROM ZipCensus zc
WHERE zc.stab IN (SELECT stab
FROM ZipCensus
GROUP BY stab
HAVING COUNT(*) < 100 ) The subquery creates a set of all states in ZipCensus where the number of zip codes in the state is less than 100 (that is, DC, DE, HI, and RI). The HAVING clause sets this limit. HAVING is very similar to WHERE, except it filters rows after aggregating, rather than before. The outer SELECT then chooses zip codes whose state matches one of the states in the IN set.