How to Show Rows Not Present in Another Table in MySQL?

Sometimes, you may need to find rows that don’t exist in another table, or you may need to select rows that don’t exist in another table. The purpose of this article is to demonstrate how you can use the NOT EXISTS clause in MySQL to show rows that are not present in another table.

MySQL NOT EXISTS clause

In order to perform SET operations with MySQL tables, it is possible to use the EXISTS, UNION and NOT EXISTS clauses. SET operations refer to the concept of treating MySQL tables & query results as mathematical sets, and selecting rows that are present in both tables, or just in one of the tables, when your query results are returned. It is for this reason that we will use the NOT EXISTS clause in our article.

How to Show Rows Not Present in Another Table

Here are the steps that need to be followed in order to find rows that are not present in another table. For example, imagine that you have two tables, orders (id, order_date, amount) and sales (id, order_date, amount).

Example:

1.Create Table

#start
 create table sales(id int, order_date date, amount int);
#end

2.Insert data

#start
 insert into sales(id, order_date, amount)
      values(1, '2023-01-24',250),
      (2, '2023-01-25',250),
      (3, '2023-01-26',250),
      (4, '2023-01-27',250),
      (5, '2023-01-28',250),
      (6, '2023-01-29',250),
      (7, '2023-01-30',250),
      (8, '2023-01-31',250),
      (9, '2023-02-01',250);
#end

3.Select Table

#start
    select * from sales;
#end

4. Create second table

#start
 create table orders(id int, order_date date, amount int);
#end

5. Insert data

#start
 insert into orders(id, order_date, amount)
      values(5, '2023-01-28',250),
      (6, '2023-01-29',250),
      (7, '2023-01-30',250),
      (8, '2023-01-31',250),
      (9, '2023-02-01',250);
#end

6. Select Table

#start
    select * from orders;
#end

This SQL query will be used to select data from the sales table that is not present in the orders table.

#start
  SELECT *
       FROM sales D
       WHERE NOT EXISTS(SELECT * FROM orders c
                       WHERE D.order_date = C.order_date);
#end

Using the NOT EXISTS clause in the above query, we are able to select rows from the sales table, which are not present in the orders table, which have been selected using a subquery. Using the subquery, we select only those rows from the orders table whose order_date matches the order_date in the sales table in the main query.