MySQL: Negative Comparison

To wit: finding all the records for a column in one table (the smaller) that are not in another table (the larger). See this page from MySQL site:

Posted by Count Henry De Havilland-Fortesque-Smedley on January 13 2004 6:59am [Delete] [Edit]

Sometimes you want to retrieve the records that DONT match a select statement.

Consider this select:
SELECT CarIndex FROM DealerCatalog, BigCatalog WHERE
DealerCatalog.CarIndex=BigCatalog.CarIndex

This finds all the CarIndex values in the Dealer's catalog that are in the bigger distributor catalog.


How do I then find the dealer CarIndex values that ARE NOT in the bigger catalog?

The answer is to use LEFT JOIN - anything that doesn't join is given a NULL value , so we look for that:

SELECT CarIndex FROM DealerCatalog LEFT JOIN BigCatalog ON DealerCatalog.CarIndex=BigCatalog.CarIndex WHERE BigCatalog.CarIndex IS NULL