
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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.
- Related Articles
- 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
- 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?
- MySQL query to include more than one column in a table that doesn't already exist
- 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
- Simplest way to copy data from one table to another new table in MySQL?
- MySQL statement to copy data from one table and insert into another table
- Copy column values from one table into another matching IDs in MySQL
- How to copy a table from one MySQL database to another?
- How to insert only those records that does not exist in a MySQL table?
- Delete all records from a table in MySQL?
