Last updated on June 17th, 2024 at 06:20 am
Sometimes you may need to find or select rows not present in another table in MySQL. This is mainly required in data analysis & reporting where you will need to compare data across multiple tables. It is also useful for data management where you will need to perform such checks as a part of data quality control. There are a couple of ways to easily do this in MySQL. In this article, we will look at how to show rows not present in another table in MySQL.
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). We need to select rows that are present in sales table but not in orders table. First we will create the two tables.
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 |
+------+------------+--------+
We will look at 4 ways to solve this problem.
1. Using Not In Clause
If you need to select rows in one table that are not present in another table, based on a column, then you can easily do this using NOT IN Clause as shown below. Here is its syntax.
SELECT * FROM Table1 WHERE id NOT IN (SELECT id FROM Table2)
Here is the query to select sales rows whose order_date column value is not present in orders table.
mysql> SELECT *
FROM sales
WHERE order_date NOT IN (SELECT order_date FROM orders);
+------+------------+--------+
| id | order_date | amount |
+------+------------+--------+
| 1 | 2021-01-24 | 250 |
| 2 | 2021-01-25 | 250 |
| 3 | 2021-01-26 | 250 |
| 4 | 2021-01-27 | 250 |
+------+------------+--------+
Another alternative way to use NOT IN clause to show records not present in other table is to list all columns of your table in (…) as shown below.
mysql> SELECT * FROM sales
WHERE (id,order_date,amount)
NOT IN (SELECT * FROM orders);
+------+------------+--------+
| id | order_date | amount |
+------+------------+--------+
| 1 | 2021-01-24 | 250 |
| 2 | 2021-01-25 | 250 |
| 3 | 2021-01-26 | 250 |
| 4 | 2021-01-27 | 250 |
+------+------------+--------+
Please note, you need to mention all column names of sales table in WHERE clause, else you will get an error. In this case, MySQL will match column names of sales table with those of orders table and also compare column values for each row.
mysql> SELECT * FROM sales
WHERE (id,order_date)
NOT IN (SELECT * FROM orders);
ERROR 1241 (21000): Operand should contain 2 column(s)
Basically, NOT IN clause allows you to show rows not present in another table, based on one column, or based on all columns, nothing in between.
2. Using 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.
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 |
+------+------------+--------+
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.
Please note, using NOT EXISTS is the most recommended way for large data sets since it saves time and memory.
3. Using Left Join
Left join returns all rows that from left table and matching rows from right table. It returns nulls for right table columns, in case there is no match. It is these rows with null values that are of interest to us, since they are present in left table but not in right table. Here is how to use LEFT JOIN to get rows that are present in left table but not in right table.
mysql> SELECT sales.* FROM sales
LEFT JOIN orders ON sales.order_date = orders.order_date
WHERE orders.order_date IS NULL;
+------+------------+--------+
| id | order_date | amount |
+------+------------+--------+
| 1 | 2021-01-24 | 250 |
| 2 | 2021-01-25 | 250 |
| 3 | 2021-01-26 | 250 |
| 4 | 2021-01-27 | 250 |
+------+------------+--------+
Please remember to add the IS NULL clause at the end of your SQL query. That is what returns the rows present in left table but not in right table.
4. Using Natural Join
If you have too many columns in your table and it is tedious to mention all of them in your JOIN ON clause, then you can use a NATURAL LEFT JOIN which will automatically do join based on matching column names. The main difference between LEFT JOIN and NATURAL LEFT JOIN is that in LEFT JOIN we specify the JOIN columns whereas in NATURAL LEFT JOIN, MySQL automatically joins based on all matching column names.
mysql> SELECT sales.* FROM sales
NATURAL LEFT JOIN orders
WHERE orders.order_date IS NULL;
+------+------------+--------+
| id | order_date | amount |
+------+------------+--------+
| 1 | 2021-01-24 | 250 |
| 2 | 2021-01-25 | 250 |
| 3 | 2021-01-26 | 250 |
| 4 | 2021-01-27 | 250 |
+------+------------+--------+
Conclusion
In this article, we have learnt 4 different ways to find and display rows that are present in one table but not in another. Among them, the solution using NOT EXISTS works especially well for large tables since it is time and memory efficient. On the other hand, solutions using NOT IN and NATURAL LEFT JOIN have simple syntax. The solution using LEFT JOIN is memory efficient since it does not pull unnecessary data. You can use any of these methods are per your requirement.
Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
Also read
How to Get Current Week Data in MySQL
Select Top 10 records for each category in MySQL
Common Table Expression in MySQL
How to Calculate Margin in MySQL
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.