PostgreSQL: NOT Condition

In this PostgreSQL post explains how to use the PostgreSQL NOT condition with syntax and examples.

Description

The PostgreSQL NOT condition (also called the NOT Operator) is used to negate a condition in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the NOT condition in PostgreSQL is:

NOT condition

Parameters or Arguments

condition

The condition to negate.

Note

• The PostgreSQL NOT condition requires that the opposite of the condition be must be met for the record to be included in the result set.

Example - Combine With IN condition

The PostgreSQL NOT condition can be combined with the IN condition.

For example:

SELECT *
FROM employees
WHERE last_name NOT IN ('Mark', 'Johnson', 'Smith');

This PostgreSQL NOT example would return all rows from the employees table where the last_name is not Mark, Johnson, or Smith. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

Example - Combine With IS NULL condition

The PostgreSQL NOT condition can also be combined with the IS NULL condition.

For example,

SELECT *
FROM contacts
WHERE address IS NOT NULL;

This PostgreSQL NOT example would return all records from the contacts table where the address does not contain a NULL value.

Example - Combine With LIKE condition

The PostgreSQL NOT condition can also be combined with the LIKE condition.

For example:

SELECT product_name, product_description
FROM products
WHERE product_name NOT LIKE 'H%';

By placing the PostgreSQL NOT Operator in front of the LIKE condition, you are able to retrieve all products whose product_name does not start with 'H'.

Example - Combine With BETWEEN condition

The PostgreSQL NOT condition can also be combined with the BETWEEN condition. Here is an example of how you would combine the NOT Operator with the BETWEEN condition.

For example:

SELECT *
FROM employees
WHERE employee_id NOT BETWEEN 525 AND 600;

This PostgreSQL NOT example would return all rows from the employees table where the employee_id was NOT between 525 and 600, inclusive. It would be equivalent to the following SELECT statement:

SELECT *
FROM employees
WHERE employee_id  525
OR employee_id > 600;

Example - Combine With EXISTS condition

The PostgreSQL NOT condition can also be combined with the EXISTS condition.

For example,

SELECT *
FROM products
WHERE NOT EXISTS (SELECT 1
              FROM inventory
              WHERE products.product_id = inventory.product_id);

This PostgreSQL NOT example would return all records from the products table where there are no records in the inventory table for the given product_id.