MySQL 8 Cookbook

Finding mismatched rows between tables

Suppose you want to find rows in a table that are not in other tables. You can achieve this in two ways. Using the NOT IN clause or using OUTER JOIN.

To find the matched rows, you can use normal JOIN, if you want to find mismatched rows, you can use OUTER JOIN. Normal JOIN means A intersection B. OUTER JOIN gives matching records of both A and B and also gives unmatched records of A with NULL. If you want the output of A-B, you can use the WHERE <JOIN COLUMN IN B> IS NULL clause.

To understand the usage of OUTER JOIN, create two employee tables and insert some values:

mysql> CREATE TABLE employees_list1 AS SELECT * FROM employees WHERE first_name LIKE 'aa%';Query OK, 444 rows affected (0.22 sec)Records: ...