Sometimes you may need to find rows not present in another table or select rows not present in another table. In this article, we will look at how to show rows not present in another table in MySQL, using NOT EXISTS clause.
MySQL NOT EXISTS clause
MySQL provides EXISTS, UNION and NOT EXISTS clauses that help you perform SET operations with MySQL tables. By SET operations, we mean that you can treat MySQL tables & query results as mathematical sets and select rows that are present in both tables, or only one of the tables. For our article, we will use the NOT EXISTS clause.
How to Show Rows Not Present in Another Table
Here are the steps to find rows not present in another table. Let us say you have two tables sales(id, order_date, amount) and orders(id, order_date, amount)
mysql> create table sales(id int, order_date date, amount int);
mysql> insert into sales(id, order_date, amount)
values(1, '2021-01-24',250),
(2, '2021-01-25',250),
(3, '2021-01-26',250),
(4, '2021-01-27',250),
(5, '2021-01-28',250),
(6, '2021-01-29',250),
(7, '2021-01-30',250),
(8, '2021-01-31',250),
(9, '2021-02-01',250);
mysql> select * from sales;
+------+------------+--------+
| id | order_date | amount |
+------+------------+--------+
| 1 | 2021-01-24 | 250 |
| 2 | 2021-01-25 | 250 |
| 3 | 2021-01-26 | 250 |
| 4 | 2021-01-27 | 250 |
| 5 | 2021-01-28 | 250 |
| 6 | 2021-01-29 | 250 |
| 7 | 2021-01-30 | 250 |
| 8 | 2021-01-31 | 250 |
| 9 | 2021-02-01 | 250 |
+------+------------+--------+
mysql> create table orders(id int, order_date date, amount int);
mysql> insert into orders(id, order_date, amount)
values(5, '2021-01-28',250),
(6, '2021-01-29',250),
(7, '2021-01-30',250),
(8, '2021-01-31',250),
(9, '2021-02-01',250);
mysql> select * from orders;
+------+------------+--------+
| id | order_date | amount |
+------+------------+--------+
| 5 | 2021-01-28 | 250 |
| 6 | 2021-01-29 | 250 |
| 7 | 2021-01-30 | 250 |
| 8 | 2021-01-31 | 250 |
| 9 | 2021-02-01 | 250 |
+------+------------+--------+
Also read : How to Get Current Week Data in MySQL
Here is the SQL query to select data from sales table that is not present in orders table.
mysql> SELECT *
FROM sales D
WHERE NOT EXISTS(SELECT * FROM orders c
WHERE D.order_date = C.order_date);
+------+------------+--------+
| id | order_date | amount |
+------+------------+--------+
| 1 | 2021-01-24 | 250 |
| 2 | 2021-01-25 | 250 |
| 3 | 2021-01-26 | 250 |
| 4 | 2021-01-27 | 250 |
+------+------------+--------+
Also read : Select Top 10 records for each category in MySQL
In the above query, we use NOT EXISTS clause to select row from sales table that are not present in orders table, which are selected using subquery. In the subquery, we select only those rows from orders table whose order_date is same as that in sales table.
Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
