PostgreSQL BETWEEN: Performing Range Tests

Summary: in this tutorial, you will learn how to use the PostgreSQL BETWEEN operator in the WHERE clause to check if a value is between two values.

PostgreSQL BETWEEN operator

The BETWEEN operator is a comparison operator that returns true if a value is between two values.

Here’s the syntax of the BETWEEN operator:

value BETWEEN low AND highCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The BETWEEN operator returns true if the value is between low and high:

low <= value <= highCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In SQL, the BETWEEN operator is a shorthand of writing the following expression:

value >= low AND value <= highCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Hence, the BETWEEN operator simplifies the above range test.

In practice, you’ll use the BETWEEN operator in the WHERE clause to filter rows based on a range of values.

PostgreSQL BETWEEN operator example

Suppose we have the following inventories table:

CREATE TABLE inventories (
  name VARCHAR(255),
  brand VARCHAR(50),
  quantity INT,
  price DECIMAL(19, 2)
);

INSERT INTO
  inventories (name, brand, quantity, price)
VALUES
  ('iPhone 14 Pro', 'Apple', 10, 999.99),
  ('Galaxy S23 Ultra', 'Samsung', 15, 1199.99),
  ('Pixel 7 Pro', 'Google', 8, 899.99),
  ('Xperia 1 IV', 'Sony', 7, 1299.99);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Data:

       name       |  brand  | quantity |  price
------------------+---------+----------+---------
 iPhone 14 Pro    | Apple   |       10 |  999.99
 Galaxy S23 Ultra | Samsung |       15 | 1199.99
 Pixel 7 Pro      | Google  |        8 |  899.99
 Xperia 1 IV      | Sony    |        7 | 1299.99Code language: plaintext (plaintext)

The following example uses the BETWEEN operator to find the products in the inventories table with prices are between 899.99 and 999.99:

SELECT
  name,
  price
FROM
  inventories
WHERE
  price BETWEEN 899.99 AND 999.99;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

     name      | price
---------------+--------
 iPhone 14 Pro | 999.99
 Pixel 7 Pro   | 899.99Code language: plaintext (plaintext)

NOT BETWEEN operator

The NOT operator negates the BETWEEN operator:

value NOT BETWEEN low AND highCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The NOT BETWEEN returns true if the value is less than low or greater than high:

value < low OR value > highCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following statement uses the NOT BETWEEN operator to find products whose prices are not between 899.99 and 999.99:

SELECT
  name,
  price
FROM
  inventories
WHERE
  price NOT BETWEEN 899.99 AND 999.99;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

       name       |  price
------------------+---------
 Galaxy S23 Ultra | 1199.99
 Xperia 1 IV      | 1299.99Code language: plaintext (plaintext)

Summary

Use the PostgreSQL BETWEEN operator to filter values that are between low and high values.
Use the NOT operator to negate the BETWEEN operator.