MySQL - How to Use a LEFT JOIN to Find Missing Records

A LEFT JOIN is a type of database operation used to combine two tables based on a common column, while also including all the records from the left table, even if they don't have corresponding matches in the right table. This can be useful for finding missing records or for comparing data between two tables.

Let's say you have two tables: TableA and TableB. You want to find the records in TableA that do not have matching records in TableB. You can achieve this using a LEFT JOIN and checking for NULL values in the columns from TableB.

Here's an example SQL query that demonstrates how to use a LEFT JOIN to find missing records:

Find the records in TableA that do not have matching records in TableB

SELECT TableA.*
FROM TableA
LEFT JOIN TableB ON TableA.id = TableB.id
WHERE TableB.id IS NULL;

In this SQL query snippet:

• TableA and TableB are the names of the two tables you're working with.
• id is the common column that you're using to join the tables. You can replace this with the appropriate column name that you're using as the reference.
• The LEFT JOIN combines the records from TableA with matching records from TableB. If there is no match in TableB, columns from TableB will contain NULL values.
• The WHERE TableB.id IS NULL condition filters the results to only include the records from TableA that don't have matching records in TableB.

This query will return all the records from TableA that do not have corresponding matches in TableB, effectively helping you find missing records.

Please keep in mind that the specific column names and table names should be adjusted to match your actual database schema.

Let's illustrate the application of LEFT JOIN to find missing rows in the second table through a practical real-world scenario.

Sample Data to Demonstrate Using Left Join to Find Missing Rows

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

Let's assume you have the following Orders table and Customers tables:

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

Left Join Operation to Find Orders without Matching Customer Records

Suppose you want to retrieve a list of all orders along with the customer names for those orders, including orders without matching customers. You can achieve this using a left join between the Orders and Customers tables based on the CustomerID field.

Use LEFT JOIN to retrieve a list of all orders along with the customer names for those orders

SELECT O.OrderID
	,O.CustomerID
	,C.CustomerName
	,O.OrderDate
FROM Orders AS O
LEFT JOIN Customers AS C ON O.CustomerID = C.CustomerID;

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

You can then add a WHERE clause to only include orders without matching customers.

Use LEFT JOIN to only retrieve a list of orders without matching customers

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

OrderID	CustomerID	CustomerName	OrderDate
4	103	null	2023-04-05