Find records in one MySQL table that do not exist in another table?

To find records in one MySQL table that do not exist in another table, we can use Perform a subquery on a table with no records. This can be better understood using the following Given steps -

First create a table using create command. The table name is "PresentHistory" and it has Two columns. Given below -

mysql> CREATE table PresentHistory
-> (
-> HisID int,
-> HisName varchar(100)
-> );
Query OK, 0 rows affected (0.54 sec)

After creating the table, some records will be inserted which will appear in the second table: Excellent. This is done with the help of the insert command as shown below -

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 successfully inserting the record, the select statement is used to display 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, create the second table using the create command. The table is named "PastHistory" and contains two columns as shown below.

mysql> CREATE table PastHistory
-> (
-> PastId int,
-> PastName varchar(100)
-> );
Query OK, 0 rows affected (0.74 sec)

After creating the table, there are some records in the first table; Content that does not exist in the first table will be inserted into the 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. Among them, 2 records come from the first table, There are 2 records in the second table that are different.

You can see the records in the second table through 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)

Check whether there is a record in the second table in one table The syntax for existing records is as follows As follows-

SELECT * from yourSecondTableName where columnNamefromSecondtable NOT IN
(SELECT columnNamefromfirsttable from yourFirstTableName);

The given query is used to get different records in the second table-

mysql> SELECT * from PastHistory where PastName not in (select HisName from
PresentHistory);

The output of the above query is as as follows-

+--------+----------+
| PastId | PastName |
+--------+----------+
| 3      | Carol    |
| 4      | Jason    |
+--------+----------+
2 rows in set (0.00 sec)

From As can be clearly seen from the above output, we found two items that do not exist in First table.

The above is the detailed content of Find records in one MySQL table that do not exist in another table?. For more information, please follow other related articles on the PHP Chinese website!