LEFT JOIN with NULL result rows in SQL

Introduction

LEFT JOIN helps you combine two tables and keep all rows from the first table, even if there is no matching data in the second table. This shows missing matches as NULL.

You want to list all customers and their orders, even if some customers have no orders.

You need to find employees and their assigned projects, including those without projects.

You want to show all products and their sales, even if some products have zero sales.

You want to compare two lists and see which items are missing in the second list.

Syntax

SQL

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column;

The LEFT JOIN keeps all rows from table1.

If there is no match in table2, columns from table2 will be NULL.

Examples

Shows all customers and their orders. Customers without orders show NULL for order id.

SQL

SELECT customers.name, orders.id
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

Lists all employees and their projects. Employees without projects show NULL for project name.

SQL

SELECT employees.name, projects.name
FROM employees
LEFT JOIN projects ON employees.project_id = projects.id;

Sample Program

This query lists all customers and their order IDs. Customers without orders show NULL in order_id.

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, 'Carol');
INSERT INTO orders VALUES (101, 1), (102, 1), (103, 3);

SELECT customers.name, orders.id AS order_id
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
ORDER BY customers.id, orders.id;

Important Notes

LEFT JOIN is useful to find unmatched rows by checking for NULLs in the joined table's columns.

Ordering results helps see NULL rows clearly at the right place.

Summary

LEFT JOIN keeps all rows from the first table.

Rows without matches in the second table show NULL values.

This helps find missing or unmatched data easily.