SQL INTERSECT: Find Values Present in Both Results

The practice task

Find fictional participant IDs appearing in both a writing-workshop list and an analysis-workshop list. Include one repeated writing signup so the distinct-set behavior is visible.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE writing(person_id INTEGER);
CREATE TABLE analysis(person_id INTEGER);
INSERT INTO writing VALUES (1),(2),(2),(3);
INSERT INTO analysis VALUES (2),(3),(4);

SELECT person_id FROM writing
INTERSECT
SELECT person_id FROM analysis
ORDER BY person_id;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

IDs 2 and 3 are present in both lists. ID 1 appears only in writing and ID 4 only in analysis. The repeated writing row for ID 2 does not duplicate that participant in the result.

For this membership task, returning a distinct ID list is intentional. If the question instead asks how many sessions each person attended in both subjects, retain the underlying event records and aggregate at the appropriate grain. A set intersection discards that frequency information.

The selected columns must represent the same kind of fact in corresponding positions. An intersection of person IDs with unrelated session IDs can accidentally match identical numbers without expressing a meaningful relationship. Validate the key domain before comparing it.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Use comparable identity columns from both inputs.
• Keep frequency questions separate from membership questions.
• Add ORDER BY when the output sequence is part of your check.