What does EXCEPT do?

What does EXCEPT do?

• A. It returns rows that appear in both queries.
• B. It returns rows that appear only in the first query.
• C. It returns rows that are in the second query but not the first.
• D. It returns the cross product of rows from both queries.

Answer: (B)

EXCEPT computes the difference between two query results: it returns the rows that come from the first query but do not appear in the second query. Think of it as subtracting the second result set from the first one. In most SQL systems, this yields distinct rows, removing any duplicates. If you ever need to keep duplicates, some dialects offer EXCEPT ALL. For example, if the first query returns (1, 'apple'), (2, 'banana'), and the second returns (2, 'banana'), the result of EXCEPT is (1, 'apple'). This shows why the correct interpretation is that EXCEPT gives rows that are in the first set but not in the second. This is different from an INTERSECT, which would give rows common to both; the other options would correspond to considering rows in the second but not the first, or to combining every row from both queries (a cross product), neither of which matches EXCEPT.

EXCEPT computes the difference between two query results: it returns the rows that come from the first query but do not appear in the second query. Think of it as subtracting the second result set from the first one. In most SQL systems, this yields distinct rows, removing any duplicates. If you ever need to keep duplicates, some dialects offer EXCEPT ALL.

For example, if the first query returns (1, 'apple'), (2, 'banana'), and the second returns (2, 'banana'), the result of EXCEPT is (1, 'apple'). This shows why the correct interpretation is that EXCEPT gives rows that are in the first set but not in the second.

This is different from an INTERSECT, which would give rows common to both; the other options would correspond to considering rows in the second but not the first, or to combining every row from both queries (a cross product), neither of which matches EXCEPT.