PostgreSQL: Using the NOT Condition in Queries

Negate conditions in PostgreSQL queries using the NOT operator. This guide shows how to use NOT with various other conditions and WHERE clauses.

Using the `NOT` Condition in PostgreSQL Queries

Introduction

In PostgreSQL, the `NOT` condition (or operator) is used with the `WHERE` clause to negate a condition. This allows you to select rows where a specified condition is *not* true. This tutorial shows how to use `NOT` with various other conditions.

`NOT` Condition Syntax

The basic syntax is:

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Replace `condition` with the condition you want to negate.

Examples of `NOT` with Different Conditions

`NOT` with `IN`

Select rows where a column's value is *not* in a given list:

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

(Example showing the output of this query would be included here.)

`NOT` with `LIKE`

Select rows where a column's value does *not* match a pattern:

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

(Example showing the output of this query would be included here.)

`NOT` with `BETWEEN`

Select rows where a column's value is *not* within a specified range:

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

(Example showing the output of this query would be included here.)

`NOT` with `IS NULL`

Select rows where a column is *not* NULL:

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

(Example showing the output of this query would be included here.)

`NOT` with `EXISTS`

Select rows where a subquery does *not* return any rows:

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

(Example showing the output of this query would be included here.)

Conclusion

The `NOT` condition in PostgreSQL provides a way to invert conditions in your `WHERE` clause, allowing for more flexible data selection. Combining `NOT` with other conditions significantly expands your query capabilities.