LEFT JOIN preserving all left rows in SQL

Introduction

LEFT JOIN helps you combine two tables and keep all rows from the first (left) table, even if there is no matching data in the second (right) table.

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

You need to show all employees and their departments, including those not assigned to any department.

You want to find all products and their sales, including products that have never been sold.

You want to combine student records with their test scores, showing students even if they missed the test.

Syntax

SQL

SELECT columns
FROM left_table
LEFT JOIN right_table ON left_table.key = right_table.key;

LEFT JOIN keeps all rows from the left_table, adding NULLs for missing matches in right_table.

Use ON to specify how the tables relate by matching columns.

Examples

This shows all customers and their orders. Customers without orders still appear with 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 department names. Employees without a department show NULL for department name.

SQL

SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments ON employees.dept_id = departments.id;

Sample Program

This example creates two tables: customers and orders. It inserts some data, then uses LEFT JOIN to show all customers with their orders. Customers without orders show NULL for 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

If no matching row exists in the right table, the result shows NULL for those columns.

LEFT JOIN is useful to find missing or unmatched data while keeping the main list intact.

Summary

LEFT JOIN keeps all rows from the left table.

It adds matching rows from the right table or NULL if no match.

Use it to combine data but never lose rows from the main table.