- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
663 Views
