SQL EXCEPT

What is EXCEPT in SQL?

The EXCEPT clause in SQL helps users combine two SELECT statements and returns distinct rows from the first SELECT statement that are not available in the second SELECT statement.

• Its rules are similar to the UNION operator and can be compared to subtract operator in relational algebra.
• All versions of SQL server support the EXCEPT clause. MySQL does not support the EXCEPT operator.
• Conditions for EXCEPT clause:
  • There must be the same number of expressions in both SELECT statements.
  • The data types of corresponding columns should be the same or compatible.

The EXCEPT query will return the records in the shaded area. These are the records that are present in Dataset1 and not in Dataset2.

Syntax

The SQL EXCEPT command works with the following syntax:

column1, column2 - The columns to be retrieved. table_name - The tables from which records will be retrieved. WHERE - [optional] These conditions need to be met for the records to be selected.

Examples

Example 1

Let us consider that we have two relations, Customers and New_Stores. We want to return all those customers who are not part of the new_stores.

Customer Table

New_Stores Table

Code

Output

Example 2

Let us consider that we have two relations, Books1 and Books2. We want to delete all those entries from Books1 that are present in Books2.

Books1 Table

Books2 Table

Code

Output

EXCEPT with BETWEEN Operator

In SQL, the EXCEPT operator can be used to return the distinct rows from the left table that do not exist in the right table. We can also use the BETWEEN operator to specify a range of values in a query. When we combine EXCEPT with BETWEEN operator, it returns the rows that exist in the left table but not in the right table and satisfy the specified range of values.

Here's an example that shows how to use EXCEPT with BETWEEN operator:

In the above example, column_name(s) refers to the columns that we want to select from the tables table1 and table2. value1 and value2 represent the lower and upper limits of the range of values for the specified column.

This query will return all the distinct rows from table1 that do not exist in table2 and satisfy the specified range of values.

EXCEPT with IN Operator

The EXCEPT operator in SQL can also be used with the IN operator to get the difference between two sets of values. The IN operator is used to compare a value with a set of values.

Here's an example to illustrate how to use EXCEPT with the IN operator:

This query will select all the rows from table1 that are not present in table2 and have a value not in the given set of values.

Note that the EXCEPT operator returns only the distinct values. If you want to include duplicates, you can use the UNION ALL operator.

EXCEPT with LIKE Operator

The EXCEPT operator can also be used in conjunction with the LIKE operator to exclude certain patterns from the query result. This can be useful when you want to select all rows that do not match a particular pattern.

For example, let's say you have a table of customer names and you want to select all customers whose names do not start with the letter "A". You could use the following query:

This will return all customer names except those that start with the letter "A". In the above example, the LIKE operator is used in the subquery to select all customer names that start with the letter "A". The EXCEPT operator is then used to remove these names from the main query result.

EXCEPT with ORDER BY Clause

When using EXCEPT, you can also sort the result set using the ORDER BY clause. The ORDER BY clause sorts the result set in ascending or descending order based on one or more columns. The syntax for using EXCEPT with ORDER BY clause is as follows:

Here, ORDER BY clause is used to sort the result set in ascending or descending order. ASC is used for ascending order and DESC is used for descending order.

EXCEPT Statements in a Single Table

The EXCEPT operator can be used to find the records in one query that are not present in another query. When using EXCEPT in a single table, we can use it to compare the results of two different queries on the same table.

For example, suppose we have a table named employees with columns id, name, and salary. We can use the EXCEPT operator to find the employees whose salaries are not in the range of 30000 to 40000. The following is an example query:

This query selects all the columns from the employees table where the salaries are not in the specified range. By using EXCEPT in a single table, we can quickly compare the results of two different queries on the same table.

How is EXCEPT Different from NOT IN Clause?

The functionality of both clauses appears to be the same since they specify entries that should not be included in the final result set of the query. However, there lies a point of difference between them.

• EXCEPT clause is used to remove all duplicates automatically in the final result, whereas NOT IN retains the duplicate entries.
• The EXCEPT clause can perform comparison in single or multiple columns. Whereas the NOT IN clause can perform comparison in a single column only.

Learn More

• Advanced concepts of SQL
• SQL Commands: DDL, DML, DCL, TCL, DQL
• How to Avoid SQL Errors?
• Online SQL Formatter