Summary -

In this topic, we described about the EXCEPT operator with detailed example.

EXCEPT operator is used to combine two or more SELECT statements of different tables. By using EXCEPT operator we can get DISTINCT rows from first SELECT statement by omitting matched rows in all other SELECT statements and duplicate rows in first SELECT statement in result set. While using EXCEPT operator the number of columns and DATA TYPE of columns should be same in all SELECT statements. EXCEPT operator takes first SELECT statement column names as the result set column names by default.

Syntax -

SELECT Expression1 [AS Alias_name],Expressio2,…
FROM table1 [WHERE condition]
EXCEPT
SELECT Expression1, Expressio2,… FROM table2 [WHERE condition];

Example –

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000
010 Employee6 Analyst Employee3 2019-07-11 27000.00 1000
011 Employee8 Salesman Employee5 2019-09-09 10000.00 1000

employee_info -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
011 Employee8 Salesman Employee5 2019-09-09 10000.00 1000
013 Employee5 Engineer Employee7 2019-07-16 22000.00 1000

Scenario – Getting First table DISTINCT rows with multiple columns Which are not in second table by using EXCEPT operator.

Requirement - Getting employee_details table DISTINCT rows of multiple columns which are not in employee_info table by using EXCEPT operator. The query was as follows – 

SELECT emp_name, designation, manager_id FROM employee_details
EXCEPT
SELECT emp_name, designation, manager_id FROM employee_info;

By executing above query we can get rows of multiple columns from employee_details which are not in employee_info. The output was as follows -

emp_name designation manager_id
Employee2 Director
Employee4 Manager Employee2
Employee8 Salesman Employee4
Employee9 Salesman Employee4

Scenario – Getting First table DISTINCT rows of single column Which are not in second table by using EXCEPT operator with alias name.

Requirement – Getting employee_details table DISTINCT rows of single column which are not in employee_info table by using EXCEPT operator. The query was as follows – 

SELECT  emp_name AS NAME FROM employee_details
EXCEPT
SELECT emp_name FROM employee_info;

By executing above query we can get rows of single column emp_name from employee_details which are not in employee_info. The output was as follows -

NAME
Employee2
Employee4
Employee9

Scenario – Getting First table DISTINCT rows with multiple columns Which are not in second table by using EXCEPT operator with WHERE condition.

Requirement – Getting employee_details table DISTINCT rows of multiple columns which are not in employee_info table by using EXCEPT operator with WHERE condition . The query was as follows – 

SELECT emp_name,designation,manager_id,date_of_hire FROM employee_details
WHERE dept_id = 1000
EXCEPT
SELECT emp_name, designation, manager_id, date_of_hire FROM employee_info
WHERE dept_id = 1000;

By executing above query we can get rows of multiple columns whose dept_id is 1000 from employee_details which are not in employee_info. The output was as follows -

emp_name designation manager_id date_of_hire
Employee6 Analyst Employee3 2019-07-11