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.