SQL Reference - EXCEPT

EXCEPT

EXCEPT in SQL operates on two SQL queries and returns the difference between the first query and the second. It eliminates rows from the result set of the first query that match exactly with rows in the result set of the second query. It ensures that the data in the first query returned is unique compared to the second, with no duplicates.

Example

SELECT column_name FROM table1
EXCEPT
SELECT column_name FROM table2;

Output

| column_name |
|-------------|
| Value 1     |
| Value 3     |

Explanation

The EXCEPT operator is used to return all the distinct rows from the first SELECT statement that are not returned by the second SELECT statement. In the given example, it is retrieving distinct rows from table1 that are not present in table2 based on the column_name specified.

Example

SELECT department_id
FROM   employees
EXCEPT
SELECT department_id
FROM   departments;

Output

| DEPARTMENT_ID |
|---------------|
|     270       |
|     340       |

Explanation

The EXCEPT operator returns records from the first SELECT statement (employees table) that are not matched by the second SELECT statement (departments table). In the example given, we are obtaining the department_ids from the employees table that do not exist in the departments table.