EXCEPT

SQL Server

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.

Oracle

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.