PostgreSQL – EXCEPT
The EXCEPT keyword is used in a query to retrieve distinct rows from the result of the first query that are not present in the result of the second query. In other words, it returns the set difference between the results of two queries.
The basic syntax of the EXCEPT operator is as follows:
SELECT columns FROM table1 EXCEPT SELECT columns FROM table2;where columns are the columns you want to select from the tables.
and table1 and table2 and the tables you want to query.
Here’s a simple example to illustrate how the EXCEPT operator works. Consider two tables, employees and former_employees, both with a name column:
Table employees:
| name |
|---|
| Alice |
| Bob |
| Carol |
| David |
Table former_employees:
| name |
|---|
| Carol |
| David |
You can use the EXCEPT operator to find the employees who are currently employed but not in the list of former employees:
sqlCopy code
SELECT name FROM employees EXCEPT SELECT name FROM former_employees;
The result would be:
| name |
|---|
| Alice |
| Bob |
In this example, the EXCEPT operator retrieves the distinct names from the employees table that are not present in the former_employees table.
The columns selected in both queries must have the same data types, and the order of the columns must match for the EXCEPT operator to work correctly. Also, the EXCEPT operator automatically removes duplicate rows from its result set.