How to use NOT IN operator in Postgres

The Postgres relational database management system has been widely regarded as an enterprise-level relational database management system. The querying support of Postgres is based on SQL, but it extends the SQL processing mechanism for manipulating complex and huge data. Apart from this querying support, Postgres allows you to customize the functionality, like you can define your custom data type, customize the functions and many more.

Among many operators in Postgres, the NOT IN operator of Postgres allows it to filter the content by targeting and excluding specific values from a table. This article explains the usage of the NOT IN operator in Postgres databases. Moreover, you would also get a working mechanism of the NOT IN operator in the context of Postgres.


An actively running postgres service is required with the following instances:

– A Postgres database that must contain at least one table and several columns to exercise this operator.

In this writeup, we will be using several tables of mydb database to apply the NOT IN operator.

How does NOT IN Operator work

The NOT IN operator works with WHERE condition of a SQL statement. The syntax to use NOT IN operator is provided below:

> NOT IN (<value1, value2,value3,...>)

Or for better understanding, we embed NOT IN operator with WHERE clause as shown below:

> WHERE <column-name> NOT IN (<value1, value2,...>)

Conventionally, the Postgres built-in keywords are used in capital letters, but the small alphabets also perform the same action without any errors.

How to use NOT IN operator in Postgres

The previous section explains the working pattern of the NOT IN operator. This part of writing elaborates the functionality of the NOT IN operator with some examples.

Example 1: Apply NOT IN on string data type

Here in this example, the employee table of mydb database is used and the content inside it is provided below:

# SELECT * FROM employee;

A picture containing calendar Description automatically generated

Let’s say we want to extract the content from the employee table where the name column of the employee table does not match ‘jack‘, and ‘jones‘. For this condition, the NOT IN operator is brought into practice in the following way as described in the command written below. The output of the command provided below excludes the jack and jones data from the employee table and represents the rest of the data.

# SELECT * FROM employee WHERE name NOT IN ('jack', 'jones');

Background pattern Description automatically generated with medium confidence

Example 2: Apply NOT IN on a numeric datatype

The NOT IN operator is interoperable and can be applied to any kind of data type in Postgres. In this example, we have used the NOT IN operator to filter the content from the primary key of the employee table. The primary key is set on the ‘id‘ column of the table. The command provided below will get the data from the employee table that does not match the ‘1‘, ‘3‘, and ‘5‘ in the id column.

# SELECT * FROM employee WHERE id NOT IN ('1', '3', '5');

Graphical user interface Description automatically generated


The NOT IN operator of Postgres extracts the content from the table with WHERE condition. This detailed guide provides working and usage of the NOT IN operator in Postgres context. From this guide, you would be able to apply the NOT IN operator to multiple columns that have different data types. Moreover, the general syntax and function of this operator are also provided. This guide is quite helpful when you are querying long tables and want to exclude a few values from that.

About the author

Adnan Shabbir