PostgreSQL NOT IN Condition

In this section, we are going to understand the working of PostgreSQL NOT IN condition, and example of Not IN condition with Numeric and Character values.

Introduction of PostgreSQL NOT IN condition

The PostgreSQL NOT IN condition is used with WHERE clause to fetch data from a table where defined condition contradicts the PostgreSQL IN condition.

PostgreSQL NOT IN Condition Syntax

In PostgreSQL, the NOT IN condition can be used with the SELECT, INSERT, UPDATE and DELETE commands.

expression NOT IN (value1, value2, .... valueN);  

Example of PostgreSQL NOT IN Condition: with Numeric values

The PostgreSQL NOT IN Operator is used to fetch those rows whose values do not match the list's values.

For this, we are taking the department table from the TheDeveloperBlog database.

The following example displays Not IN Operator's use to identify the department information whose emp_id is neither 1 nor 2:

SELECT emp_id, dept_id, emp_fname, dept_name
FROM department
WHERE emp_id NOT IN (1, 2)
ORDER BY dept_name DESC;

Output

On executing the above command, we will get the below output displaying those records whose emp_id is neither 1 nor 2.

Similarly, we can also use the AND Operator and NOT EQUAL (<>) Operator instead of NOT IN operator as we can see in the following statement:

SELECT emp_id, dept_id, emp_fname, dept_name
FROM department
WHERE emp_id <> 1 AND emp_id <> 2
ORDER BY dept_name DESC;

Output

On implementing the above statement, we will get a similar output compared to the above query output:

Example of PostgreSQL NOT IN Condition: with Character values

For this, we are taking the employee table as above to get that employee information whose emp_fname does not relate to James, Mia employees.

We are using the NOT IN operator in the WHERE clause as we can see the following command:

SELECT emp_id, emp_fname, emp_lname
FROM employee
WHERE emp_fname NOT IN ('James', 'Mia')
ORDER BY emp_id;

Output

On executing the above command, we will get the following result:

In the above example, the PostgreSQL NOT IN condition will return all rows from the employee table where the emp_fname is neither 'James' nor 'Mia'.

Overview

In the PostgreSQL NOT IN Condition section, we have learned the following topics:

• We used the Not Operator with IN Operator to get the records from the particular table.
• We used the NOT IN Condition to fetch the Numeric from the particular table.
• We used the NOT IN Condition to fetch the Character from the particular table.