Find records from one MySQL table which don't exist in another?
To find the records from one MySQL table which don’t exist in another table we can use the
subquery for the table which does not have the records. This can be better understood using the
given steps −
First a table is created using the create command. The table name is ‘PresentHistory’ and it has
two columns. This is given as follows −
mysql> CREATE table PresentHistory
-> (
-> HisID int,
-> HisName varchar(100)
-> );
Query OK, 0 rows affected (0.54 sec)
After creating the table, some records are inserted that will be present in the second table as
well. This is done with the help of the insert command as follows −
mysql> INSERT into PresentHistory values(1,'John');
Query OK, 1 row affected (0.13 sec)
mysql> INSERT into PresentHistory values(2,'Bob');
Query OK, 1 row affected (0.15 sec)
After inserting the records successfully, they are displayed with the select statement as follows −
mysql> SELECT * from PresentHistory;
After executing the above query, the output obtained is.
+-------+---------+
| HisID | HisName |
+-------+---------+
| 1 | John |
| 2 | Bob |
+-------+---------+
2 rows in set (0.00 sec)
Now, a second table is created using the create command. This table is named ‘PastHistory’
and contains two columns as given below.
mysql> CREATE table PastHistory
-> (
-> PastId int,
-> PastName varchar(100)
-> );
Query OK, 0 rows affected (0.74 sec)
After creating the table, some records which are present in first table and some records which
are not present in first table are inserted in PastHistory table.
mysql> INSERT into PastHistory values(1,'John');
Query OK, 1 row affected (0.13 sec)
mysql> INSERT into PastHistory values(2,'Bob');
Query OK, 1 row affected (0.13 sec)
mysql> INSERT into PastHistory values(3,'Carol');
Query OK, 1 row affected (0.17 sec)
mysql> INSERT into PastHistory values(4,'Jason');
Query OK, 1 row affected (0.16 sec)
Now, there are 4 records in the second table. Out of these, 2 records are from the first table and
2 records are different in second table.
The records in the second table are seen with the help of the select statement as follows −
mysql> SELECT * from PastHistory;
The output of the above query is
+--------+----------+
| PastId | PastName |
+--------+----------+
| 1 | John |
| 2 | Bob |
| 3 | Carol |
| 4 | Jason |
+--------+----------+
4 rows in set (0.00 sec)
The syntax to check the records from one table that don’t exist in the second table is given as
follows −
SELECT * from yourSecondTableName where columnNamefromSecondtable NOT IN
(SELECT columnNamefromfirsttable from yourFirstTableName);
The given query is used to get the records that are distinct in the second table −
mysql> SELECT * from PastHistory where PastName not in (select HisName from
PresentHistory);
The output of the above query is as follows −
+--------+----------+
| PastId | PastName |
+--------+----------+
| 3 | Carol |
| 4 | Jason |
+--------+----------+
2 rows in set (0.00 sec)
From the above output it is clear that we have found two records which are not present in the
first table.
Published on 18-Oct-2018 13:17:06
