07 Joins / Checkpoint

Checkpoint — Joins

Q: Where does a filter on the right table go in a LEFT JOIN?

In the ON clause, when it decides which right rows count as a match. In WHERE, a condition such as o.status = 'paid' rejects the NULL-filled rows of unmatched left rows and turns the left join into an inner join.

Q: How do you emulate FULL OUTER JOIN in MySQL?

Write A LEFT JOIN B, then UNION ALL the rows of B LEFT JOIN A where A's key is NULL. The first part keeps every A row; the second adds the B rows that matched nothing. UNION ALL keeps genuine duplicate rows that plain UNION would remove.

Q: Why does NOT IN fail when the subquery returns NULL?

x NOT IN (…, NULL) includes the comparison x <> NULL, which is unknown, so the whole condition can never be true and no rows are returned. NOT EXISTS and LEFT JOIN … IS NULL do not have this problem.

25 min · +25 XP · 3 exercises · 12 questions

Exercises0/3

Quiz · 70% to clear12 q

This checkpoint covers the whole module: inner joins and how rows multiply in one-to-many relationships, left and right joins with counting and the ON-versus-WHERE rule, the full outer join and its MySQL emulation, cross joins for grids, self joins for hierarchies, pairs and day-to-day comparisons, chains of joins through junction tables with the fan-out problem, and semi and anti joins.

How it works. Twelve questions and three queries. You need 70% on the questions and every query accepted to clear the module. You can retake it as often as you like; your best score counts.

Before you start, make sure you can answer these from memory:

What happens to a row with no partner in an inner join, a left join and a full outer join?
Why is COUNT(o.id) right and COUNT(*) wrong after a left join?
Where does a condition on the optional table go, and what happens if it is in WHERE?
How do you write a full outer join in MySQL, and why UNION ALL rather than UNION?
In a self join for pairs, what does a.id < b.id prevent?
Why does an inner join after a left join undo it?
What is fan-out, and how do you avoid double counting across two child tables?
Why is NOT EXISTS safer than NOT IN for an anti join?

The three queries are a department report that keeps empty departments, a self join pairing employees in the same department, and an anti join for courses with no enrolments in one term.

Common questions

Where does a filter on the right table go in a LEFT JOIN?

In the ON clause, when it decides which right rows count as a match. In WHERE, a condition such as o.status = 'paid' rejects the NULL-filled rows of unmatched left rows and turns the left join into an inner join.

How do you emulate FULL OUTER JOIN in MySQL?

Write A LEFT JOIN B, then UNION ALL the rows of B LEFT JOIN A where A's key is NULL. The first part keeps every A row; the second adds the B rows that matched nothing. UNION ALL keeps genuine duplicate rows that plain UNION would remove.

Why does NOT IN fail when the subquery returns NULL?

x NOT IN (…, NULL) includes the comparison x <> NULL, which is unknown, so the whole condition can never be true and no rows are returned. NOT EXISTS and LEFT JOIN … IS NULL do not have this problem.

SQL Joins Quiz: INNER, LEFT, Self and Anti Join Practice Test

Test yourself: 12 questions and three queries on SQL joins: INNER and LEFT JOIN, ON vs WHERE, full outer join in MySQL, self joins, fan-out and anti joins.

Course: SQL study plan
Module: Joins
Kind: Checkpoint — cleared at 70%
Reading time: 25 min
Runtime: MySQL 8 syntax on SQLite 3.49

Checkpoint — Joins is the checkpoint that closes the Joins module: a graded quiz and whole-program exercises, passed at 70%.

Instructions

How it works. Twelve questions and three queries. You need 70% on the questions and every query accepted to clear the module. You can retake it as often as you like; your best score counts.

Before you start, make sure you can answer these from memory:

What happens to a row with no partner in an inner join, a left join and a full outer join?
Why is COUNT(o.id) right and COUNT(*) wrong after a left join?
Where does a condition on the optional table go, and what happens if it is in WHERE?
How do you write a full outer join in MySQL, and why UNION ALL rather than UNION?
In a self join for pairs, what does a.id < b.id prevent?
Why does an inner join after a left join undo it?
What is fan-out, and how do you avoid double counting across two child tables?
Why is NOT EXISTS safer than NOT IN for an anti join?

The three queries are a department report that keeps empty departments, a self join pairing employees in the same department, and an anti join for courses with no enrolments in one term.

Common questions

Where does a filter on the right table go in a LEFT JOIN?

How do you emulate FULL OUTER JOIN in MySQL?

Why does NOT IN fail when the subquery returns NULL?

Exercises

Department report

Report every department with its number of employees and its highest salary. A department with no employees shows 0 and NULL. Employees with no department are ignored.

Tables: departments(id, name) and employees(id, name, dept_id, salary).

departments.id	name
1	Engineering
2	Sales
3	Legal

employees.id	name	dept_id	salary
1	Anu	1	120000
2	Ben	1	95000
3	Chen	2	70000
4	Dev	NULL	50000
5	Esha	2	82000

Output: columns department, headcount, top_salary, ordered by headcount descending, then department.

department | headcount | top_salary
Engineering | 2 | 120000
Sales | 2 | 82000
Legal | 0 | NULL

Colleagues in the same department

List every pair of employees who work in the same department, with the department's name. Each pair appears once: employee_a is the one with the smaller id. Employees with no department are never paired.

Tables: departments(id, name) and employees(id, name, dept_id).

departments.id	name
1	Engineering
2	Sales
3	Legal

employees.id	name	dept_id
1	Anu	1
2	Ben	1
3	Chen	2
4	Dev	NULL
5	Esha	2
6	Farid	1
7	Gia	3

Output: columns department, employee_a, employee_b, ordered by department, employee_a, employee_b.

department | employee_a | employee_b
Engineering | Anu | Ben
Engineering | Anu | Farid
Engineering | Ben | Farid
Sales | Chen | Esha

Courses with no enrolment this term

List the courses that have no enrolment in the term '2024-S2' (they may have enrolments in other terms). Some enrolment rows have a NULL course_id.

Tables: courses(id, title) and enrollments(student_id, course_id, term).

courses.id	title
101	Databases
102	Networks
103	Statistics
104	Compilers

student_id	course_id	term
1	101	2024-S2
2	101	2024-S1
3	102	2024-S1
1	103	2024-S2
2	NULL	2024-S2

Output: columns id, title, ordered by id.

id | title
102 | Networks
104 | Compilers

In this module: Joins

← Semi joins and anti joins — "has a match" and "has none" · Scalar subqueries — a query that returns one value →