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.
Prerequisites
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:
Or for better understanding, we embed NOT IN operator with WHERE clause as shown below:
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:
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.
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.
Conclusion
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.
