Joins & Subqueries

Difficulty: Intermediate

Question

Explain different types of SQL joins. When would you use a subquery vs a join?

Answer

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

Types: - INNER JOIN: only matching rows from both tables - LEFT JOIN: all rows from left table + matching from right (NULL if no match) - RIGHT JOIN: all rows from right table + matching from left - FULL OUTER JOIN: all rows from both tables - CROSS JOIN: cartesian product (every combination) - SELF JOIN: table joined with itself

Subquery vs Join: - Joins are generally faster and more readable for combining data - Subqueries are better for filtering based on aggregates or existence checks

Code examples

Join Types Illustrated

-- Setup:
-- employees: id, name, department_id
-- departments: id, name

-- INNER JOIN: only employees WITH a department
SELECT e.name, d.name AS dept
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- LEFT JOIN: ALL employees, even without department
SELECT e.name, COALESCE(d.name, 'Unassigned') AS dept
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- Find departments with NO employees
SELECT d.name
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
WHERE e.id IS NULL;

-- Self join: find employees and their managers
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

LEFT JOIN is the most commonly used in real apps because you usually want all rows from the primary table even if related data is missing.

Subquery vs JOIN

-- Find employees earning above average salary

-- Approach 1: Subquery (cleaner for this case)
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Approach 2: JOIN with derived table
SELECT e.name, e.salary
FROM employees e
CROSS JOIN (SELECT AVG(salary) AS avg_sal FROM employees) a
WHERE e.salary > a.avg_sal;

-- Correlated subquery: salary above dept average
SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e.department_id  -- references outer query
);

Simple subqueries run once. Correlated subqueries run once per row in the outer query - they can be slow on large tables.

EXISTS vs IN

-- Find customers who have placed orders

-- Using IN (loads all order customer_ids into memory)
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);

-- Using EXISTS (stops at first match - usually faster)
SELECT name FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
);

-- Rule of thumb:
-- IN: better when subquery returns few rows
-- EXISTS: better when outer table is small, subquery table is large
-- EXISTS can short-circuit (stops checking after first match)

EXISTS is generally more efficient for large datasets because it stops scanning as soon as it finds a match. IN must collect all values first.

Key points

• Always visualize joins with a Venn diagram when explaining
• LEFT JOIN + WHERE right.id IS NULL = find missing relationships
• Self joins are common for hierarchical data (employees-managers, categories-subcategories)

Concepts covered

INNER JOIN, LEFT JOIN, Self Join, Subquery, Correlated Subquery