EXCEPT

The SQL EXCEPT operator is a set operator that is used to return the records that are present in the first table but not in the second table. This operator is very useful in situations where you need to compare two tables and find the records that are unique to one of them.

Syntax

The syntax for the SQL EXCEPT operator is as follows:

SELECT column1, column2, ...
FROM table1
EXCEPT
SELECT column1, column2, ...
FROM table2;

In this syntax, the SELECT statements represent the two tables that are being compared. The EXCEPT keyword is used to subtract the records in the second table from the records in the first table. The resulting table will contain only those records that are present in the first table but not in the second table.

Example

Here is an example of how to use the SQL EXCEPT operator:

Suppose we have two tables, employees and managers, and we want to find the employees who are not managers.

employees table:

emp_id	emp_name	emp_dept
1	John	Sales
2	Mary	Marketing
3	David	HR
4	Sarah	Sales

managers table:

emp_id	emp_name	emp_dept
2	Mary	Marketing
3	David	HR

create table employees
(
emp_id int, 
emp_name varchar(250), 
emp_dept varchar(250)
);
insert into employees(emp_id, emp_name, emp_dept) values
(1,'John','Sales'), (2,'Mary','Marketing'), 
(3,'David','HR'), (4,'Sarah','Sales');

create table managers
(
emp_id int, 
emp_name varchar(250), 
emp_dept varchar(250)
);
insert into managers(emp_id, emp_name, emp_dept) values
(2,'Mary','Marketing'), (3,'David','HR');

To find the employees who are not managers, we can use the following SQL query:

SELECT emp_id, emp_name, emp_dept
FROM employees
EXCEPT
SELECT emp_id, emp_name, emp_dept
FROM managers;

This will return the following result:

emp_id	emp_name	emp_dept
1	John	Sales
4	Sarah	Sales

As you can see, the resulting table contains only those records that are present in the employees table but not in the managers table. The SQL EXCEPT operator is a powerful tool for comparing tables and finding unique records.