SQL EXCEPT vs NOT IN - What's the Difference?

Introduction

In this article, we will be discussing the difference between the “except” clause and the “not in” operator of the PostgreSQL database. We hope to clear up the difference between the two so you know when to use which.

Let’s start off by stating that EXCEPT is a clause and NOT IN is an operator. Now it begs the question, what exactly are clauses and operators in PostgreSQL:

Clauses

Clauses in the PostgreSQL are keywords that have definite meaning (similar to their English language counterpart) and purpose. The purpose is self-explanatory by the name of the clause. For example, the WHERE clause is used to query a specific condition. GROUP BY clause is used to group the data on a specific attribute. ORDER BY clause is used to order the data by a specific attribute, etc.

Some clauses in PostgreSQL can only be used with another clause and they usually enhance the functionality of the clause they are being used with. For example, the HAVING clause can only be used with a group by clause and it restricts the results of the group by clause on a condition. The FROM clause is mandatory with the SELECT statement to specify from which table the data needs to be selected.

PostgreSQL EXCEPT Clause

In the real world, when we use the word ‘except’, we are defining an exception from what we stated in the statement preceding the ‘except’ and the statement succeeding describes that exception. For example, “I like all the fruits except oranges and jackfruits”. Now in this statement, “I like all the fruits” is the original statement but “oranges and jackfruits” are not included in the fruits the person likes.

In PostgreSQL, EXCEPT works in pretty much the same way. EXCEPT is used to restrict the results from a SELECT FROM WHERE query on the query after the EXCEPT clause. The general syntax is:

Now, this syntax is identical to the one we use in the English language, so it is very easy to understand. The above Venn diagram makes it even more clear to understand what exactly an EXCEPT clause will do. It would give all the results of query A except those which are in query B too.

Consider the following example:

When the above code is executed, we get the following tables:

Now if we want to see which customers did not purchase anything, we can do that by:

The EXCEPT statement reduces to the result from the upper query to only those that are not in the lower query as evident by the output.

Operators

The operators in PostgreSQL are used to perform simple operations such as arithmetic, logic or comparison operations. So we have Arithmetic operators, logical operators, and comparison operators in PostgreSQL. Currently, we will only focus on Logic Operations.

Usually, logic operations are used as conjunctions between two queries. There are many logic operators in PostgreSQL. These include:

• AND
• OR
• NOT
• ANY
• IN
• EXISTS etc.

PostgreSQL NOT IN operator

The NOT IN operator is a combination of a NOT and IN operator. So we need to know first what is a NOT and an IN operation. The NOT operator reverses the meaning and functionality of any operator that succeeds it. It is also similar to what NOT is used in the English language. For example, “The room was empty” and “The room was not empty” have the opposite meaning. In the same way, NOT IN has entirely different meaning and functionality as compared to the IN operator.

The IN operator is used to match a set of values to some other set of values. The general syntax is:

OR

When combined with NOT, the NOT IN operator checks if some value does not exist in some set of values. i.e. It returns all the results from the preceding values that are not in the succeeding set of values. Or we can say it limits the results of query A to only those results that are only in A and not in B. So we can say that it works the same way as the EXCEPT clause. It is just another way of achieving the same functionality.

For example, in the customers and orders database example, we found the customers who have not ordered anything using except clause. We can do the same using the NOT IN operator as:

This would give the same results as we got with the EXCEPT clause:

Conclusion

There is absolutely no difference in the EXCEPT clause and NOT IN operator. They both serve the same purpose and they are simply two different ways of achieving the same functionality. The queries written using EXCEPT can be rewritten using NOT IN operator. It is just a matter of personal preference.