O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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: ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required