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:

Parameter	Description
Condition	The 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’:

  SELECT customer_id, first_name, last_name  FROM Customer  WHERE last_name NOT IN (‘Smith’, ‘Brown’)  ORDER BY customer_id;  

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:

  SELECT customer_id, first_name, last_name  FROM Customer  WHERE last_name NOT LIKE ‘Smi%’  ORDER BY customer_id;  

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 tutoraspire Database, where we are trying to get those car details whose car_price is not between the range of 100000 and 399999:

  SELECT car_id, car_name, car_price, car_model  FROM car  WHERE car_price NOT BETWEEN 100000 AND 399999  ORDER BY car_id DESC;  

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:

  SELECT car_id, car_name, car_price, car_model  FROM car  WHERE car_price< 100000  OR car_price> 399999  ORDER BY car_id DESC;  

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.

  SELECT customer_id, first_name, last_name, order_id  FROM Customer  WHERE order_id IS NOT NULL  ORDER BY customer_id;  

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 tutoraspire database.

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

  SELECT emp_id, emp_fname, emp_lname, location  FROM employee  WHERE NOT EXISTS (  SELECT *  FROM department  WHERE employee.emp_id= department.emp_id);  

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.

Next TopicPostgreSQL LIKE Condition

PostgreSQL Not Condition

PostgreSQL Not Condition

Introduction of PostgreSQL NOT condition

PostgreSQL NOT Condition Syntax

Examples of PostgreSQL NOT Condition with Different Conditions

Overview

PostgreSQL Having Clause

PostgreSQL Self Join

You may also like