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

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

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

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

Hint

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

Practice

1. What does a LEFT JOIN do in SQL?

easy

A. Returns all rows from the left table and matched rows from the right table, NULL if no match.

B. Returns only rows that have matching values in both tables.

C. Returns all rows from the right table and matched rows from the left table.

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

Solution

1. Step 1: Understand LEFT JOIN behavior

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

2. Step 2: Identify NULLs for unmatched rows

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

3. Final Answer:

   Returns all rows from the left table and matched rows from the right table, NULL if no match. -> Option A

4. Quick Check:

   LEFT JOIN = all left rows + NULL for no match [OK]

Hint: LEFT JOIN keeps all left rows, unmatched right rows show NULL [OK]

Common Mistakes:

• Confusing LEFT JOIN with INNER JOIN
• Thinking unmatched rows are dropped
• Assuming NULLs appear in left table columns

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

easy

A. SELECT * FROM table1 LEFT OUTER 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 USING (id);

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

Solution

1. Step 1: Review standard LEFT JOIN syntax

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

2. Step 2: Check each option

   SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id; matches the correct syntax exactly. SELECT * FROM table1 JOIN LEFT table2 ON table1.id = table2.id; has JOIN LEFT which is invalid. SELECT * FROM table1 LEFT OUTER JOIN table2 WHERE table1.id = table2.id; is invalid because JOIN requires an ON clause (syntax error). SELECT * FROM table1 LEFT JOIN table2 USING (id); is correct syntax with parentheses.

3. Final Answer:

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

4. Quick Check:

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

Hint: Use LEFT JOIN ... ON ... for correct syntax [OK]

Common Mistakes:

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

3. Given tables Employees and Departments with data:

Employees:
id | name | dept_id
1 | Alice | 10
2 | Bob | 20
3 | Carol | NULL

Departments:
dept_id | dept_name
10 | Sales
20 | HR
30 | IT

What is the result of this query?

SELECT e.name, d.dept_name FROM Employees e LEFT JOIN Departments d ON e.dept_id = d.dept_id;

medium

A. [{"name": "Alice", "dept_name": "Sales"}, {"name": "Bob", "dept_name": "HR"}]

B. [{"name": "Alice", "dept_name": "Sales"}, {"name": "Bob", "dept_name": "HR"}, {"name": "Carol", "dept_name": null}]

C. [{"name": "Alice", "dept_name": "Sales"}, {"name": "Bob", "dept_name": "HR"}, {"name": "Carol", "dept_name": "IT"}]

D. [{"name": "Alice", "dept_name": null}, {"name": "Bob", "dept_name": null}, {"name": "Carol", "dept_name": null}]

Solution

1. Step 1: Match Employees with Departments by dept_id

   Alice's dept_id 10 matches Sales, Bob's 20 matches HR, Carol's NULL has no match.

2. Step 2: Apply LEFT JOIN behavior

   All employees appear. For Carol, no matching department, so dept_name is NULL.

3. Final Answer:

   [{"name": "Alice", "dept_name": "Sales"}, {"name": "Bob", "dept_name": "HR"}, {"name": "Carol", "dept_name": null}] -> Option B

4. Quick Check:

   LEFT JOIN keeps all left rows, unmatched right columns NULL [OK]

Hint: LEFT JOIN shows NULL for unmatched right table rows [OK]

Common Mistakes:

• Omitting rows with NULL join keys
• Assuming unmatched rows get default values
• Confusing INNER JOIN output with LEFT JOIN

4. Consider this SQL query:

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

Why might this query return fewer rows than table A has?

medium

A. Because the query syntax is invalid and causes an error.

B. Because LEFT JOIN only returns rows with matching b.value > 10.

C. Because the WHERE clause filters out rows where b.value is NULL, removing unmatched rows.

D. Because the ON condition is incorrect and causes no matches.

Solution

1. Step 1: Understand LEFT JOIN with WHERE filter

   LEFT JOIN keeps all rows from A, but WHERE filters after join.

2. Step 2: Effect of WHERE on NULLs from unmatched rows

   Rows with no match have b.value as NULL, and WHERE b.value > 10 excludes NULLs, removing those rows.

3. Final Answer:

   Because the WHERE clause filters out rows where b.value is NULL, removing unmatched rows. -> Option C

4. Quick Check:

   WHERE filters NULLs after LEFT JOIN, reducing rows [OK]

Hint: WHERE on right table column after LEFT JOIN filters out NULLs [OK]

Common Mistakes:

• Thinking LEFT JOIN always keeps all left rows regardless of WHERE
• Confusing ON and WHERE filtering effects
• Assuming query syntax error causes fewer rows

5. You have two tables:

Orders:
order_id | customer_id
1 | 101
2 | 102
3 | 103

Customers:
customer_id | name
101 | John
102 | Jane

You want to list all orders with customer names, but show 'Unknown' if no customer found.

Which SQL query correctly achieves this?

hard

A. SELECT o.order_id, COALESCE(c.name, 'Unknown') AS customer_name FROM Orders o LEFT JOIN Customers c ON o.customer_id = c.customer_id;

B. SELECT o.order_id, IFNULL(c.name, 'Unknown') AS customer_name FROM Orders o INNER JOIN Customers c ON o.customer_id = c.customer_id;

C. SELECT o.order_id, c.name FROM Orders o RIGHT JOIN Customers c ON o.customer_id = c.customer_id;

D. SELECT o.order_id, CASE WHEN c.name IS NULL THEN 'Unknown' ELSE c.name END FROM Orders o JOIN Customers c ON o.customer_id = c.customer_id;

Solution

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

   LEFT JOIN keeps all orders even if no matching customer exists.

2. Step 2: Replace NULL customer names with 'Unknown'

   Use COALESCE to show 'Unknown' when c.name is NULL.

3. Final Answer:

   SELECT o.order_id, COALESCE(c.name, 'Unknown') AS customer_name FROM Orders o LEFT JOIN Customers c ON o.customer_id = c.customer_id; -> Option A

4. Quick Check:

   LEFT JOIN + COALESCE handles missing customers [OK]

Hint: Use LEFT JOIN with COALESCE to replace NULLs [OK]

Common Mistakes:

• Using INNER JOIN excludes orders without customers
• Using RIGHT JOIN reverses table roles incorrectly
• Forgetting to handle NULL customer names