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

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

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

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

Hint

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

Practice

1. Why do we use JOINs in SQL when working with multiple tables?

easy

A. To create new tables automatically

B. To delete rows from multiple tables at once

C. To combine rows from related tables based on matching columns

D. To change the data type of columns

Solution

1. Step 1: Understand the purpose of JOINs

   JOINs are used to combine rows from two or more tables based on a related column between them.

2. Step 2: Identify the correct use case

   Combining related data helps to see connected information in one result, which matches To combine rows from related tables based on matching columns.

3. Final Answer:

   To combine rows from related tables based on matching columns -> Option C

4. Quick Check:

   JOINs combine related tables = A [OK]

Hint: JOINs link tables by matching columns to combine data [OK]

Common Mistakes:

• Thinking JOINs delete or create tables
• Confusing JOINs with data type changes
• Assuming JOINs work without matching columns

2. Which of the following is the correct syntax to join two tables orders and customers on the column customer_id?

easy

A. SELECT * FROM orders JOIN customers WHER orders.customer_id = customers.customer_id;

B. SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id;

C. SELECT * FROM orders, customers ON orders.customer_id = customers.customer_id;

D. SELECT * FROM orders JOIN customers USING customer_id;

Solution

1. Step 1: Review JOIN syntax

   The correct JOIN syntax uses the ON clause to specify the matching columns between tables.

2. Step 2: Compare options

   SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id; uses JOIN with ON correctly. SELECT * FROM orders JOIN customers WHERE orders.customer_id = customers.customer_id; uses WHERE incorrectly for JOIN. SELECT * FROM orders, customers ON orders.customer_id = customers.customer_id; misuses ON without JOIN. SELECT * FROM orders JOIN customers USING customer_id; uses USING correctly and is valid syntax.

3. Final Answer:

   SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id; -> Option B

4. Quick Check:

   JOIN with ON clause = A [OK]

Hint: Use ON to specify join condition, not WHERE [OK]

Common Mistakes:

• Using WHERE instead of ON for JOIN condition
• Missing JOIN keyword before ON
• Confusing USING and ON syntax

3. Given these tables:
employees(id, name, dept_id)
departments(dept_id, dept_name)
What will this query return?

SELECT employees.name, departments.dept_name FROM employees JOIN departments ON employees.dept_id = departments.dept_id;

medium

A. An error because dept_id is missing

B. A list of all employees without department names

C. A list of all departments without employee names

D. A list of employee names with their department names

Solution

1. Step 1: Understand the JOIN condition

   The query joins employees and departments where dept_id matches, linking employees to their departments.

2. Step 2: Analyze the SELECT columns

   The query selects employee names and their corresponding department names, so the output shows pairs of employee and department.

3. Final Answer:

   A list of employee names with their department names -> Option D

4. Quick Check:

   JOIN on dept_id returns employee and department pairs = D [OK]

Hint: JOIN returns combined rows matching ON condition [OK]

Common Mistakes:

• Assuming JOIN returns unmatched rows
• Confusing columns in SELECT with JOIN condition
• Expecting error when columns exist

4. What is wrong with this JOIN query?

SELECT * FROM orders JOIN customers ON orders.customer_id customers.customer_id;

medium

A. Missing '=' operator in ON clause

B. Missing JOIN keyword

C. Using WHERE instead of ON

D. No tables specified

Solution

1. Step 1: Check the ON clause syntax

   The ON clause must have a condition with an operator like '=', but here it is missing between the two columns.

2. Step 2: Verify other parts

   JOIN keyword and tables are present, so the main error is the missing '=' operator.

3. Final Answer:

   Missing '=' operator in ON clause -> Option A

4. Quick Check:

   ON clause needs '=' operator = C [OK]

Hint: ON clause must have '=' between columns [OK]

Common Mistakes:

• Omitting '=' in ON condition
• Confusing ON with WHERE syntax
• Leaving out JOIN keyword

5. You have two tables:
students(id, name, class_id)
classes(class_id, class_name)
You want to list all students with their class names, but also include students without a class assigned. Which JOIN should you use?

hard

A. LEFT JOIN

B. INNER JOIN

C. RIGHT JOIN

D. CROSS JOIN

Solution

1. Step 1: Understand JOIN types

   INNER JOIN returns only matching rows. LEFT JOIN returns all rows from the left table and matching rows from the right.

2. Step 2: Apply to problem

   To include all students even if they have no class, use LEFT JOIN from students to classes.

3. Final Answer:

   LEFT JOIN -> Option A

4. Quick Check:

   Include all left table rows = LEFT JOIN = B [OK]

Hint: Use LEFT JOIN to keep all left table rows [OK]

Common Mistakes:

• Using INNER JOIN excludes unmatched rows
• RIGHT JOIN keeps right table rows, not left
• CROSS JOIN creates all combinations, not related rows