SQLDailyDrills

INTERSECT and EXCEPT

Combine two query results: keep the rows found in both (INTERSECT) or only in the first (EXCEPT).

Syntax

query1 INTERSECT query2;
query1 EXCEPT query2;
  • Both queries need the same number of columns with compatible types.
  • Both remove duplicates. Add ALL to keep them. Oracle calls EXCEPT MINUS.

Example

SELECT city FROM customers EXCEPT SELECT 'Paris' ORDER BY city;

Every customer city except Paris.

Tables used: customers. Try it in a lesson below.

Common mistakes

  • EXCEPT is not symmetric: A EXCEPT B differs from B EXCEPT A.
  • NULLs count as equal here, unlike in a normal comparison.

Practise it