Why JOINs combine related tables in MySQL - See It in Action

Why JOINs combine related tables

📖 Scenario: You work at a small bookstore. The store keeps two tables: one for books and one for authors. Each book has an author ID that matches an author in the authors table. You want to see the book titles along with their authors' names.

🎯 Goal: Build a simple SQL query using a JOIN to combine the books and authors tables so you can see each book's title with the correct author's name.

📋 What You'll Learn

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

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

Insert exactly these authors into authors: (1, 'Jane Austen'), (2, 'Mark Twain').

Insert exactly these books into books: (101, 'Pride and Prejudice', 1), (102, 'Adventures of Huckleberry Finn', 2).

Write a SQL SELECT query using JOIN to combine books and authors on author_id.

Select the title from books and the author_name from authors.

💡 Why This Matters

🌍 Real World

Combining related tables is common in real databases to get meaningful information from separate data sources, like matching books to their authors.

💼 Career

Understanding JOINs is essential for database jobs, data analysis, and backend development where data from multiple tables must be combined.

1

Create the authors and books tables

Create a table called authors with columns author_id as INTEGER and author_name as TEXT. Then create a table called books with columns book_id as INTEGER, title as TEXT, and author_id as INTEGER.

MySQL

-- Create the authors table
-- Create the books table

Need a hint?

Use CREATE TABLE statements with the exact column names and types.

2

Insert data into authors and books tables

Insert these exact rows into authors: (1, 'Jane Austen') and (2, 'Mark Twain'). Insert these exact rows into books: (101, 'Pride and Prejudice', 1) and (102, 'Adventures of Huckleberry Finn', 2).

MySQL

CREATE TABLE authors (
  author_id INTEGER,
  author_name TEXT
);

CREATE TABLE books (
  book_id INTEGER,
  title TEXT,
  author_id INTEGER
);

-- Insert authors data
-- Insert books data

Need a hint?

Use INSERT INTO with the exact values given.

3

Write a JOIN query to combine books and authors

Write a SQL SELECT query that uses JOIN to combine books and authors on the author_id column. Select the title from books and the author_name from authors.

MySQL

CREATE TABLE authors (
  author_id INTEGER,
  author_name TEXT
);

CREATE TABLE books (
  book_id INTEGER,
  title TEXT,
  author_id INTEGER
);

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

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

-- Write the JOIN query below

Need a hint?

Use JOIN with ON books.author_id = authors.author_id to combine the tables.

4

Complete the query with an ORDER BY clause

Add an ORDER BY clause to the existing query to sort the results by books.title in ascending order.

MySQL

CREATE TABLE authors (
  author_id INTEGER,
  author_name TEXT
);

CREATE TABLE books (
  book_id INTEGER,
  title TEXT,
  author_id INTEGER
);

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

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

SELECT books.title, authors.author_name
FROM books
JOIN authors ON books.author_id = authors.author_id
-- Add ORDER BY clause here

Need a hint?

Use ORDER BY books.title ASC to sort the results alphabetically by title.