Previous

SQL EXCEPT

The EXCEPT operator in SQL is used to return the rows from the first SELECT query that do not exist in the second SELECT query. It is the opposite of the INTERSECT operator, as it helps find records in one result set that are not present in another.

 

Syntax of SQL EXCEPT

SELECT column1, column2, ...
FROM table1
WHERE condition
EXCEPT
SELECT column1, column2, ...
FROM table2
WHERE condition;
  • SELECT statements: The individual queries whose results you want to compare.
  • EXCEPT: The operator used to return rows from the first query that do not exist in the second query.
  • columns: The number of columns and their data types must be the same in both queries.

 

Key Points

  • The EXCEPT operator returns rows from the first query that are not present in the second query.
  • The EXCEPT operator removes duplicates by default. Only unique rows from the first query that do not appear in the second query will be returned.
  • The number of columns and the data types must match between the two SELECT queries.
  • The order of the queries matters—EXCEPT returns the rows from the first query that do not match the second.

 

Example of SQL EXCEPT

Consider the following two tables:

Table 1: Employees (Department A)

employee_id name department
1 John HR
2 Alice HR
3 Bob HR

Table 2: Employees (Department B)

employee_id name department
2 Alice HR
3 Bob HR
4 Sarah IT

Using the EXCEPT operator, we can find employees in Department A that are not in Department B:

SELECT name, department
FROM employees_1
EXCEPT
SELECT name, department
FROM employees_2;

Output:

name department
John HR

Explanation:

  • The result set includes only the row(s) from employees_1 that do not have a matching row in employees_2. In this case, "John" is the only employee in Department A who is not in Department B.

 

SQL EXCEPT with Conditions

You can also apply conditions to both SELECT queries when using EXCEPT. For example, you might want to find employees who are only in Department A and not in Department B, while considering a specific department:

SELECT name, department
FROM employees_1
WHERE department = 'HR'
EXCEPT
SELECT name, department
FROM employees_2
WHERE department = 'HR';

Output:

name department
John HR

 

Summary

The EXCEPT operator in SQL is used to find the rows that are present in the first query but not in the second query. It removes duplicates and ensures that only the unique rows from the first query that do not exist in the second query are returned. The number of columns and their data types must be the same in both queries.

Previous