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

Need a 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

Need a 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

Need a 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

Need a hint?

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