Multiple LEFT JOINs in one query in SQL - Mini Project: Build & Apply

Multiple LEFT JOINs in one query

📖 Scenario: You are managing a small online bookstore database. You have three tables: books, authors, and publishers. Each book has an author and a publisher, but some books might not have a publisher assigned yet.

🎯 Goal: Build a SQL query that uses multiple LEFT JOIN statements to list all books with their authors and publishers. If a book does not have a publisher, it should still appear in the results with NULL for publisher details.

📋 What You'll Learn

Create a books table with columns book_id, title, author_id, and publisher_id.

Create an authors table with columns author_id and author_name.

Create a publishers table with columns publisher_id and publisher_name.

Write a SQL query that uses LEFT JOIN twice to join books with authors and publishers.

Select the book title, author name, and publisher name in the query result.

💡 Why This Matters

🌍 Real World

Online bookstores and many other applications use multiple LEFT JOINs to combine data from different tables while keeping all main records visible.

💼 Career

Understanding how to write queries with multiple LEFT JOINs is essential for database analysts, backend developers, and data engineers to retrieve comprehensive data efficiently.

1

Create the books table with sample data

Write SQL statements to create a table called books with columns book_id (integer), title (text), author_id (integer), and publisher_id (integer). Insert these exact rows into books: (1, 'The Great Adventure', 101, 201), (2, 'Mystery of the Night', 102, NULL), (3, 'Learning SQL', 103, 202).

SQL

-- Create the books table and insert the rows
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows with the exact values.

2

Create the authors and publishers tables with sample data

Write SQL statements to create a table called authors with columns author_id (integer) and author_name (text). Insert these exact rows: (101, 'Alice Johnson'), (102, 'Bob Smith'), (103, 'Carol Lee'). Then create a table called publishers with columns publisher_id (integer) and publisher_name (text). Insert these exact rows: (201, 'Sunshine Books'), (202, 'Tech Press').

SQL

CREATE TABLE books (
  book_id INTEGER,
  title TEXT,
  author_id INTEGER,
  publisher_id INTEGER
);

INSERT INTO books (book_id, title, author_id, publisher_id) VALUES
(1, 'The Great Adventure', 101, 201),
(2, 'Mystery of the Night', 102, NULL),
(3, 'Learning SQL', 103, 202);

-- Create authors and publishers tables and insert rows
-- Your code here

Hint

Use CREATE TABLE and INSERT INTO for both tables with the exact columns and rows.

3

Write the SQL query with multiple LEFT JOINs

Write a SQL SELECT query that selects books.title, authors.author_name, and publishers.publisher_name. Use LEFT JOIN to join books with authors on author_id, and then LEFT JOIN with publishers on publisher_id.

SQL

CREATE TABLE books (
  book_id INTEGER,
  title TEXT,
  author_id INTEGER,
  publisher_id INTEGER
);

INSERT INTO books (book_id, title, author_id, publisher_id) VALUES
(1, 'The Great Adventure', 101, 201),
(2, 'Mystery of the Night', 102, NULL),
(3, 'Learning SQL', 103, 202);

CREATE TABLE authors (
  author_id INTEGER,
  author_name TEXT
);

INSERT INTO authors (author_id, author_name) VALUES
(101, 'Alice Johnson'),
(102, 'Bob Smith'),
(103, 'Carol Lee');

CREATE TABLE publishers (
  publisher_id INTEGER,
  publisher_name TEXT
);

INSERT INTO publishers (publisher_id, publisher_name) VALUES
(201, 'Sunshine Books'),
(202, 'Tech Press');

-- Write the SELECT query with two LEFT JOINs
-- Your code here

Hint

Use LEFT JOIN twice to connect books with authors and publishers using the matching ID columns.

4

Complete the query with ordering and aliasing

Modify the previous SQL query to order the results by books.title alphabetically. Also, use column aliases to rename the output columns as BookTitle, AuthorName, and PublisherName.

SQL

CREATE TABLE books (
  book_id INTEGER,
  title TEXT,
  author_id INTEGER,
  publisher_id INTEGER
);

INSERT INTO books (book_id, title, author_id, publisher_id) VALUES
(1, 'The Great Adventure', 101, 201),
(2, 'Mystery of the Night', 102, NULL),
(3, 'Learning SQL', 103, 202);

CREATE TABLE authors (
  author_id INTEGER,
  author_name TEXT
);

INSERT INTO authors (author_id, author_name) VALUES
(101, 'Alice Johnson'),
(102, 'Bob Smith'),
(103, 'Carol Lee');

CREATE TABLE publishers (
  publisher_id INTEGER,
  publisher_name TEXT
);

INSERT INTO publishers (publisher_id, publisher_name) VALUES
(201, 'Sunshine Books'),
(202, 'Tech Press');

SELECT books.title, authors.author_name, publishers.publisher_name
FROM books
LEFT JOIN authors ON books.author_id = authors.author_id
LEFT JOIN publishers ON books.publisher_id = publishers.publisher_id

-- Add ORDER BY and column aliases
-- Your code here

Hint

Use AS to rename columns and ORDER BY books.title to sort the results alphabetically.

Practice

1. What is the main purpose of using multiple LEFT JOIN clauses in a single SQL query?

easy

A. To delete rows from multiple tables at once

B. To combine rows from several tables while keeping all rows from the first table

C. To return only rows that have matching values in all joined tables

D. To update multiple tables simultaneously

Solution

1. Step 1: Understand LEFT JOIN behavior

   A LEFT JOIN returns all rows from the left (first) table and matching rows from the right table. If no match, NULLs appear.

2. Step 2: Apply to multiple LEFT JOINs

   Using multiple LEFT JOINs keeps all rows from the main table and adds data from each joined table if available.

3. Final Answer:

   To combine rows from several tables while keeping all rows from the first table -> Option B

4. Quick Check:

   Multiple LEFT JOINs keep all main table rows [OK]

Hint: LEFT JOIN keeps all main table rows even if others miss data [OK]

Common Mistakes:

• Confusing LEFT JOIN with INNER JOIN which filters rows
• Thinking LEFT JOIN deletes or updates data
• Assuming NULLs mean errors instead of missing matches

2. Which of the following SQL queries correctly uses multiple LEFT JOINs to combine tables orders, customers, and payments?

easy

A. SELECT o.id, c.name, p.amount FROM orders o LEFT JOIN customers c ON o.customer_id = c.id LEFT JOIN payments p ON o.id = p.order_id;

B. SELECT o.id, c.name, p.amount FROM orders o JOIN customers c ON o.customer_id = c.id JOIN payments p ON o.id = p.order_id;

C. SELECT o.id, c.name, p.amount FROM orders o LEFT JOIN customers c ON c.id = o.customer_id RIGHT JOIN payments p ON p.order_id = o.id;

D. SELECT o.id, c.name, p.amount FROM orders o INNER JOIN customers c ON o.customer_id = c.id LEFT JOIN payments p ON o.id = p.order_id;

Solution

1. Step 1: Check JOIN types

   SELECT o.id, c.name, p.amount FROM orders o LEFT JOIN customers c ON o.customer_id = c.id LEFT JOIN payments p ON o.id = p.order_id; uses LEFT JOIN twice, correctly keeping all orders and adding customer and payment info if available.

2. Step 2: Identify errors in other options

   SELECT o.id, c.name, p.amount FROM orders o JOIN customers c ON o.customer_id = c.id JOIN payments p ON o.id = p.order_id; uses INNER JOINs, which exclude orders without customers or payments. SELECT o.id, c.name, p.amount FROM orders o LEFT JOIN customers c ON c.id = o.customer_id RIGHT JOIN payments p ON p.order_id = o.id; mixes LEFT and RIGHT JOIN incorrectly. SELECT o.id, c.name, p.amount FROM orders o INNER JOIN customers c ON o.customer_id = c.id LEFT JOIN payments p ON o.id = p.order_id; uses INNER JOIN first, losing unmatched orders.

3. Final Answer:

   SELECT o.id, c.name, p.amount FROM orders o LEFT JOIN customers c ON o.customer_id = c.id LEFT JOIN payments p ON o.id = p.order_id; -> Option A

4. Quick Check:

   Multiple LEFT JOINs keep all orders [OK]

Hint: Use LEFT JOIN for all tables to keep main table rows [OK]

Common Mistakes:

• Using INNER JOIN instead of LEFT JOIN loses unmatched rows
• Mixing JOIN types without clear logic
• Incorrect ON conditions causing wrong matches

3. Given tables:
users(id, name),
posts(id, user_id, title),
comments(id, post_id, content)
What will this query return?

SELECT u.name, p.title, c.content FROM users u LEFT JOIN posts p ON u.id = p.user_id LEFT JOIN comments c ON p.id = c.post_id ORDER BY u.id, p.id, c.id;

medium

A. All posts with their users and comments; excludes users without posts

B. Only users who have posts and comments

C. All users with their posts and comments; NULLs if no posts or comments

D. Only comments with matching posts and users

Solution

1. Step 1: Analyze first LEFT JOIN

   The first LEFT JOIN keeps all users, adding posts if they exist; users without posts get NULLs for post columns.

2. Step 2: Analyze second LEFT JOIN

   The second LEFT JOIN adds comments for each post; if no comments, comment columns are NULL. Users without posts have NULL posts, so comments also NULL.

3. Final Answer:

   All users with their posts and comments; NULLs if no posts or comments -> Option C

4. Quick Check:

   LEFT JOINs keep all users, add posts and comments if present [OK]

Hint: LEFT JOIN keeps all left table rows, fills NULLs if no matches [OK]

Common Mistakes:

• Assuming INNER JOIN behavior filters rows
• Thinking NULLs mean errors instead of missing data
• Ignoring order of JOINs affecting results

4. Identify the error in this SQL query using multiple LEFT JOINs:

SELECT a.id, b.name, c.status FROM tableA a LEFT JOIN tableB b ON a.id = b.a_id LEFT JOIN tableC c ON b.id = c.b_id WHERE c.status = 'active';

medium

A. The SELECT clause must include all columns from joined tables

B. The JOIN conditions are missing ON clauses

C. Using LEFT JOIN twice is not allowed

D. The WHERE clause filters out rows where c.status is NULL, negating LEFT JOIN effect

Solution

1. Step 1: Understand WHERE with LEFT JOIN

   The WHERE clause filters rows after JOINs. Filtering on c.status excludes rows where c is NULL, removing unmatched rows.

2. Step 2: Effect on LEFT JOIN

   This makes the LEFT JOIN behave like INNER JOIN, losing rows from tableA without matching tableC rows.

3. Final Answer:

   The WHERE clause filters out rows where c.status is NULL, negating LEFT JOIN effect -> Option D

4. Quick Check:

   Filtering on joined table in WHERE breaks LEFT JOIN [OK]

Hint: Use WHERE on main table only; filter joined tables in ON [OK]

Common Mistakes:

• Filtering joined table columns in WHERE instead of ON
• Assuming multiple LEFT JOINs are invalid
• Missing ON conditions causing cross joins

5. You have tables:
employees(id, name, department_id),
departments(id, dept_name),
projects(id, dept_id, project_name),
tasks(id, project_id, task_name).
Write a query to list all employees with their department, projects, and tasks. Include employees even if they have no department, projects, or tasks. Which query achieves this?

hard

A. SELECT e.name, d.dept_name, p.project_name, t.task_name FROM employees e LEFT JOIN departments d ON e.department_id = d.id LEFT JOIN projects p ON d.id = p.dept_id LEFT JOIN tasks t ON p.id = t.project_id;

B. SELECT e.name, d.dept_name, p.project_name, t.task_name FROM employees e LEFT JOIN departments d ON e.id = d.id LEFT JOIN projects p ON d.id = p.dept_id LEFT JOIN tasks t ON p.id = t.project_id;

C. SELECT e.name, d.dept_name, p.project_name, t.task_name FROM employees e INNER JOIN departments d ON e.id = d.id INNER JOIN projects p ON d.id = p.dept_id INNER JOIN tasks t ON p.id = t.project_id;

D. SELECT e.name, d.dept_name, p.project_name, t.task_name FROM employees e LEFT JOIN departments d ON e.id = d.id LEFT JOIN projects p ON e.id = p.dept_id LEFT JOIN tasks t ON p.id = t.project_id;

Solution

1. Step 1: Identify correct JOIN keys

   Employees link to departments by e.department_id = d.id, not e.id = d.id. Projects link to departments by dept_id, tasks to projects by project_id.

2. Step 2: Use LEFT JOINs to keep all employees

   LEFT JOINs keep all employees even if no department, projects, or tasks exist.

3. Step 3: Check each option

   SELECT e.name, d.dept_name, p.project_name, t.task_name FROM employees e LEFT JOIN departments d ON e.department_id = d.id LEFT JOIN projects p ON d.id = p.dept_id LEFT JOIN tasks t ON p.id = t.project_id; uses correct keys and LEFT JOINs. Other queries use wrong keys (joining on e.id = d.id or e.id = p.dept_id) or INNER JOINs, excluding employees without matches.

4. Final Answer:

   SELECT e.name, d.dept_name, p.project_name, t.task_name FROM employees e LEFT JOIN departments d ON e.department_id = d.id LEFT JOIN projects p ON d.id = p.dept_id LEFT JOIN tasks t ON p.id = t.project_id; -> Option A

5. Quick Check:

   Correct keys + LEFT JOINs keep all employees [OK]

Hint: Match keys correctly and use LEFT JOINs to keep all main rows [OK]

Common Mistakes:

• Joining on wrong columns causing missing data
• Using INNER JOINs losing employees without projects
• Confusing employee ID with department ID