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.

NOT Condition

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

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.

• 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.

• 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 TheDeveloperBlog 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:

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:

• 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.

• 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 TheDeveloperBlog 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:

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.