Understanding the SQL EXCEPT statement with examples

 The SQL EXCEPT statement is one of the most commonly used statements to filter records when two SELECT statements are being used to select records.

The SQL EXCEPT statement returns those records from the left SELECT query, that are not present in the results returned by the SELECT query on the right side of the EXCEPT statement.

A SQL EXCEPT statement works very similarly to the way that the minus operator does in mathematics.

The EXCEPT statement was introduced in Microsoft SQL Server 2005.

In this article, you will learn how to use SQL EXCEPT with the help of some simple examples.

The conditions to execute SQL EXCEPT statement

There are a couple of conditions that have to be met before you can use the EXCEPT statement in SQL Server:

1. The number of columns and orders in the tables that are being used to execute the SELECT statements should be the same
2. The data types of the corresponding columns of both tables involved in the corresponding SELECT queries should be either the same or compatible

Creating a dummy dataset

Let’s create a dummy dataset that we will use to execute the example SQL EXCEPT statements unless you are using a fully backed up SQL database.

Run the following script:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

CREATE Database BookStore; GO USE BookStore;   CREATE TABLE Books1 (     id INT,     name VARCHAR(50) NOT NULL,     category VARCHAR(50) NOT NULL,     price INT NOT NULL )   CREATE TABLE Books2 (     id INT,     name VARCHAR(50) NOT NULL,     category VARCHAR(50) NOT NULL,     price INT NOT NULL )

In the script above, we create a dummy database called BookStore. The database contains two tables: Books1 and Books2.

Both tables contain 4 columns: id, name, category and price.

Now let’s insert some dummy records into both tables.

Run the following script:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

USE BookStore   INSERT INTO Books1   VALUES (1, 'Book1', 'Cat1', 1800), (2, 'Book2', 'Cat2', 1500), (3, 'Book3', 'Cat3', 2000), (4, 'Book4', 'Cat4', 1300), (5, 'Book5', 'Cat5', 1500), (6, 'Book6', 'Cat6', 5000), (7, 'Book7', 'Cat7', 8000), (8, 'Book8', 'Cat8', 5000), (9, 'Book9', 'Cat9', 5400), (10, 'Book10', 'Cat10', 3200)   INSERT INTO Books2   VALUES   (6, 'Book6', 'Cat6', 5000), (7, 'Book7', 'Cat7', 8000), (8, 'Book8', 'Cat8', 5000), (9, 'Book9', 'Cat9', 5400), (10, 'Book10', 'Cat10', 3200), (11, 'Book11', 'Cat11', 5000), (12, 'Book12', 'Cat12', 8000), (13, 'Book13', 'Cat13', 5000), (14, 'Book14', 'Cat14', 5400), (15, 'Book15', 'Cat15', 3200)

The above script inserts 10 records into the Books1 table and 10 records into the Books2 table.

It is important to mention that the last 5 records in the Books1 table and the first 5 records in the Books2 table are the same.

Let’s now see how we can use SQL EXCEPT statement on this data.

Filtering records with the SQL EXCEPT statement

Let’s look at the syntax for using a SQL EXCEPT statement:

1	Right SELECT Query EXCEPT Left SELECT Query

Yes, it is that simple to execute an EXCEPT statement.

Next, we will use the SQL EXCEPT statement to select records from the Books1 table that are not present in the Books2 table. You can see that the records from ids 6 to 10 are the same in both tables.

So, if the Book1 table is on the left of the EXCEPT operator and Books2 table is on the right, the records with ids 1 to 5 will be selected from the table Books1.

To give it a try, run the following script:

1 2 3 4

USE BookStore SELECT id, name, category, price FROM Books1 Except SELECT id, name, category, price FROM Books2

Here is the output of the above script:

You can see that only records with ids 1 to 5 have been selected from the Books1 table since the records with ids 6 to 10 also exist in the Books2 table.

Similarly, if the Books2 table is on the left side of the SQL EXCEPT statement and the Books1 table is on the right, you will see records from the Books2 table not present in the Books1 table.

Execute the following script to see this in action:

1 2 3 4

USE BookStore SELECT id, name, category, price FROM Books2 Except SELECT id, name, category, price FROM Books1

Here is the output:

You can see that only records with ids 11 to 15 have been selected since records with ids 6-10 from the Books2 table, also exist in the Books1 table.

Using EXCEPT statements in a single table

In addition to using a SQL EXCEPT statement for filtering records from two tables, an EXCEPT statement can also be used to filter records from a single table. For example, the following EXCEPT statement will return all the records from the Books1 table where the price is less than or equal to 5000:

1 2 3 4

USE BookStore SELECT id, name, category, price FROM Books1 Except SELECT id, name, category, price FROM Books1 WHERE price > 5000

In the script above, we have two SELECT statements operating on a single table i.e. Books1.

The SELECT statement on the right-hand side of the EXCEPT statement selects all the records where the price is greater than 5000. The SELECT statement on the left side of the EXCEPT statement returns all the records from the Books1 table.

Next, the EXCEPT statement filters the records selected by the SELECT statement on the right, from the records returned by the SELECT statement on the left. Hence, we are only left with the records from the Books table, where the price is not greater than 5000.

Here is the output of the above query:

EXCEPT vs NOT NULL

Now that you know how an EXCEPT statement works, it is important to understand the difference between SQL EXCEPT statement and NOT IN statement. There are two major differences:

1. The EXCEPT statement only returns the distinct records, whereas a NOT IN statement returns all the records that are not filtered by the NOT IN statement
2. In the EXCEPT statement, the comparison between two SELECT statements is based on all the columns in both the tables. While a NOT IN statement compares values from a single column

Here is an example of how a NOT IN statement can be used to filter all records from the Books1 table, that also exist in the Books2 table:

1 2 3

USE BookStore SELECT id, name, category, price FROM Books1 WHERE id NOT IN (SELECT id from Books2)

Output:

You can see that here the comparison between the first and second columns is only based on the id column.

Conclusion

The SQL EXCEPT statement is used to filter records based on the intersection of records returned via two SELECT statements. The records that are common between the two tables are filtered from the table on the left side of the SQL EXCEPT statement and the remaining records are returned.

In this article, we looked at how to use an EXCEPT statement to filter records from two tables as well as from a single table. The article also covered the difference between the EXCEPT and NOT IN statements.