Multiple table JOINs in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of INNER JOIN on three tables

Given these tables:

Students: (id, name)
Enrollments: (student_id, course_id)
Courses: (id, title)

What is the output of this query?

SELECT Students.name, Courses.title FROM Students
INNER JOIN Enrollments ON Students.id = Enrollments.student_id
INNER JOIN Courses ON Enrollments.course_id = Courses.id
WHERE Courses.title = 'Math';

MySQL

CREATE TABLE Students (id INT, name VARCHAR(20));
CREATE TABLE Enrollments (student_id INT, course_id INT);
CREATE TABLE Courses (id INT, title VARCHAR(20));

INSERT INTO Students VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO Courses VALUES (10, 'Math'), (20, 'History');
INSERT INTO Enrollments VALUES (1, 10), (2, 20);

A [{"name": "Alice", "title": "Math"}]

B [{"name": "Bob", "title": "Math"}]

C [{"name": "Alice", "title": "History"}]

D []

💡 Hint

Think about which student is enrolled in the 'Math' course.

The query joins Students to Enrollments, then to Courses, filtering for 'Math'. Only Alice is enrolled in Math.

❓ query_result

intermediate

LEFT JOIN with missing matches

Given these tables:

Authors: (id, name)
Books: (id, author_id, title)

What is the output of this query?

SELECT Authors.name, Books.title FROM Authors
LEFT JOIN Books ON Authors.id = Books.author_id
WHERE Books.title IS NULL;

MySQL

CREATE TABLE Authors (id INT, name VARCHAR(20));
CREATE TABLE Books (id INT, author_id INT, title VARCHAR(20));

INSERT INTO Authors VALUES (1, 'John'), (2, 'Mary');
INSERT INTO Books VALUES (10, 1, 'Book A');

A [{"name": "John", "title": "Book A"}, {"name": "Mary", "title": null}]

B [{"name": "Mary", "title": null}]

C [{"name": "John", "title": null}]

D []

💡 Hint

LEFT JOIN keeps all authors, but WHERE filters for those without books.

Mary has no books, so Books.title is NULL for her. The query returns Mary only.

📝 Syntax

advanced

Identify the syntax error in multiple JOINs

Which option contains a syntax error in this multiple JOIN query?

SELECT e.name, d.department_name, m.name AS manager_name
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN employees m ON d.manager_id = m.id;

A SELECT e.name, d.department_name, m.name AS manager_name FROM employees e JOIN departments d ON e.department_id = d.id JOIN employees m ON d.manager_id = m.id;

B SELECT e.name, d.department_name, m.name AS manager_name FROM employees e INNER JOIN departments d ON e.department_id = d.id INNER JOIN employees m ON d.manager_id = m.id;

C SELECT e.name, d.department_name, m.name AS manager_name FROM employees e JOIN departments d ON e.department_id = d.id JOIN employees m ON d.manager_id = m.id WHERE e.salary > 50000;

D SELECT e.name, d.department_name, m.name AS manager_name FROM employees e JOIN departments d e.department_id = d.id JOIN employees m ON d.manager_id = m.id;

💡 Hint

Look carefully at the JOIN clauses and ON keywords.

Option D is missing the ON keyword after 'departments d', causing a syntax error.

❓ optimization

advanced

Optimizing multiple JOINs for performance

You have three large tables: Customers, Orders, and Products. You want to find all customers who ordered a product named 'Gadget'. Which query is more efficient?

A SELECT c.name FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id LEFT JOIN Products p ON o.product_id = p.id WHERE p.name = 'Gadget';

B SELECT c.name FROM Customers c, Orders o, Products p WHERE c.id = o.customer_id AND o.product_id = p.id AND p.name = 'Gadget';

C SELECT c.name FROM Customers c JOIN Orders o ON c.id = o.customer_id WHERE o.product_id IN (SELECT id FROM Products WHERE name = 'Gadget');

D SELECT DISTINCT c.name FROM Customers c JOIN Orders o ON c.id = o.customer_id JOIN Products p ON o.product_id = p.id WHERE p.name = 'Gadget';

💡 Hint

Consider how filtering early can reduce rows processed in joins.

Option C filters Products first, reducing rows before joining, improving performance.

🧠 Conceptual

expert

Understanding JOIN order and results

Consider these tables:

Employees: (id, name, manager_id)
Departments: (id, name, head_id)

You want to list employees with their department name and their manager's name. Which JOIN order is correct to get all employees, even those without a department or manager?

A FROM Employees e LEFT JOIN Employees m ON e.manager_id = m.id LEFT JOIN Departments d ON e.department_id = d.id

B FROM Employees e LEFT JOIN Departments d ON e.department_id = d.id LEFT JOIN Employees m ON e.manager_id = m.id

C FROM Employees e JOIN Departments d ON e.department_id = d.id JOIN Employees m ON e.manager_id = m.id

D FROM Departments d LEFT JOIN Employees e ON e.department_id = d.id LEFT JOIN Employees m ON e.manager_id = m.id

💡 Hint

Think about which table is the main one and how LEFT JOIN preserves rows.

Option A starts from Employees and LEFT JOINs managers and departments, preserving all employees even if no manager or department exists.

Practice

1. What is the main purpose of using JOIN with multiple tables in MySQL?

easy

A. To delete rows from multiple tables at once

B. To combine rows from two or more tables based on a related column

C. To create a new table from existing tables

D. To update multiple tables simultaneously

Solution

1. Step 1: Understand the role of JOIN

   JOIN is used to combine rows from two or more tables based on a related column, allowing us to see related data together.

2. Step 2: Compare with other options

   Deleting, creating, or updating tables are different SQL operations and not the main purpose of JOIN.

3. Final Answer:

   To combine rows from two or more tables based on a related column -> Option B

4. Quick Check:

   JOIN combines tables = To combine rows [OK]

Hint: JOIN connects tables by matching columns [OK]

Common Mistakes:

• Confusing JOIN with DELETE or UPDATE operations
• Thinking JOIN creates new tables permanently
• Assuming JOIN works without specifying related columns

2. Which of the following is the correct syntax to join two tables employees and departments on the column department_id?

easy

A. SELECT * FROM employees JOIN departments ON employees.department_id = departments.department_id;

B. SELECT * FROM employees JOIN departments WHERE employees.department_id = departments.department_id;

C. SELECT * FROM employees, departments ON employees.department_id = departments.department_id;

D. SELECT * FROM employees JOIN departments USING employees.department_id;

Solution

1. Step 1: Check JOIN syntax with ON clause

   The correct syntax uses JOIN followed by ON to specify the matching columns: JOIN departments ON employees.department_id = departments.department_id.

2. Step 2: Identify errors in other options

   SELECT * FROM employees JOIN departments WHERE employees.department_id = departments.department_id; uses WHERE instead of ON, which is incorrect for JOIN conditions. SELECT * FROM employees, departments ON employees.department_id = departments.department_id; uses ON without JOIN keyword properly. SELECT * FROM employees JOIN departments USING employees.department_id; uses USING incorrectly with full column name.

3. Final Answer:

   SELECT * FROM employees JOIN departments ON employees.department_id = departments.department_id; -> Option A

4. Quick Check:

   JOIN with ON = SELECT * FROM employees JOIN departments ON employees.department_id = departments.department_id; [OK]

Hint: JOIN needs ON, not WHERE, to link tables [OK]

Common Mistakes:

• Using WHERE instead of ON for JOIN condition
• Using USING with full table.column syntax
• Omitting JOIN keyword or ON clause

3. Given the tables:
students(id, name, class_id)
classes(id, class_name)
What is the output of this query?

SELECT students.name, classes.class_name FROM students JOIN classes ON students.class_id = classes.id WHERE classes.class_name = 'Math';

medium

A. Empty result because of missing WHERE clause

B. List of all students with their class names

C. List of class names only

D. List of student names who are in the Math class

Solution

1. Step 1: Understand the JOIN and WHERE clause

   The JOIN connects students with their classes by matching class_id and id. The WHERE clause filters only classes named 'Math'.

2. Step 2: Determine the output columns and rows

   The SELECT returns student names and class names only for students in 'Math' class, so the output is a list of those students with 'Math' as class_name.

3. Final Answer:

   List of student names who are in the Math class -> Option D

4. Quick Check:

   JOIN + WHERE filters = students in Math [OK]

Hint: WHERE filters after JOIN to limit rows [OK]

Common Mistakes:

• Thinking it returns all students regardless of class
• Confusing output columns with only class names
• Assuming WHERE is missing and causes empty result

4. Identify the error in this query joining orders and customers:

SELECT orders.id, customers.name FROM orders JOIN customers WHERE orders.customer_id = customers.id;

medium

A. Missing ON keyword before join condition

B. Using WHERE instead of HAVING

C. Incorrect table names

D. Missing SELECT keyword

Solution

1. Step 1: Check JOIN syntax

   The JOIN clause must be followed by ON to specify the join condition. Here, WHERE is incorrectly used instead of ON.

2. Step 2: Confirm other parts are correct

   Table names and SELECT keyword are correct. HAVING is not used for join conditions.

3. Final Answer:

   Missing ON keyword before join condition -> Option A

4. Quick Check:

   JOIN needs ON, not WHERE [OK]

Hint: JOIN requires ON before condition, not WHERE [OK]

Common Mistakes:

• Replacing ON with WHERE in JOIN
• Confusing WHERE with HAVING clause
• Ignoring syntax errors in JOIN statements

5. You have three tables:
products(id, name, category_id)
categories(id, category_name)
sales(product_id, quantity)
Which query correctly lists each product's name, its category name, and total quantity sold?

hard

A. SELECT p.name, c.category_name, SUM(s.quantity) FROM products p LEFT JOIN categories c ON p.category_id = c.id LEFT JOIN sales s ON p.id = s.product_id GROUP BY p.name;

B. SELECT p.name, c.category_name, s.quantity FROM products p JOIN categories c ON p.category_id = c.id JOIN sales s ON p.id = s.product_id;

C. SELECT p.name, c.category_name, SUM(s.quantity) FROM products p JOIN categories c ON p.category_id = c.id JOIN sales s ON p.id = s.product_id GROUP BY p.name, c.category_name;

D. SELECT p.name, c.category_name, SUM(s.quantity) FROM products p, categories c, sales s WHERE p.category_id = c.id AND p.id = s.product_id;

Solution

1. Step 1: Understand the requirement

   We need product name, category name, and total quantity sold (sum of sales.quantity) grouped by product and category.

2. Step 2: Analyze each option

   SELECT p.name, c.category_name, SUM(s.quantity) FROM products p JOIN categories c ON p.category_id = c.id JOIN sales s ON p.id = s.product_id GROUP BY p.name, c.category_name; uses JOINs with ON clauses correctly and groups by product and category names, summing quantity. SELECT p.name, c.category_name, s.quantity FROM products p JOIN categories c ON p.category_id = c.id JOIN sales s ON p.id = s.product_id; misses aggregation. SELECT p.name, c.category_name, SUM(s.quantity) FROM products p, categories c, sales s WHERE p.category_id = c.id AND p.id = s.product_id; uses implicit joins but misses GROUP BY. SELECT p.name, c.category_name, SUM(s.quantity) FROM products p LEFT JOIN categories c ON p.category_id = c.id LEFT JOIN sales s ON p.id = s.product_id GROUP BY p.name; uses LEFT JOIN but groups only by product name, missing category grouping.

3. Final Answer:

   SELECT p.name, c.category_name, SUM(s.quantity) FROM products p JOIN categories c ON p.category_id = c.id JOIN sales s ON p.id = s.product_id GROUP BY p.name, c.category_name; -> Option C

4. Quick Check:

   JOIN + GROUP BY + SUM = SELECT p.name, c.category_name, SUM(s.quantity) FROM products p JOIN categories c ON p.category_id = c.id JOIN sales s ON p.id = s.product_id GROUP BY p.name, c.category_name; [OK]

Hint: Use JOIN with GROUP BY and SUM for totals [OK]

Common Mistakes:

• Missing GROUP BY when using SUM
• Using WHERE instead of JOIN ON
• Grouping by only one column when multiple needed