MySQL select query to select rows from a table that are not in another table?

For our example, we will create two tables and apply Natural Left Join to get the rows from a table not present in the second table.

Creating the first table.

mysql> create table FirstTableDemo
   -> (
   -> id int,
   -> name varchar(100)
   -> );
Query OK, 0 rows affected (0.48 sec)

Inserting records into first table.

mysql> insert into FirstTableDemo values(1,'Bob'),(2,'John'),(3,'Carol');
Query OK, 3 rows affected (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 0

To display all records.

mysql> select *from FirstTableDemo;

The following is the output.

+------+-------+
| id   | name  |
+------+-------+
|    1 | Bob   |
|    2 | John  |
|    3 | Carol |
+------+-------+
3 rows in set (0.00 sec)

Creating second table.

mysql> create table SecondTableDemo
   -> (
   -> id int,
   -> name varchar(100)
   -> );
Query OK, 0 rows affected (0.62 sec)

Inserting records into the second table.

mysql> insert into SecondTableDemo values(1,'Bob'),(2,'John');
Query OK, 2 rows affected (0.12 sec)
Records: 2  Duplicates: 0  Warnings: 0

To display all records.

mysql> select *from SecondTableDemo;

The following is the output.

+------+------+
| id   | name |
+------+------+
|    1 | Bob  |
|    2 | John |
+------+------+

The following is the syntax to select the rows which are not in the second table.

mysql> SELECT tbl1.*
    -> FROM FirstTableDemo tbl1
    -> NATURAL LEFT JOIN  SecondTableDemo tbl2
    -> where tbl2.name IS NULL;

The following is the output that displays the rows which are in the first table, but not in the second table i.e. “Carol”.

+------+-------+
| id   | name  |
+------+-------+
|    3 | Carol |
+------+-------+
1 row in set (0.03 sec)

Chandu yadav

Updated on: 30-Jul-2019

2K+ Views

Get certified by completing the course

Get Started