15 SQL JOIN Practice Problems with Solutions

Solution

SELECT o.order_id, o.order_date, c.full_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

Explanation

INNER JOIN connects the two tables on the shared customer_id column. Only orders that have a matching customer record are returned. The table aliases o and c are shorthand to avoid writing the full table name each time.

Solution

SELECT p.product_name, c.category_name
FROM products p
INNER JOIN categories c ON p.category_id = c.category_id;

Explanation

The products table stores a category_id but not the category name. INNER JOIN pulls in the matching category name from the categories table. Products without a matching category_id would be excluded — but with clean data, all products should match.

Solution

SELECT e.full_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

Explanation

The employees table stores a department_id as a reference. INNER JOIN uses that ID to look up the matching department name in the departments table. Employees without a matching department_id would not appear in the results.

Solution

SELECT b.booking_id, b.check_in_date, h.hotel_name
FROM bookings b
INNER JOIN hotels h ON b.hotel_id = h.hotel_id;

Explanation

INNER JOIN links each booking to its hotel using the shared hotel_id. Only bookings with a valid matching hotel record are included in the results.

Solution

SELECT e.enrollment_id, e.course_name, s.full_name
FROM enrollments e
INNER JOIN students s ON e.student_id = s.student_id;

Explanation

INNER JOIN connects enrollments to students via the shared student_id. Only enrollments that have a matching student record are returned — any enrollment with an invalid student_id would be excluded.

Solution

SELECT c.full_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Explanation

LEFT JOIN keeps all rows from the left table (customers) regardless of whether they have a match in the right table (orders). Customers with no orders will still appear, but their order_id will show as NULL.

Solution

SELECT e.full_name, p.project_name
FROM employees e
LEFT JOIN project_assignments p ON e.employee_id = p.employee_id;

Explanation

LEFT JOIN ensures every employee appears in the results. Employees assigned to a project will show the project name. Employees with no assignment will show NULL in the project_name column.

Solution

SELECT p.product_name, r.review_text
FROM products p
LEFT JOIN reviews r ON p.product_id = r.product_id;

Explanation

LEFT JOIN keeps all products in the results. Products with reviews will show the review text. Products that have never been reviewed will appear with NULL in the review_text column.

Solution

SELECT s.full_name, e.course_name
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id;

Explanation

LEFT JOIN returns every student regardless of enrollment status. Students enrolled in a course show the course name. Students with no enrollment show NULL for course_name.

Solution

SELECT h.hotel_name, b.booking_id
FROM hotels h
LEFT JOIN bookings b ON h.hotel_id = b.hotel_id;

Explanation

LEFT JOIN keeps all hotels in the output. Hotels with bookings will show the corresponding booking IDs. Hotels with no bookings will still appear but with NULL in the booking_id column.

Solution

SELECT o.order_id, c.full_name, ci.city_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN cities ci ON c.city_id = ci.city_id;

Explanation

The first INNER JOIN connects orders to customers. The second INNER JOIN then connects customers to cities using the city_id. Each JOIN adds another table to the chain, letting you pull columns from all three tables in one query.

Solution

SELECT p.product_name, c.category_name, s.supplier_name
FROM products p
INNER JOIN categories c ON p.category_id = c.category_id
INNER JOIN suppliers s ON p.supplier_id = s.supplier_id;

Explanation

Two INNER JOINs extend the products table with data from both categories and suppliers. Each JOIN uses a different foreign key column from the products table to connect to the right lookup table.

Solution

SELECT e.full_name, d.department_name, m.full_name AS manager_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN employees m ON e.manager_id = m.employee_id;

Explanation

The first INNER JOIN pulls in the department name. The second INNER JOIN joins the employees table to itself — using a different alias (m for manager) — to look up the manager's name from the same table. This is called a self-join.

Solution

SELECT s.full_name, c.course_name, c.instructor_name
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
LEFT JOIN courses c ON e.course_id = c.course_id;

Explanation

LEFT JOIN is used for both joins so that students with no enrollment still appear in the results. If a student has no enrollment, both course_name and instructor_name will be NULL. Chaining LEFT JOINs preserves all rows from the leftmost table throughout.

Solution

SELECT b.booking_id, g.full_name, h.hotel_name, ci.city_name
FROM bookings b
INNER JOIN guests g ON b.guest_id = g.guest_id
INNER JOIN hotels h ON b.hotel_id = h.hotel_id
INNER JOIN cities ci ON h.city_id = ci.city_id;

Explanation

Three INNER JOINs chain four tables together. The first links bookings to guests, the second links bookings to hotels, and the third links hotels to cities. Each JOIN adds one more table to the query, letting you pull together data spread across multiple tables in a single result set.