EXCEPT operation
The EXCEPT/MINUS operation finds the difference between the two queries and the result comprises the rows that belong only to the first query.
EXCEPT and MINUS are synonyms. You can use either word in SQL statements.
To contrast INTERSECT and EXCEPT:
- An INTERSECT B contains rows from table A that are duplicated in table B.
- An EXCEPT B contains rows from table A that do not exist in table B.
EXCEPT
In an EXCEPT [DISTINCT],
or a MINUS [DISTINCT] operation, if a tuple t appears m (> 0) times
in the first input table, and same tuple t appears n (>= 0) times
in the second input table, then that tuple t appears only once in
the output table if (m > 0 && n == 0).
{0,1,2,2,2,2,3,N,N} EXCEPT {1,2,2,3,5,5,N,N,N}
Ë{0}EXCEPT ALL
In an EXCEPT
ALL, or in a MINUS ALL operation, if a tuple t appears m (> 0) times
in the first input table, and same tuple t appears n (>= 0) times
in the second input table, then that tuple t appears (m - n) times
in the output table if (m - n) > 0.
{0,1,2,2,2,2,3,N,N} EXCEPT ALL{1,2,2,3,5,5,N,N,N}
Ë{0,2,2}