EXCEPT
Description
The EXCEPT
operator in SQL is used to return all rows from the first SELECT
statement that are not present in the second SELECT
statement. It effectively subtracts the result set of the second query from the result set of the first query. It is often used to find differences between two sets of data.
Oracle supports both EXCEPT and its functionally equivalent counterpart, MINUS.
Why EXCEPT is Preferred:
Wider adoption across different SQL database systems.
Alignment with the ANSI SQL standard for portability.
Choosing EXCEPT or EXCEPT ALL:
Use
EXCEPT
(default) for distinct results, excluding duplicates.Use
EXCEPT ALL
(optional) if we specifically need all rows from the difference, including duplicates present in the first result set.
Characteristics of the EXCEPT
Operator
EXCEPT
OperatorEliminates Duplicates: The
EXCEPT
operator automatically removes duplicate rows from the result set.Column Match: The number of columns and the data types of the columns in the
SELECT
statements must match.Order of Queries Matters: The order of the
SELECT
statements is important because it determines which rows are subtracted from which.
Syntax
Example
Consider two tables, employees
and managers
, with the following data:
employees
table:
emp_id | name | dept_id |
---|---|---|
1 | Alice | 101 |
2 | Bob | 102 |
3 | Charlie | 103 |
4 | Dave | 104 |
managers
table:
mgr_id | name | dept_id |
---|---|---|
2 | Bob | 102 |
3 | Charlie | 103 |
5 | Eve | 105 |
To find employees who are not managers, we can use the EXCEPT
operator as follows:
Result:
name | dept_id |
---|---|
Alice | 101 |
Dave | 104 |
In this example, the EXCEPT
clause returns the rows where the name
and dept_id
are present in the employees
table but not in the managers
table.
Last updated