Compare two tables and return missing ids using MySQL LEFT OUTER JOIN


To compare two tables and return missing ids, you need to use MySQL LEFT OUTER JOIN.

Let us create a table with sample fields and then we will insert records. The query to create the first table −

First_Table

mysql> create table First_Table
   -> (
   -> Id int
   -> );
Query OK, 0 rows affected (0.88 sec)

Now insert some records in the table using insert command. The query is as follows −

mysql> insert into First_Table values(1);
Query OK, 1 row affected (0.68 sec)
mysql> insert into First_Table values(2);
Query OK, 1 row affected (0.29 sec)
mysql> insert into First_Table values(3);
Query OK, 1 row affected (0.20 sec)
mysql> insert into First_Table values(4);
Query OK, 1 row affected (0.20 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from First_Table;

The following is the output −

+------+
| Id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

Here is the query to create the second table −

Second_Table

mysql> create table Second_Table
   -> (
   -> Id int
   -> );
Query OK, 0 rows affected (0.60 sec)

Now you can insert some records in the table using insert command. The query is as follows −

mysql> insert into Second_Table values(2);
Query OK, 1 row affected (0.19 sec)
mysql> insert into Second_Table values(4);
Query OK, 1 row affected (0.20 sec)

Display all records from the table using select statement −

mysql> select *from Second_Table;

The following is the output −

+------+
| Id   |
+------+
|    2 |
|    4 |
+------+
2 rows in set (0.00 sec)

The query is as follows to compare two tables and return missing IDs using LEFT OUTER JOIN −

mysql> SELECT First_Table.Id FROM First_Table
   -> LEFT OUTER JOIN Second_Table ON First_Table.Id = Second_Table.Id
   -> WHERE Second_Table.Id IS NULL;

The following is the output −

+------+
| Id   |
+------+
| 1    |
| 3    |
+------+
2 rows in set (0.00 sec)

Samual Sam
Samual Sam

Learning faster. Every day.

Updated on: 30-Jun-2020

636 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements