I often want to know which items in a table are not in another table. In this example, I have two tables of words. The big table has ~68,000 words and the small one has ~29,000. Since they both have the field name, I use an alias in the SELECT statement so that I don’t have to decipher the results. I also put the fields in the same order as the join so that the logic of the query is consistent with the SELECT statement, but you can put them in any order.
Remember that a LEFT OUTER JOIN contains all of the records from the first table. If there is no corresponding record in the second table, the values for its fields are null.
SELECT SmallTable.word AS SmallWord, BigTable.word AS BigWord
FROM SmallTable
LEFT OUTER JOIN BigTable
ON SmallTable.word = BigTable.word
WHERE BigTable.word IS NULL
ORDER BY `SmallTable`.`word` ASC
The query yields 364 words and looks like this:
Just for fun, the reverse query yields 38,776 words and looks like this.
SELECT SmallTable.word AS SmallWord, BigTable.word AS BigWord
FROM BigTable
LEFT OUTER JOIN SmallTable
ON SmallTable.word = BigTable.word
WHERE SmallTable.word IS NULL
ORDER BY `BigTable`.`word` ASC
