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.
CREATE Database ShowRoom;
GO
USE ShowRoom;
CREATE TABLE Cars1
(
id INT,
name VARCHAR(50) NOT NULL,
company VARCHAR(50) NOT NULL,
power INT NOT NULL
)
CREATE TABLE Cars2
(
id INT,
name VARCHAR(50) NOT NULL,
company VARCHAR(50) NOT NULL,
power INT NOT NULL
)
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:
USE ShowRoom INSERT INTO Cars1 VALUES (1, 'Corrolla', 'Toyota', 1800), (2, 'City', 'Honda', 1500), (3, 'C200', 'Mercedez', 2000), (4, 'Vitz', 'Toyota', 1300), (5, 'Baleno', 'Suzuki', 1500), (6, 'C500', 'Mercedez', 5000), (7, '800', 'BMW', 8000), (8, 'Mustang', 'Ford', 5000), (9, '208', 'Peugeot', 5400), (10, 'Prius', 'Toyota', 3200) INSERT INTO Cars2 VALUES (6, 'C500', 'Mercedez', 5000), (7, '800', 'BMW', 8000), (8, 'Mustang', 'Ford', 5000), (9, '208', 'Peugeot', 5400), (10, 'Prius', 'Toyota', 3200), (11, 'Atlas', 'Volkswagen', 5000), (12, '110', 'Bugatti', 8000), (13, 'Landcruiser', 'Toyota', 3000), (14, 'Civic', 'Honda', 1800), (15, 'Accord', 'Honda', 2800)
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:
Right_Query EXCEPT Left_Query
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.
USE ShowRoom SELECT id, name, company, power FROM Cars1 Except SELECT id, name, company, power FROM Cars2
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:
USE ShowRoom SELECT id, name, company, power FROM Cars2 Except SELECT id, name, company, power FROM Cars1
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:
USE ShowRoom SELECT id, name, company, power FROM Cars1 Except SELECT id, name, company, power from Cars1 WHERE power < 3000
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.
USE ShowRoom SELECT id, name, company, power FROM Cars1 WHERE id NOT IN (SELECT id from Cars2)
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.
INSERT INTO Cars1 VALUES (1, 'Corrolla', 'Toyota', 1800)
Now the Cars1 table has two duplicate records. You can verify this by executing the following query:
SELECT * FROM Cars1
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.
USE ShowRoom SELECT id, name, company, power FROM Cars1 Except SELECT id, name, company, power FROM Cars2
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:
USE ShowRoom SELECT id, name, company, power FROM Cars1 WHERE id NOT IN (SELECT id from Cars2)
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:
USE ShowRoom SELECT id, name, company, power FROM Cars1 Except SELECT id, name,power FROM Cars2
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:
USE ShowRoom SELECT id, name, company, power FROM Cars1 WHERE id NOT IN (SELECT id, name from Cars2)
In the above script, the sub-query has two columns i.e. id and name. Therefore an error will be thrown:
Further Reading:
- W3 Schools: IN and NOT IN Examples
- TechontheNet: The EXCEPT Operator
- YouTube: The EXCEPT Operator
