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:

OrderID	CustomerID	OrderDate
1	101	2023-01-15
2	102	2023-02-20
3	101	2023-03-10
4	103	2023-04-05

Customers table:

CustomerID	CustomerName	City
101	Alice	New York
102	Bob	Los Angeles
104	Carol	Chicago

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

SELECT O.OrderID
	,O.CustomerID
	,C.CustomerName
	,O.OrderDate
FROM Customers AS C
RIGHT JOIN Orders AS O ON O.CustomerID = C.CustomerID
WHERE C.CustomerID IS NULL;

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.