Skip to main content

MySQL - How to Use a RIGHT JOIN to Find Missing Rows

A right join is used to combine two tables based on a specified condition and includes all rows from the right (second) table and matching rows from the left (first) table. If there are no matches, the result will still include rows from the right table, but the columns from the left table will contain NULL values.

To use a right join to find missing rows, you would typically want to find the rows that exist in the right table but do not have corresponding matches in the left table. Here's how you can do that:

Sample Data to Demonstrate Using Right Join to Find Missing Records

You can use this DB Fiddle to follow along and practice using right join to compare data between two tables

Let's assume you have two tables: Orders and Customers. You want to find the orders that have no matching customer.

Orders table:


Customers table:

101AliceNew York
102BobLos Angeles

Right Join Operation to Find Missing Customer Records

Here's a step-by-step example using SQL:

Use RIGHT JOIN to only retrieve a list of orders without matching customers
FROM Customers AS C
RIGHT JOIN Orders AS O ON O.CustomerID = C.CustomerID

In this example:

  1. We start with the Orders table as the right table and the Customers table as the left table.

  2. We perform a right join using the condition O.CustomerID = C.CustomerID, which means we're matching orders with customers based on the CustomerID field.

  3. We then use the WHERE clause to filter out rows where CustomerID is NULL. These are the rows from the Orders table that do not have a matching customer in the Customers table.