LEFT JOIN in MySQL - Mini Project: Build & Apply

Understanding LEFT JOIN in MySQL

📖 Scenario: You are managing a small bookstore database. You have two tables: authors and books. Some authors have written books, but some authors have not yet published any books. You want to create a list of all authors along with their books if they have any.

🎯 Goal: Build a MySQL query using LEFT JOIN to list all authors and their books. If an author has no books, still show the author with NULL for the book details.

📋 What You'll Learn

Create a table called authors with columns author_id (integer) and author_name (string).

Create a table called books with columns book_id (integer), title (string), and author_id (integer).

Insert the exact authors and books data as specified.

Write a LEFT JOIN query to list all authors and their books.

Ensure authors without books still appear in the result with NULL book fields.

💡 Why This Matters

🌍 Real World

LEFT JOIN is useful when you want to list all items from one group and optionally show related data from another group, like all customers and their orders, even if some customers have no orders.

💼 Career

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

1

Create the authors table and insert data

Create a table called authors with columns author_id as INT and author_name as VARCHAR(50). Insert these exact rows: (1, 'Jane Austen'), (2, 'Mark Twain'), (3, 'Virginia Woolf').

MySQL

-- Create the authors table and insert data
-- Your code here

Hint

Use CREATE TABLE to make the table and INSERT INTO to add rows.

2

Create the books table and insert data

Create a table called books with columns book_id as INT, title as VARCHAR(100), and author_id as INT. Insert these exact rows: (101, 'Pride and Prejudice', 1), (102, 'Adventures of Huckleberry Finn', 2).

MySQL

CREATE TABLE authors (
  author_id INT,
  author_name VARCHAR(50)
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain'),
(3, 'Virginia Woolf');

-- Create the books table and insert data
-- Your code here

Hint

Remember to match the column names and data types exactly.

3

Write a LEFT JOIN query to list all authors and their books

Write a SELECT query that uses LEFT JOIN to join authors with books on author_id. Select authors.author_name and books.title. Use LEFT JOIN so all authors appear even if they have no books.

MySQL

CREATE TABLE authors (
  author_id INT,
  author_name VARCHAR(50)
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain'),
(3, 'Virginia Woolf');

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100),
  author_id INT
);

INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Pride and Prejudice', 1),
(102, 'Adventures of Huckleberry Finn', 2);

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

Hint

Use LEFT JOIN to keep all authors even if no matching book exists.

4

Complete the query with ordering and aliasing

Modify the previous query to order the results by authors.author_name ascending. Also, alias authors.author_name as Author and books.title as Book in the SELECT clause.

MySQL

CREATE TABLE authors (
  author_id INT,
  author_name VARCHAR(50)
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain'),
(3, 'Virginia Woolf');

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100),
  author_id INT
);

INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Pride and Prejudice', 1),
(102, 'Adventures of Huckleberry Finn', 2);

-- Modify the query to add aliases and order by author_name
-- Your code here

Hint

Use AS to rename columns and ORDER BY to sort results.

Practice

1. What does a LEFT JOIN do in SQL?

easy

A. Deletes rows from the left table that do not have matches in the right table.

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

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

D. Returns all rows from the left table and matching rows from the right table, NULL if no match.

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: Check what happens with unmatched rows

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

3. Final Answer:

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

4. Quick Check:

   LEFT JOIN = all left rows + matched right rows [OK]

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

Common Mistakes:

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

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

easy

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

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

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

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

Solution

1. Step 1: Review standard LEFT JOIN syntax

   The correct syntax uses LEFT JOIN followed by ON condition to specify matching columns.

2. Step 2: Check each option

   SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id; uses correct syntax: LEFT JOIN with ON clause. 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; uses WHERE instead of ON, which is incorrect for JOIN condition. SELECT * FROM table1 LEFT JOIN table2 WHERE table1.id = table2.id; uses WHERE instead of ON, invalid syntax.

3. Final Answer:

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

4. Quick Check:

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

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

Common Mistakes:

• Using JOIN LEFT instead of LEFT JOIN
• Using WHERE instead of ON for join condition
• Putting join condition directly in WHERE after table

3. Given these tables:

Employees(id, name)
Departments(id, dept_name, emp_id)

What is the output of this query?

SELECT e.name, d.dept_name FROM Employees e LEFT JOIN Departments d ON e.id = d.emp_id ORDER BY e.id;

Assuming:
Employees: (1, 'Alice'), (2, 'Bob'), (3, 'Carol')
Departments: (10, 'HR', 1), (20, 'IT', 3)

medium

A. [('Alice', NULL), ('Bob', NULL), ('Carol', NULL)]

B. [('Alice', 'HR'), ('Bob', 'IT'), ('Carol', NULL)]

C. [('Alice', 'HR'), ('Bob', NULL), ('Carol', 'IT')]

D. [('Alice', 'HR'), ('Carol', 'IT')]

Solution

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

   LEFT JOIN keeps all Employees. For each employee, it tries to find a matching department where e.id = d.emp_id.

2. Step 2: Check matches and NULLs

   'Alice' (id=1) matches department 'HR' (emp_id=1). 'Bob' (id=2) has no matching department, so dept_name is NULL. 'Carol' (id=3) matches 'IT' (emp_id=3).

3. Final Answer:

   [('Alice', 'HR'), ('Bob', NULL), ('Carol', 'IT')] -> Option C

4. Quick Check:

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

Hint: LEFT JOIN shows all left rows; unmatched right columns are NULL [OK]

Common Mistakes:

• Assuming unmatched rows are dropped
• Mixing up emp_id and id columns
• Ignoring ORDER BY effect

4. Identify the error in this LEFT JOIN query:

SELECT a.id, b.value FROM tableA a LEFT JOIN tableB b ON a.id = b.id WHERE b.value > 10;

medium

A. The WHERE clause filters out rows where b.value is NULL, defeating LEFT JOIN purpose.

B. The ON clause is missing a join condition.

C. LEFT JOIN should be replaced with INNER JOIN for this query.

D. The SELECT statement is missing table aliases.

Solution

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

   LEFT JOIN keeps all rows from tableA, but WHERE b.value > 10 excludes rows where b.value is NULL (no match), effectively turning it into INNER JOIN.

2. Step 2: Identify the error

   The error is using WHERE to filter on right table columns after LEFT JOIN, which removes unmatched rows.

3. Final Answer:

   The WHERE clause filters out rows where b.value is NULL, defeating LEFT JOIN purpose. -> Option A

4. Quick Check:

   WHERE on right table column after LEFT JOIN removes unmatched rows [OK]

Hint: Use ON for filtering right table, not WHERE, to keep unmatched rows [OK]

Common Mistakes:

• Using WHERE instead of ON for right table filters
• Confusing LEFT JOIN with INNER JOIN
• Ignoring NULLs in join columns

5. You have two tables:

Products(product_id, name)
Sales(sale_id, product_id, quantity)

You want to list all products with total quantity sold, including products with zero sales.
Which query correctly uses LEFT JOIN to achieve this?

hard

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

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

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

D. SELECT p.name, COUNT(s.sale_id) FROM Products p LEFT JOIN Sales s ON p.product_id = s.product_id WHERE s.quantity > 0 GROUP BY p.product_id;

Solution

1. Step 1: Use LEFT JOIN from Products to Sales

   To include all products even with no sales, LEFT JOIN Sales to Products on product_id.

2. Step 2: Aggregate sales quantity and group by product

   Use SUM(s.quantity) to total sales per product. Group by p.product_id to get one row per product.

3. Final Answer:

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

4. Quick Check:

   LEFT JOIN + SUM + GROUP BY lists all products with total sales [OK]

Hint: LEFT JOIN products to sales, SUM quantity, GROUP BY product [OK]

Common Mistakes:

• Using INNER JOIN excludes products with zero sales
• Joining Sales to Products reverses LEFT JOIN logic
• Filtering in WHERE removes unmatched rows