Checkpoint — Set operations and CTEs

Q: Why can NOT IN return no rows at all?

If the subquery or list contains a NULL, x NOT IN (...) includes the comparison x <> NULL, which is unknown, so the condition is never true and every row is filtered out. NOT EXISTS and EXCEPT give the expected rows.

Q: When should I use UNION instead of UNION ALL?

Use UNION only when the question asks for distinct rows from either source, such as every city you deal with. Otherwise use UNION ALL: it keeps rows that merely look alike, such as two equal sales, and skips the cost of the duplicate check.

Q: How do you fill missing dates in a MySQL report?

Generate every date with a recursive CTE, DATE_ADD(day, INTERVAL 1 DAY) until the last date, then LEFT JOIN the data to it and use COALESCE(SUM(amount), 0), so a day with no rows shows 0 instead of disappearing.

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

Exercises0/3

Quiz · 70% to clear12 q

This checkpoint covers the whole module: stacking results with UNION and UNION ALL, the column rules and sorting the combined result, rows in both and rows in one with INTERSECT and EXCEPT, their EXISTS/NOT EXISTS and LEFT JOIN … IS NULL equivalents, the NOT IN null trap, naming steps with common table expressions, and walking hierarchies and generating series with WITH RECURSIVE.

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 three rules must the SELECTs of a union satisfy, and where do the result's column names come from?
When does UNION give a wrong answer that UNION ALL would not?
What is A EXCEPT B if A is {1, 2, 3} and B is {2, NULL} — and what does NOT IN give for the same data?
How do you write "rows in A but not in B" on a MySQL version without EXCEPT?
In a chain of CTEs, which CTEs can each one read?
What are the anchor and the recursive member, and when does a recursive CTE stop?
Why does a path column in a MySQL recursive CTE need a CAST in the anchor?

The three queries are a date-filled daily revenue report built on a recursive date series, an activity count across two tables combined with UNION ALL inside a CTE, and the size of every manager's whole team — direct and indirect reports — from a recursive CTE grouped in the outer query.

Common questions

Why can NOT IN return no rows at all?

If the subquery or list contains a NULL, x NOT IN (...) includes the comparison x <> NULL, which is unknown, so the condition is never true and every row is filtered out. NOT EXISTS and EXCEPT give the expected rows.

When should I use UNION instead of UNION ALL?

Use UNION only when the question asks for distinct rows from either source, such as every city you deal with. Otherwise use UNION ALL: it keeps rows that merely look alike, such as two equal sales, and skips the cost of the duplicate check.

How do you fill missing dates in a MySQL report?

Generate every date with a recursive CTE, DATE_ADD(day, INTERVAL 1 DAY) until the last date, then LEFT JOIN the data to it and use COALESCE(SUM(amount), 0), so a day with no rows shows 0 instead of disappearing.

SQL CTE and UNION Quiz: Set Operations Practice Test

Test yourself: 12 questions and three queries on UNION and UNION ALL, INTERSECT, EXCEPT, NOT EXISTS, the NOT IN null trap, chained CTEs and recursive CTEs.

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

Checkpoint — Set operations and CTEs is the checkpoint that closes the Set operations and CTEs 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 three rules must the SELECTs of a union satisfy, and where do the result's column names come from?
When does UNION give a wrong answer that UNION ALL would not?
What is A EXCEPT B if A is {1, 2, 3} and B is {2, NULL} — and what does NOT IN give for the same data?
How do you write "rows in A but not in B" on a MySQL version without EXCEPT?
In a chain of CTEs, which CTEs can each one read?
What are the anchor and the recursive member, and when does a recursive CTE stop?
Why does a path column in a MySQL recursive CTE need a CAST in the anchor?

Common questions

Why can NOT IN return no rows at all?

When should I use UNION instead of UNION ALL?

How do you fill missing dates in a MySQL report?

Exercises

Daily revenue with every day shown

The sales table has a row per sale. Report revenue for every day from the earliest sale date to the latest, including days with no sales (revenue 0).

sales: id	sale_date	amount
1	2024-03-01	100
2	2024-03-01	50
3	2024-03-03	70
4	2024-03-05	20

Return day and revenue, ordered by day.

day | revenue
2024-03-01 | 150
2024-03-02 | 0
2024-03-03 | 70
2024-03-04 | 0
2024-03-05 | 20

Generate the days with a recursive CTE and DATE_ADD(day, INTERVAL 1 DAY); the table always has at least one sale.

Activity across posts and comments

Count each user's activity: the number of their posts plus the number of their comments. Users with no activity show 0.

users: id	name
1	Asha
2	Ben
3	Chen
4	Dev

posts: id	user_id
1	1
2	1
3	2

comments: id	user_id
1	2
2	2
3	1
4	3

Return name and activity, ordered by activity descending, then name.

name | activity
Asha | 3
Ben | 3
Chen | 1
Dev | 0

Every manager's whole team

For each employee who manages anyone, count everyone below them — direct reports, their reports, and so on.

employees: id	name	manager_id
1	Meera	NULL
2	Arjun	1
3	Divya	1
4	Kabir	2
5	Leela	4
6	Anil	3

Return name and team_size, ordered by team_size descending, then name. People who manage no one do not appear.

name | team_size
Meera | 5
Arjun | 2
Divya | 1
Kabir | 1

In this module: Set operations and CTEs

← Recursive CTEs — hierarchies, paths and number series · OVER and PARTITION BY — aggregates that keep every row →