Multiple LEFT JOINs in one query in SQL

Introduction

We use multiple LEFT JOINs to combine data from several tables, even if some tables don't have matching records. This helps us see all main data with related details.

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

You need to show all employees with their department and project info, even if some employees are not assigned to projects.

You want to display all products with their categories and suppliers, even if some products lack category or supplier info.

Syntax

SQL

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.key = table2.key
LEFT JOIN table3 ON table1.key = table3.key;

LEFT JOIN keeps all rows from the first (left) table, adding matching rows from joined tables or NULL if no match.

You can add as many LEFT JOINs as needed to include related data from multiple tables.

Examples

Shows all customers and their orders, including customers without orders.

SQL

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

Lists all employees with their department and project names, even if some info is missing.

SQL

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

Sample Program

This query lists all authors with their books and any reviews. Authors without books or books without reviews still appear with NULLs.

SQL

CREATE TABLE authors (id INT, name VARCHAR(20));
CREATE TABLE books (id INT, title VARCHAR(30), author_id INT);
CREATE TABLE reviews (id INT, book_id INT, rating INT);

INSERT INTO authors VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO books VALUES (1, 'Book A', 1), (2, 'Book B', 1), (3, 'Book C', 2);
INSERT INTO reviews VALUES (1, 1, 5), (2, 1, 4), (3, 3, 3);

SELECT authors.name AS author, books.title AS book, reviews.rating
FROM authors
LEFT JOIN books ON authors.id = books.author_id
LEFT JOIN reviews ON books.id = reviews.book_id
ORDER BY authors.name, books.title;

Important Notes

If a LEFT JOIN finds no matching row, it fills columns from that table with NULL.

Order of LEFT JOINs matters: each join uses the result of previous joins as its left table.

Use ORDER BY to organize results clearly when joining multiple tables.

Summary

Multiple LEFT JOINs let you combine data from many tables while keeping all rows from the main table.

They help show complete info even if some related data is missing.

Remember NULLs appear when no matching data exists in joined tables.