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

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

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

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

Hint

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

Practice

1. What does a LEFT JOIN do in SQL?

easy

A. Deletes rows from the left table that have no match in the right table.

B. Keeps only rows that have matches in both tables.

C. Keeps all rows from the right table and adds matching rows from the left table.

D. Keeps all rows from the left table and adds matching rows from the right table or NULL if no match.

Solution

1. Step 1: Understand LEFT JOIN behavior

   A LEFT JOIN returns all rows from the left table regardless of matches in the right table.

2. Step 2: Check what happens with unmatched rows

   If there is no matching row in the right table, the result shows NULL for right table columns.

3. Final Answer:

   Keeps all rows from the left table and adds matching rows from the right table or NULL if no match. -> Option D

4. Quick Check:

   LEFT JOIN = all left rows kept [OK]

Hint: Remember: LEFT JOIN keeps all left rows, fills right with NULL if no match [OK]

Common Mistakes:

• Confusing LEFT JOIN with INNER JOIN
• Thinking it keeps all right table rows
• Assuming unmatched rows are dropped

2. Which of the following is the correct syntax for a LEFT JOIN in SQL?

easy

A. SELECT * FROM table1 LEFT ON JOIN table2 WHERE table1.id = table2.id;

B. SELECT * FROM table1 JOIN LEFT table2 ON table1.id = table2.id;

C. SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

D. SELECT * FROM table1 LEFT JOIN table2 WHERE table1.id = table2.id;

Solution

1. Step 1: Review correct LEFT JOIN syntax

   The correct syntax is: SELECT columns FROM left_table LEFT JOIN right_table ON condition;

2. Step 2: Identify syntax errors in other options

   Options A, B, and D misuse keywords or omit ON clause, causing syntax errors.

3. Final Answer:

   SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id; -> Option C

4. Quick Check:

   LEFT JOIN syntax = SELECT ... LEFT JOIN ... ON ... [OK]

Hint: LEFT JOIN always uses ON to specify join condition [OK]

Common Mistakes:

• Swapping JOIN and LEFT keywords
• Using WHERE instead of ON for join condition
• Omitting ON clause

3. Given these tables:

Employees
id | name
1 | Alice
2 | Bob
3 | Carol

Sales
emp_id | amount
1 | 100
3 | 200

What is the result of:
SELECT Employees.name, Sales.amount FROM Employees LEFT JOIN Sales ON Employees.id = Sales.emp_id;

medium

A. [('Alice', 100), ('Bob', 0), ('Carol', 200)]

B. [('Alice', 100), ('Bob', NULL), ('Carol', 200)]

C. [('Alice', 100), ('Carol', 200)]

D. [('Bob', NULL)]

Solution

1. Step 1: Match Employees with Sales using LEFT JOIN

   All Employees rows appear. For matching emp_id in Sales, amount is shown; else NULL.

2. Step 2: Map each employee to sales amount or NULL

   Alice (id=1) matches 100, Bob (id=2) no match so NULL, Carol (id=3) matches 200.

3. Final Answer:

   [('Alice', 100), ('Bob', NULL), ('Carol', 200)] -> Option B

4. Quick Check:

   LEFT JOIN keeps all left rows with NULL for no match [OK]

Hint: LEFT JOIN shows NULL for unmatched right rows, not zero [OK]

Common Mistakes:

• Replacing NULL with zero
• Omitting unmatched rows
• Confusing LEFT JOIN with INNER JOIN

4. Consider this query:

SELECT a.id, b.value FROM A LEFT JOIN B ON a.id = b.a_id WHERE b.value > 10;

What is the problem with this query if you want to keep all rows from A?

medium

A. The WHERE clause filters out rows where b.value is NULL, losing some left rows.

B. The ON clause is missing a join condition.

C. LEFT JOIN should be replaced with INNER JOIN for correct results.

D. The SELECT statement is missing table aliases.

Solution

1. Step 1: Understand effect of WHERE on LEFT JOIN

   WHERE filters after join, so rows with NULL b.value are removed, losing left rows.

2. Step 2: Identify how to fix to keep all left rows

   Move condition to ON clause or use WHERE b.value > 10 OR b.value IS NULL to preserve unmatched rows.

3. Final Answer:

   The WHERE clause filters out rows where b.value is NULL, losing some left rows. -> Option A

4. Quick Check:

   WHERE after LEFT JOIN can remove unmatched rows [OK]

Hint: Put filters on right table in ON, not WHERE, to keep all left rows [OK]

Common Mistakes:

• Assuming WHERE doesn't affect LEFT JOIN results
• Confusing ON and WHERE clauses
• Replacing LEFT JOIN with INNER JOIN unnecessarily

5. You have two tables:

Products
product_id | name
1 | Pen
2 | Pencil
3 | Eraser

Sales
product_id | quantity
1 | 10
1 | 5
3 | 7

Write a query using LEFT JOIN to get each product's total sales quantity, showing 0 if no sales exist.

hard

A. SELECT p.name, COALESCE(SUM(s.quantity), 0) AS total FROM Products p LEFT JOIN Sales s ON p.product_id = s.product_id GROUP BY p.name;

B. SELECT p.name, SUM(s.quantity) AS total FROM Products p INNER JOIN Sales s ON p.product_id = s.product_id GROUP BY p.name;

C. SELECT p.name, SUM(s.quantity) AS total FROM Products p LEFT JOIN Sales s ON p.product_id = s.product_id WHERE s.quantity > 0 GROUP BY p.name;

D. SELECT p.name, SUM(s.quantity) AS total FROM Sales s LEFT JOIN Products p ON s.product_id = p.product_id GROUP BY p.name;

Solution

1. Step 1: Use LEFT JOIN to keep all products

   LEFT JOIN ensures all products appear even if no sales exist.

2. Step 2: Use COALESCE with SUM to show 0 for no sales

   SUM returns NULL if no matching rows; COALESCE converts NULL to 0.

3. Final Answer:

   SELECT p.name, COALESCE(SUM(s.quantity), 0) AS total FROM Products p LEFT JOIN Sales s ON p.product_id = s.product_id GROUP BY p.name; -> Option A

4. Quick Check:

   LEFT JOIN + COALESCE(SUM()) = total sales with zeros [OK]

Hint: Use COALESCE(SUM()) with LEFT JOIN to replace NULL totals with zero [OK]

Common Mistakes:

• Using INNER JOIN losing products with no sales
• Not handling NULL sums with COALESCE
• Filtering in WHERE removing unmatched rows