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.

Chandu yadav

Published on 18-Oct-2018 09:47:06

• Related Questions & Answers
• MySQL query to include more than one column in a table that doesn't already exist
• Take all records from one MySQL table and insert it to another?
• MySQL query to copy records from one table to another with different columns
• Handshakes That Don't Cross in C++
• Move rows from one table to another in MySQL?
• Insert data from one table to another in MySQL?
• Update data in one table from data in another table in MySQL?
• Why python for loops don't default to one iteration for single objects?
• Why Don't Transformers Work on DC Supply?
• MySQL stored procedure parameters don't seem to work with special character @?
• Insert from one table with different structure to another in MySQL?
• How to copy rows from one table to another in MySQL?
• MySQL query to find alternative records from a table
• MySQL statement to copy data from one table and insert into another table
• Remove All Nodes Which Don't Lie in Any Path With Sum>=k using C++