INTERACTIVE LECTURES
CASE & UNION: one report, many sources
Guided class activity · apply the ideas from the full SQL lessons.
Preparing for Lab 9 · After practice, try the team challenge.
Opening your saved work…
Sign in to save across devices → · Download your guest work before switching accounts.
Compare membership in two sets using IDs
Step 7 of 8 · Database: Boreal Board Game Café
READ & UNDERSTAND
INTERSECT answers which IDs appear in both sets. EXCEPT answers which IDs appear in the first set but not the second. Swapping EXCEPT operands reverses the question; it is not a formatting change.
Compare stable member IDs, then look up names. Different people can have the same name. Exclude NULL member_id from café orders before treating the result as a set of members, because walk-ins are not identified members.
Use WHERE id IN (set query) to retrieve display columns from members. The set operators remove repeated values, which is appropriate for a list of people. This does not establish how many orders they placed or whether they attended an event.
Worked example
SELECT id, first_name FROM members WHERE id IN (SELECT member_id FROM sessions INTERSECT SELECT member_id FROM cafe_orders WHERE member_id IS NOT NULL) ORDER BY id;
The example asks for overlap, regardless of how many actions each person recorded.
The practice asks for the order-only set. Use IDs for the operation, then add names.
Your question
Return id and first_name for members who have at least one café order but no session. Build the ID set with EXCEPT and order by id.
Explain your evidence
How would the opposite EXCEPT order change the business question? What can this report not tell us about why someone has no session?
Try a query to unlock the worked-answer discussion.
Query results
Predict the result, then run your query.Predict the result, then run your query.
Read-only activity · One statement per run · First 200 rows displayed · Checks compare the complete bounded result, not just this preview.
Explore this query freely in the playground ↗Check the save status above. Account-saved work is available in My Progress. Explanations are for discussion, not automatically graded. No prerequisite locks.