Joining more than two tables in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of joining three tables with INNER JOIN

Given the tables Employees, Departments, and Locations, what is the output of the following SQL query?

SELECT e.name, d.department_name, l.city
FROM Employees e
INNER JOIN Departments d ON e.department_id = d.id
INNER JOIN Locations l ON d.location_id = l.id
ORDER BY e.name;

SQL

CREATE TABLE Employees (id INT, name VARCHAR(50), department_id INT);
CREATE TABLE Departments (id INT, department_name VARCHAR(50), location_id INT);
CREATE TABLE Locations (id INT, city VARCHAR(50));

INSERT INTO Employees VALUES (1, 'Alice', 10), (2, 'Bob', 20), (3, 'Charlie', 10);
INSERT INTO Departments VALUES (10, 'Sales', 100), (20, 'Engineering', 200);
INSERT INTO Locations VALUES (100, 'New York'), (200, 'San Francisco');

A [{'name': 'Alice', 'department_name': 'Sales', 'city': 'New York'}, {'name': 'Bob', 'department_name': 'Engineering', 'city': 'San Francisco'}, {'name': 'Charlie', 'department_name': 'Sales', 'city': 'New York'}]

B [{'name': 'Alice', 'department_name': 'Sales', 'city': 'San Francisco'}, {'name': 'Bob', 'department_name': 'Engineering', 'city': 'New York'}, {'name': 'Charlie', 'department_name': 'Sales', 'city': 'San Francisco'}]

C [{'name': 'Alice', 'department_name': 'Sales', 'city': 'New York'}, {'name': 'Charlie', 'department_name': 'Sales', 'city': 'New York'}]

D [{'name': 'Bob', 'department_name': 'Engineering', 'city': 'San Francisco'}]

💡 Hint

Think about how INNER JOIN works: it only includes rows where the join condition matches in all tables.

The query joins Employees to Departments on department_id, then Departments to Locations on location_id. All employees have matching departments and locations, so all three employees appear with their correct department and city.

📝 Syntax

intermediate

Identify the syntax error in joining three tables

Which option contains a syntax error when joining three tables Orders, Customers, and Products to get order details?

SQL

SELECT o.order_id, c.customer_name, p.product_name
FROM Orders o
JOIN Customers c ON o.customer_id = c.id
JOIN Products p ON o.product_id = p.id;

A SELECT o.order_id, c.customer_name, p.product_name FROM Orders o INNER JOIN Customers c ON o.customer_id = c.id JOIN Products p ON o.product_id = p.id;

B SELECT o.order_id, c.customer_name, p.product_name FROM Orders o JOIN Customers c ON o.customer_id = c.id JOIN Products p o.product_id = p.id;

C SELECT o.order_id, c.customer_name, p.product_name FROM Orders o JOIN Customers c ON o.customer_id = c.id INNER JOIN Products p ON o.product_id = p.id;

D SELECT o.order_id, c.customer_name, p.product_name FROM Orders o JOIN Customers c ON o.customer_id = c.id JOIN Products p ON o.product_id = p.id;

💡 Hint

Check the JOIN syntax carefully, especially the ON clause.

Option B is missing the ON keyword before the join condition for Products, causing a syntax error.

❓ optimization

advanced

Optimizing a query joining four tables

You have four tables: Sales, Customers, Products, and Stores. Which query is the most efficient to get sales details including customer name, product name, and store location?

A SELECT s.sale_id, c.name, p.name, st.location FROM Sales s LEFT JOIN Customers c ON s.customer_id = c.id LEFT JOIN Products p ON s.product_id = p.id LEFT JOIN Stores st ON s.store_id = st.id;

B SELECT s.sale_id, c.name, p.name, st.location FROM Sales s, Customers c, Products p, Stores st WHERE s.customer_id = c.id AND s.product_id = p.id AND s.store_id = st.id;

C SELECT s.sale_id, c.name, p.name, st.location FROM Sales s JOIN Customers c ON s.customer_id = c.id JOIN Products p ON s.product_id = p.id JOIN Stores st ON s.store_id = st.id;

D SELECT s.sale_id, c.name, p.name, st.location FROM Sales s CROSS JOIN Customers c CROSS JOIN Products p CROSS JOIN Stores st WHERE s.customer_id = c.id AND s.product_id = p.id AND s.store_id = st.id;

💡 Hint

Consider the clarity and performance of JOIN syntax versus older comma joins and CROSS JOINs.

Option C uses explicit INNER JOINs which are clearer and often optimized better by SQL engines. Option C uses old-style joins which can be less efficient. Option C uses LEFT JOINs which may return extra rows if no matches exist. Option C uses CROSS JOINs which produce large intermediate results.

🧠 Conceptual

advanced

Understanding join types with three tables

You join three tables: A, B, and C. If you want to keep all rows from A even if there are no matching rows in B or C, which join type should you use?

A Use INNER JOIN between A and B, then INNER JOIN between B and C.

B Use FULL OUTER JOIN between A and B, then FULL OUTER JOIN between B and C.

C Use RIGHT JOIN from A to B, then RIGHT JOIN from B to C.

D Use LEFT JOIN from A to B, then LEFT JOIN from B to C.

💡 Hint

Think about which join keeps all rows from the left table regardless of matches.

LEFT JOIN keeps all rows from the left table (A), even if there are no matches in the right tables (B and C). INNER JOIN would exclude unmatched rows.

🔧 Debug

expert

Debugging incorrect results from joining three tables

You run this query joining three tables but get fewer rows than expected:

SELECT e.name, d.department_name, p.project_name
FROM Employees e
JOIN Departments d ON e.department_id = d.id
JOIN Projects p ON e.id = p.employee_id
WHERE d.location = 'New York';

What is the most likely reason for missing rows?

A Some employees in New York departments have no projects, so INNER JOIN excludes them.

B The WHERE clause filters out employees not in New York, causing no rows to show.

C The JOIN condition between Employees and Departments is incorrect, causing no matches.

D The Projects table has no employee_id column, causing a syntax error.

💡 Hint

Consider how INNER JOIN behaves when there is no matching row in the joined table.

INNER JOIN only returns rows where matching rows exist in all joined tables. Employees without projects are excluded, causing fewer rows.