Finding unmatched rows with LEFT JOIN in SQL - Mini Project: Build & Apply

Finding unmatched rows with LEFT JOIN

📖 Scenario: You work at a small bookstore. You have two tables: books and sales. The books table lists all books in the store. The sales table records which books have been sold.You want to find books that have never been sold yet.

🎯 Goal: Create a SQL query that finds all books from the books table that do not have matching entries in the sales table using a LEFT JOIN.

📋 What You'll Learn

Create a books table with columns book_id and title and insert 3 books with IDs 1, 2, 3 and titles 'Book A', 'Book B', 'Book C'.

Create a sales table with columns sale_id and book_id and insert 2 sales for books 1 and 2.

Write a LEFT JOIN query joining books and sales on book_id.

Filter the results to show only books with no matching sales (unmatched rows).

💡 Why This Matters

🌍 Real World

Finding unmatched rows is useful in real life to identify missing or incomplete data, such as customers who never made a purchase or products never sold.

💼 Career

Database developers and analysts often use LEFT JOIN with NULL filtering to find gaps in data, which helps in reporting, data cleaning, and business decisions.

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). Insert these exact rows: (1, 'Book A'), (2, 'Book B'), and (3, 'Book C').

SQL

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

Need a hint?

Use CREATE TABLE books (book_id INTEGER, title TEXT); to create the table.

Use INSERT INTO books (book_id, title) VALUES (...); to add each book.

2

Create the sales table and insert data

Write SQL statements to create a table called sales with columns sale_id (integer) and book_id (integer). Insert these exact rows: (1, 1) and (2, 2) representing sales of books 1 and 2.

SQL

CREATE TABLE books (book_id INTEGER, title TEXT);
INSERT INTO books (book_id, title) VALUES (1, 'Book A');
INSERT INTO books (book_id, title) VALUES (2, 'Book B');
INSERT INTO books (book_id, title) VALUES (3, 'Book C');
-- Create the sales table and insert 2 rows
-- Your code here

Need a hint?

Use CREATE TABLE sales (sale_id INTEGER, book_id INTEGER); to create the table.

Use INSERT INTO sales (sale_id, book_id) VALUES (...); to add each sale.

3

Write a LEFT JOIN query to join books and sales

Write a SQL query that selects books.book_id and books.title from the books table left joined with the sales table on books.book_id = sales.book_id.

SQL

CREATE TABLE books (book_id INTEGER, title TEXT);
INSERT INTO books (book_id, title) VALUES (1, 'Book A');
INSERT INTO books (book_id, title) VALUES (2, 'Book B');
INSERT INTO books (book_id, title) VALUES (3, 'Book C');
CREATE TABLE sales (sale_id INTEGER, book_id INTEGER);
INSERT INTO sales (sale_id, book_id) VALUES (1, 1);
INSERT INTO sales (sale_id, book_id) VALUES (2, 2);

-- Write a LEFT JOIN query selecting books.book_id and books.title
-- Your code here

Need a hint?

Use LEFT JOIN sales ON books.book_id = sales.book_id to join the tables.

Select books.book_id and books.title.

4

Filter to find books with no matching sales

Add a WHERE clause to the previous query to select only rows where sales.book_id is NULL. This shows books that have no sales.

SQL

CREATE TABLE books (book_id INTEGER, title TEXT);
INSERT INTO books (book_id, title) VALUES (1, 'Book A');
INSERT INTO books (book_id, title) VALUES (2, 'Book B');
INSERT INTO books (book_id, title) VALUES (3, 'Book C');
CREATE TABLE sales (sale_id INTEGER, book_id INTEGER);
INSERT INTO sales (sale_id, book_id) VALUES (1, 1);
INSERT INTO sales (sale_id, book_id) VALUES (2, 2);

SELECT books.book_id, books.title
FROM books
LEFT JOIN sales ON books.book_id = sales.book_id
-- Add WHERE clause to filter unmatched rows
-- Your code here

Need a hint?

Use WHERE sales.book_id IS NULL to find books without sales.