Why outer joins are needed in SQL - See It in Action

Understanding Why Outer Joins Are Needed

📖 Scenario: You work at a small bookstore. You have two tables: one with books and another with sales. Some books have sales records, but some do not because they haven't been sold yet.

🎯 Goal: Build a query that shows all books and their sales if any. If a book has no sales, it should still appear in the list with a NULL for sales. This will help the store owner see which books have not sold yet.

📋 What You'll Learn

Create a books table with columns book_id and title.

Create a sales table with columns sale_id, book_id, and quantity.

Insert exact data into both tables as specified.

Write a query using a LEFT OUTER JOIN to list all books with their sales quantities if available.

Ensure books without sales still appear with NULL in the sales columns.

💡 Why This Matters

🌍 Real World

Bookstores and many businesses need to see all items in inventory even if they have no sales yet. Outer joins help show this complete picture.

💼 Career

Understanding outer joins is essential for data analysts and database developers to write queries that handle missing or incomplete data gracefully.

1

Create the books table and insert data

Create a table called books with columns book_id (integer) and title (text). Insert these exact rows: (1, 'The Great Gatsby'), (2, '1984'), (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

Create a table called sales with columns sale_id (integer), book_id (integer), and quantity (integer). Insert these exact rows: (1, 1, 5), (2, 2, 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?

Remember to match the column names and insert the exact rows.

3

Write a LEFT OUTER JOIN query to combine books and sales

Write a SQL query that selects books.book_id, books.title, and sales.quantity. Use a LEFT OUTER JOIN on books.book_id = sales.book_id to include all books even if they have no sales.

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,
  quantity INTEGER
);

INSERT INTO sales (sale_id, book_id, quantity) VALUES
(1, 1, 5),
(2, 2, 3);

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

Need a hint?

Use LEFT OUTER JOIN to keep all books even if no matching sales exist.

4

Complete the query with an ORDER BY clause

Add an ORDER BY books.book_id clause at the end of the query to list the books in order by their ID.

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,
  quantity INTEGER
);

INSERT INTO sales (sale_id, book_id, quantity) VALUES
(1, 1, 5),
(2, 2, 3);

SELECT books.book_id, books.title, sales.quantity
FROM books
LEFT OUTER 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 results by book ID.