Finding unmatched rows with LEFT JOIN in SQL

Introduction

We use LEFT JOIN to find rows in one table that do not have matching rows in another table. This helps spot missing or unmatched data.

You want to find customers who have not placed any orders.

You need to list employees who are not assigned to any project.

You want to identify products that have never been sold.

You want to check which students have not enrolled in any course.

Syntax

SQL

SELECT A.*
FROM TableA A
LEFT JOIN TableB B ON A.key = B.key
WHERE B.key IS NULL;

The LEFT JOIN keeps all rows from the left table (TableA).

The WHERE clause filters to only rows where the right table (TableB) has no match (NULL).

Examples

Finds customers who have not placed any orders.

SQL

SELECT customers.*
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
WHERE orders.customer_id IS NULL;

Finds employees not assigned to any project.

SQL

SELECT employees.*
FROM employees
LEFT JOIN projects ON employees.id = projects.employee_id
WHERE projects.employee_id IS NULL;

Finds products never sold.

SQL

SELECT products.*
FROM products
LEFT JOIN sales ON products.id = sales.product_id
WHERE sales.product_id IS NULL;

Sample Program

This example finds customers who have not placed any orders. Bob (id=2) has no orders, so he appears in the result.

SQL

CREATE TABLE customers (id INT, name VARCHAR(20));
CREATE TABLE orders (id INT, customer_id INT);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO orders VALUES (1, 1), (2, 1), (3, 3);

SELECT customers.id, customers.name
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
WHERE orders.customer_id IS NULL
ORDER BY customers.id;

Important Notes

Always check for NULL in the right table to find unmatched rows.

LEFT JOIN returns all rows from the left table, even if no match exists.

Summary

LEFT JOIN plus WHERE right_table.key IS NULL finds unmatched rows.

This technique helps find missing or orphan data in related tables.