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

Need a 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

Need a 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

Need a 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

Need a hint?

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