Lesson 2 of 4

Joins, subqueries and set operations

3 min read

Join types

Join	Returns
`INNER JOIN`	only rows that match in both tables
`LEFT JOIN`	all rows from the left table, matches from the right or NULL
`RIGHT JOIN`	the mirror of LEFT
`FULL OUTER JOIN`	all rows from both, NULLs where no match
`CROSS JOIN`	every combination (rows × rows)
self join	a table joined to itself (employee → manager)

sql

-- Jobs with their company names
SELECT j.title, c.name AS company
FROM jobs j
INNER JOIN companies c ON c.id = j.company_id;

-- Every student and how many times they applied (including zero)
SELECT s.name, COUNT(a.id) AS applications
FROM students s
LEFT JOIN applications a ON a.student_id = s.id
GROUP BY s.id, s.name;

Note COUNT(a.id), not COUNT(*): with a LEFT JOIN, a student with no applications still has one row (with NULLs), and COUNT(*) would count it as 1.

Finding rows with no match (anti-join)

sql

-- Students who never applied
SELECT s.* FROM students s
LEFT JOIN applications a ON a.student_id = s.id
WHERE a.id IS NULL;

-- Same with NOT EXISTS (often clearer, and safe with NULLs)
SELECT s.* FROM students s
WHERE NOT EXISTS (SELECT 1 FROM applications a WHERE a.student_id = s.id);

Avoid NOT IN (subquery) when the subquery can return NULL: then it returns no rows at all.

A classic trap: filtering the right table in WHERE

sql

-- Turns the LEFT JOIN into an INNER JOIN by accident (NULL status fails the test)
SELECT s.name, a.status FROM students s
LEFT JOIN applications a ON a.student_id = s.id
WHERE a.status = 'hired';

-- Keep unmatched students: put the condition in ON
LEFT JOIN applications a ON a.student_id = s.id AND a.status = 'hired'

Subqueries

sql

-- Jobs paying above the average
SELECT title, salary FROM jobs WHERE salary > (SELECT AVG(salary) FROM jobs);

-- Correlated: jobs paying above their own area's average
SELECT j.title, j.area, j.salary FROM jobs j
WHERE j.salary > (SELECT AVG(x.salary) FROM jobs x WHERE x.area = j.area);

A correlated subquery references the outer row, so conceptually it runs once per row.

CTEs: readable multi-step queries

sql

WITH job_stats AS (
  SELECT job_id, COUNT(*) AS applicants, AVG(score) AS avg_score
  FROM applications GROUP BY job_id
)
SELECT j.title, s.applicants, ROUND(s.avg_score, 1) AS avg_score
FROM jobs j JOIN job_stats s ON s.job_id = j.id
WHERE s.applicants >= 20
ORDER BY s.avg_score DESC;

Recursive CTEs walk hierarchies (org charts, category trees).

Set operations

UNION (combine, remove duplicates), UNION ALL (combine, keep duplicates; faster), INTERSECT, EXCEPT. Columns must match in number and type.

Practice

Companies that have never posted a job.
For each job, the number of applicants and number hired.
Students who applied to jobs in at least 3 different areas.

← Previous Next →