PostgreSQL Not Condition

In this section, we are going to understand the working of PostgreSQL NOT Condition and see the examples of NOT Condition with IN, LIKE, BETWEEN, IS NULL, and EXISTS Conditions.

Introduction of PostgreSQL NOT condition

The PostgreSQL NOT condition is used with the WHERE clause to negate a condition in a command. The NOT Condition is also knowns as NOT Operator.

PostgreSQL NOT Condition Syntax

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

In the above syntax, we have used the below parameter:

ParameterDescription
ConditionThe Condition parameter is used to define the conditions which we want to negate.

Examples of PostgreSQL NOT Condition with Different Conditions

  • Example of using PostgreSQL NOT Operator with IN Condition

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

For this, we are taking the Customer table from the Organization database.

The following example displays Not Operator with IN condition to identify the customer information whose last_name is not 'Smith' or 'Brown':

Output

On executing the above command, we will get the below output displaying those records whose last_name is not Smith or Brown.

PostgreSQL Between Condition
  • Example of using PostgreSQL NOT Operator with LIKE condition

In PostgreSQL, we can also combine the NOT Operator with Like Condition.

In the below example, we will learn the use of PostgreSQL NOT Operator with PostgreSQL LIKE condition.

The following command is used to identify those customers information whose last_name does not start with string Smi:

Output

After implementing the above statement, we will get the following output, which displays those customers whose last_name is not like Smi.

PostgreSQL Between Condition
  • Example of using PostgreSQL NOT Operator with Between condition

In PostgreSQL, we can also use the NOT operator with BETWEEN Condition within the WHERE clause to fetch data from a table where the defined condition contradicts the PostgreSQL Between Condition.

In the below example, we are using the Car table from the Javatpoint Database, where we are trying to get those car details whose car_price is not between the range of 100000 and 399999:

Output

After successfully executing the above command, we will get the following output, which displays the car information whose price is not between the range of 100000 and 399999:

PostgreSQL Between Condition

In the below example, we are using the greater than (>) and less than (<) operators with OR operator instead of using NOT BETWEEN/ AND operators. The below command is equivalent to the above SELECT query:

Output

After implementing the above command, we will get the following result:

PostgreSQL Between Condition
  • Example of using PostgreSQL NOT Operator with IS NULL Condition

In PostgreSQL, the IS NULL Condition can be combined with the NOT condition.

We are using the Customer table from the Organization database. In the following example, we will identify those customer records whose order_id is not null.

Output

On executing the above command, we will get the following result, which displays those customer details whose order_id is not null.

PostgreSQL Between Condition
  • Example of using PostgreSQL NOT Operator with Exists condition

In PostgreSQL, the EXISTS operator is used to test for the existence of any data in a subquery, and the Exists condition can also be combined with the NOT operator within the WHERE clause.

Let us see one sample example to understand the working of NOT condition with Exists condition.

In the following command, we will take the employee and department table from the Javatpoint database.

To identify all the employee records that do not exist in the department table with the help of the emp_id column.

Output

After executing the above command, we will get the following output, which returns all the employee table records. There are no records in the department table for the given emp_id:

PostgreSQL Between Condition

Overview

In the PostgreSQL NOT 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 Condition with Like Condition to get the records from the particular table.
  • We used the NOT operator with BETWEEN condition to get the records from the table.
  • We used the NOT operator with IS NULL condition to get the records from the particular table.
  • We used the NOT condition with the EXISTS condition.





Latest Courses