Understanding Difference between EXCEPT and NOT IN Operator

Understanding Difference between EXCEPT and NOT IN Operator
4 (80%) 1 vote

Both EXCEPT and NOT IN operators are used to filter records from a table based on a specific criterion. In this article, we will look at these operators in detail and will investigate the differences between them.

Before we go further, let’s create some dummy data that we will use in this example:

Preparing Dummy Data

Execute the following script to create a dummy database and tables that we will be using in this article.

In the script above, we create a ShowRoom database with two tables, Cars1 and Cars2.

Let’s add some dummy records to the Cars1 and Cars2 tables.

Execute the following script:

We now have a useful data set.

Let’s look at how the EXCEPT and NOT IN operators differ from each other using this data.

EXCEPT Operator

The EXCEPT operator filters all the records from the left query result, that are also present in the result of the right query and returns the remaining results from the left query.

The syntax of EXCEPT operator is as follows:

There are two conditions that need to be met before the execution of the EXCEPT operator. They are:

  • The order and number of the columns returned by both the queries must be the same.
  • The data types of the columns must match or be compatible.

This may sound tricky. However, the following example will make it clear.

In the Cars1 and Cars2 tables, the records with id 6 to id 10 are common. If we use the EXCEPT operator with records from the Cars1 table on the left and Cars2 table on right, those records from the Cars1 table will be returned that are not in Cars2.
Let’s see the EXCEPT operator in action.

The result of the above query will look like this:

The query on the left side retrieves all the records from the Cars1 table while that on the right side retrieves all the records from the Cars2 table. In the Cars1 table the records with id 6 to 10 are also present in the Cars2 table, therefore, the EXCEPT operator only returned the first five rows from the Cars1 table.

Similarly, if you want to return all the records from the Cars2 table that are not present in the Cars1 table, you can simply move the Cars2 table to the left of the EXCEPT operator as shown in the following query:

Using EXCEPT Operator on a Single Table

In the previous examples, we applied the EXCEPT operator on two tables. However, the EXCEPT operator can be used to filter records from a single table as well. For example, if you want to filter all the records from the Cars1 table where the power value is greater than 3,000, you can use the EXCEPT operator as follows:

In the above script, the query on the left side of the EXCEPT operator retrieves all the records from the Cars1 table. The query on the right side of the EXCEPT operator retrieves only those records from the Cars1 table where the power value is less than 3,000. The EXCEPT operator filters records from the query on the left that exist in the results from the query on the right. Therefore, all the records in the Cars1 table where power is less than 3,000 will be filtered out and the rest of the records will be returned as shown in the output below:

NOT IN Operator

The NOT IN Operator is also used to filter rows. Let’s find all of the records from the Cars1 table that are also present in the Cars2 table and return the rest of the records using NOT IN operator.

The result of the above query looks like this:

This is similar to the results obtained using the EXCEPT operator.

Difference between EXCEPT and NOT IN Operators

We can see that the results obtained using EXCEPT and NOT IN operator are similar. However, there are two major differences between EXCEPT and NOT IN Operators:

1- NOT IN Returns Duplicate Records while EXCEPT Returns Distinct Records only

The EXCEPT operator removes duplicate rows from the results and returns only DISTINCT records. On the other hand, the NOT IN operator will return duplicate records.
Let’s take a look at this with the help of an example. Execute the following query to insert a duplicate record in the Cars1 table.

Now the Cars1 table has two duplicate records. You can verify this by executing the following query:

The Cars1 table will look like that:

You can see that rows 1 and 11 are duplicates of each other.

Let’s first use EXCEPT operator to filter all the records from the Cars1 table that are present in the Cars2 table.

The result looks like this:

You can see that although rows 1 and 11 were duplicates in the Cars1 table and they were not present in the Cars2 table, the EXCEPT operator didn’t return both of these rows. It has only returned distinct rows.

Now let’s use the NOT IN operator for the same task:

The output of the above query looks like this:

You can see that NOT IN returns duplicate rows i.e. row 1 and row 6.

2- EXCEPT compares columns while NOT IN compares a single value

EXCEPT operator compares values in one or more than one columns. This means that the number of columns must be the same. On the other hand, the NOT IN operator compares values in a single column.

For instance, you cannot have an unequal number of columns for the queries on the left and right of an EXCEPT operator as shown in the following example:

The above query returns the following error:

For the NOT IN operator, you cannot have more than one column in the sub-query. This means that the following query will return an error:

In the above script, the sub-query has two columns i.e. id and name. Therefore an error will be thrown:

Further Reading:

Ben Richardson

Ben Richardson

Ben Richardson runs Acuity Training. Acuity is an IT training business offering classroom courses in London and Guildford. It is a leading provider of SQL training the UK and offers a full range of SQL training from introductory training to advanced administration courses.
Ben Richardson