LEFT JOIN preserving all left rows in SQL - Mini Project: Build & Apply

LEFT JOIN preserving all left rows

📖 Scenario: You work for a small bookstore that keeps two tables: one for books and one for sales. Some books may not have any sales yet. You want to create a report that shows all books, including those without sales, so the store can see which books have not sold yet.

🎯 Goal: Build a SQL query using LEFT JOIN to list all books with their sales information if available, preserving all books even if they have no sales.

📋 What You'll Learn

Create a table called books with columns book_id (integer) and title (text).

Create a table called sales with columns sale_id (integer), book_id (integer), and quantity (integer).

Insert the exact data into books: (1, 'The Great Gatsby'), (2, '1984'), (3, 'To Kill a Mockingbird').

Insert the exact data into sales: (101, 1, 3), (102, 1, 2), (103, 3, 5).

Write a LEFT JOIN query to select all books and their sales quantities, showing NULL for sales if none exist.

💡 Why This Matters

🌍 Real World

Bookstores and many businesses use LEFT JOIN to create reports that include all items, even those without related records like sales or orders.

💼 Career

Understanding LEFT JOIN is essential for data analysts and database developers to write queries that show complete data sets including missing or unmatched records.

1

Create the books table and insert data

Write SQL statements to create a table called books with columns book_id (integer) and title (text). Then insert these exact rows: (1, 'The Great Gatsby'), (2, '1984'), and (3, 'To Kill a Mockingbird').

SQL

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

Need a hint?

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

2

Create the sales table and insert data

Write SQL statements to create a table called sales with columns sale_id (integer), book_id (integer), and quantity (integer). Then insert these exact rows: (101, 1, 3), (102, 1, 2), and (103, 3, 5).

SQL

CREATE TABLE books (book_id INTEGER, title TEXT);
INSERT INTO books (book_id, title) VALUES (1, 'The Great Gatsby');
INSERT INTO books (book_id, title) VALUES (2, '1984');
INSERT INTO books (book_id, title) VALUES (3, 'To Kill a Mockingbird');
-- Create the sales table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE and INSERT INTO like before, but include all three columns.

3

Write the LEFT JOIN query to combine books and sales

Write a SQL query that selects books.book_id, books.title, and sales.quantity from the books table left joined with the sales table on books.book_id = sales.book_id. This query should preserve all rows from books even if there is no matching sale.

SQL

CREATE TABLE books (book_id INTEGER, title TEXT);
INSERT INTO books (book_id, title) VALUES (1, 'The Great Gatsby');
INSERT INTO books (book_id, title) VALUES (2, '1984');
INSERT INTO books (book_id, title) VALUES (3, 'To Kill a Mockingbird');
CREATE TABLE sales (sale_id INTEGER, book_id INTEGER, quantity INTEGER);
INSERT INTO sales (sale_id, book_id, quantity) VALUES (101, 1, 3);
INSERT INTO sales (sale_id, book_id, quantity) VALUES (102, 1, 2);
INSERT INTO sales (sale_id, book_id, quantity) VALUES (103, 3, 5);
-- Write the LEFT JOIN query below
-- Your code here

Need a hint?

Use LEFT JOIN to keep all books and match sales where possible.

4

Complete the query by ordering results by book_id

Add an ORDER BY books.book_id clause at the end of the query to sort the results by book_id in ascending order.

SQL

CREATE TABLE books (book_id INTEGER, title TEXT);
INSERT INTO books (book_id, title) VALUES (1, 'The Great Gatsby');
INSERT INTO books (book_id, title) VALUES (2, '1984');
INSERT INTO books (book_id, title) VALUES (3, 'To Kill a Mockingbird');
CREATE TABLE sales (sale_id INTEGER, book_id INTEGER, quantity INTEGER);
INSERT INTO sales (sale_id, book_id, quantity) VALUES (101, 1, 3);
INSERT INTO sales (sale_id, book_id, quantity) VALUES (102, 1, 2);
INSERT INTO sales (sale_id, book_id, quantity) VALUES (103, 3, 5);
SELECT books.book_id, books.title, sales.quantity
FROM books
LEFT JOIN sales ON books.book_id = sales.book_id
-- Add ORDER BY clause below
-- Your code here

Need a hint?

Use ORDER BY books.book_id to sort the output by book ID.