The NOT operator is a frequently used operator in Postgres that negates a boolean expression. It's incredibly useful when filtering or excluding certain records from your result set. In Postgres, the NOT operator has various use cases, including in the WHERE clause of a SELECT statement, in the ON clause of a JOIN, and even in a CHECK constraint.

This blog will explain how the NOT operator can be used to manipulate and analyze data in Postgres. So, let’s begin.

NOT Operator/Condition in PostgreSQL

The NOT operator in Postgres allows us to filter or exclude certain data from the query’s result set, providing greater control over the information returned by any query. It can be used/combined with other logical/comparison operators, such as IN, BETWEEN, EXISTS, etc. To use the NOT operator in Postgres, users must follow the below-provided syntax:

NOT condition;

The most frequent use of the NOT operator is in the WHERE clause of a SELECT query.

Sample Table

We have already created a table named “emp_information”, whose data is shown in the following snippet:

SELECT * FROM emp_information;

We will use the above table in all the examples.

Example 1: How to Use NOT Operator With IN Operator in Postgres?

The IN operator checks whether a value belongs to a list of values. However, using IN operator with NOT operator will negate the original result of the IN operator:

SELECT emp_name, emp_salary 
FROM emp_information
WHERE emp_salary NOT IN(3800, 3851, 4500, 5000);

The output shows that the NOT operator retrieves all the records except the specified ones.

Example 2: How to Use NOT Operator With BETWEEN Operator in Postgres?

Using the NOT operator with BETWEEN operator will negate the original result of the BETWEEN operator:

SELECT emp_name, emp_salary 
FROM emp_information
WHERE emp_salary NOT BETWEEN 3800 AND 5000;

The above snippet proves the working of the NOT operator in Postgres.

Example 3: How to Use/Combine NOT Operator With IS NULL Operator in Postgres?

The IS NULL operator retrieves the null values; however, using a NOT operator with IS NULL operator will retrieve all the non-null values:

SELECT emp_name, emp_salary 
FROM emp_information
WHERE emp_salary IS NOT NULL;

The output authenticates the working of the NOT operator.

Example 4: How to Use NOT Operator With LIKE Operator in Postgres?

The below-provided query will find the employees whose name doesn’t start with “J”:

SELECT *
FROM emp_information
WHERE emp_name NOT LIKE 'J%';

The output shows that the NOT operator negates the working of the LIKE operator.

That’s it from this guide!

Conclusion

The NOT operator is a frequently used operator in Postgres that negates a boolean expression. It allows us to filter or exclude certain data from the query’s result set, providing greater control over the information returned by any query. The NOT operator can be combined with other operators, such as IN, BETWEEN, EXISTS, etc. This blog explained the usage of the NOT operator via several practical examples.