In Postgres, the NOT IN operator contradicts the working of the IN operator. The NOT IN operator filters the query results and returns all the values of a result set except the specified values. In simple terms, the NOT IN operator excludes the specified values and returns the rest of the values.

Through examples, this article will explain how the NOT IN operator works in PostgreSQL. So, let’s begin.

How Does the NOT IN Operator Work in PostgreSQL?

The NOT IN operator is used in conjunction with the Postgres WHERE clause and can be used on both string and numeric data. It returns all the values of a result set except the specified values. Here is the basic syntax of the Postgres NOT IN operator:

WHERE col_name NOT IN (val_1, val_2, val_3, ..., val_N);

Let’s implement it practically to learn how the NOT IN operator works in PostgreSQL.

Example #1: How to Use NOT IN Operator With the Numeric Data?

A table has already been created named “article_details”. Let’s execute the SELECT query to fetch the table’s records:

SELECT * FROM article_details;

The article_details table has 12 rows. Suppose we have to retrieve all the articles except two whose ids are 4 and 8. To achieve this purpose, we will use the NOT IN operator as follows:

SELECT *
FROM article_details
WHERE article_id NOT IN(4,8);

The NOT IN operator returned all the values that were not specified in the NOT IN operator.

Example #2: How Does the NOT IN Operator Work With the String Data?

Let’s consider the same article_details table and this time, use the NOT IN operator on the string data as follows:

SELECT *
FROM article_details
WHERE article_title NOT IN('PostgreSQL CREATE TABLE','PostgreSQL IN Operator');

As a result of NOT IN operator, the specified article titles were excluded, and the rest of the details were returned.

That was all the necessary information regarding Postgres NOT IN operator.

Conclusion

In PostgreSQL, the NOT IN operator filters the query results and returns all the values of a result set except the specified values. The NOT IN operator is used in conjunction with the Postgres WHERE clause and can be used on both string and numeric data. This write-up goes through a couple of examples to demonstrate the working of the NOT IN operator.