INTERSECT and EXCEPT

The INTERSECT and EXCEPT operators are used to compare two query results. Both require the same number of columns with compatible data types.

INTERSECT

• Returns only the rows that appear in both queries.
• Works like the logical AND for result sets.

Example – Students with both enrollments and scores:

SELECT student_id FROM enrollments
INTERSECT
SELECT student_id FROM scores;

This returns the student_id values that exist in both tables.

EXCEPT

• Returns rows from the first query that do not appear in the second query.
• Works like the logical subtraction of sets.

Example – Students who are enrolled but have no score recorded:

SELECT id FROM enrollments
EXCEPT
SELECT id FROM scores;

Key Difference

• INTERSECT → Common rows in both queries.
• EXCEPT → Rows in the first query but not in the second.

‍