LEFT JOIN with NULL result rows in SQL - Mini Project: Build & Apply

LEFT JOIN with NULL result rows

📖 Scenario: You are managing a small online bookstore database. You have two tables: books and sales. The books table lists all books available, and the sales table records sales transactions for some of these books. Some books may not have any sales yet.

🎯 Goal: Write a SQL query using LEFT JOIN to list all books along with their sales count. For books with no sales, the sales count should show as NULL.

📋 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) and book_id (integer).

Insert exactly these rows into books: (1, 'The Great Gatsby'), (2, '1984'), (3, 'To Kill a Mockingbird').

Insert exactly these rows into sales: (101, 1), (102, 1), (103, 3).

Write a LEFT JOIN query to join books with sales on book_id.

Select books.book_id, books.title, and the count of sales.sale_id as sales_count.

Group the results by books.book_id and books.title.

Ensure that books with no sales show sales_count as NULL (do not replace with zero).

💡 Why This Matters

🌍 Real World

Online stores often need to report all products with their sales, including those with no sales yet. LEFT JOIN helps show all products even if no sales exist.

💼 Career

Understanding LEFT JOIN and handling NULL results is essential for database querying in roles like data analyst, backend developer, and database administrator.

1

Create the books table and insert data

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

SQL

-- Create the books table
-- Your code here

Need a hint?

Use CREATE TABLE books (book_id INTEGER, title TEXT); and then INSERT INTO books (book_id, title) VALUES (...), (...), (...);

2

Create the sales table and insert data

Create a table called sales with columns sale_id (integer) and book_id (integer). Then insert these exact rows into sales: (101, 1), (102, 1), (103, 3).

SQL

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

Need a hint?

Use CREATE TABLE sales (sale_id INTEGER, book_id INTEGER); and then INSERT INTO sales (sale_id, book_id) VALUES (...), (...), (...);

3

Write the LEFT JOIN query to count sales per book

Write a SQL query that uses LEFT JOIN to join books with sales on book_id. Select books.book_id, books.title, and the count of sales.sale_id as sales_count. Group the results by books.book_id and books.title.

SQL

CREATE TABLE books (book_id INTEGER, title TEXT);
INSERT INTO books (book_id, title) VALUES
(1, 'The Great Gatsby'),
(2, '1984'),
(3, 'To Kill a Mockingbird');

CREATE TABLE sales (sale_id INTEGER, book_id INTEGER);
INSERT INTO sales (sale_id, book_id) VALUES
(101, 1),
(102, 1),
(103, 3);

-- Write the LEFT JOIN query below
-- Your code here

Need a hint?

Use LEFT JOIN to join books and sales on book_id. Use COUNT(sales.sale_id) to count sales per book. Group by books.book_id and books.title.

4

Modify the query to show NULL for books with no sales

Modify the previous query so that books with no sales show sales_count as NULL instead of zero. Do this by using NULLIF function to convert zero counts to NULL.

SQL

CREATE TABLE books (book_id INTEGER, title TEXT);
INSERT INTO books (book_id, title) VALUES
(1, 'The Great Gatsby'),
(2, '1984'),
(3, 'To Kill a Mockingbird');

CREATE TABLE sales (sale_id INTEGER, book_id INTEGER);
INSERT INTO sales (sale_id, book_id) VALUES
(101, 1),
(102, 1),
(103, 3);

-- Modify the query to show NULL for sales_count when zero
-- Your code here

Need a hint?

Use NULLIF(COUNT(sales.sale_id), 0) to convert zero counts to NULL.