PostgreSQL - BETWEEN Operator

The BETWEEN operator in PostgreSQL is used to filter rows where a column value falls within a specified range. The range is inclusive, meaning both the starting and ending values are included in the result.

• Can be used with numeric, text and date values.
• Can be combined with clauses such as WHERE, ORDER BY and LIMIT.

Syntax

SELECT column_1, column_2, ...
FROM table_name
WHERE column_name BETWEEN value_1 AND value_2;

Where:

• column_1, column_2, ...: The columns to retrieve.
• table_name: The name of the table.
• column_name: The column whose values are filtered.
• value_1: The lower boundary of the range.
• value_2: The upper boundary of the range.

Examples

Firstly, create a student table and insert some records into it.

CREATE TABLE student (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    course VARCHAR(50),
    age INT
);

INSERT INTO student (student_id, first_name, last_name, course, age)
VALUES
(101, 'John', 'Smith', 'Computer Science', 20),
(102, 'Emma', 'Johnson', 'Mathematics', 21),
(103, 'Michael', 'Brown', 'Physics', 22),
(104, 'Olivia', 'Davis', 'Chemistry', 20),
(105, 'William', 'Wilson', 'Biology', 23);

Output:

Example 1: Use BETWEEN with Numeric Values

The following query retrieves students whose age is between 20 and 22.

Query:

SELECT * FROM student
WHERE age BETWEEN 20 AND 22;

Output:

Example 2: Use NOT BETWEEN

The following query retrieves students whose age is not between 20 and 22.

Query:

SELECT * FROM student
WHERE age NOT BETWEEN 20 AND 22;

Output:

Example 3: Use BETWEEN with Text Values

The following query retrieves students whose first names fall alphabetically between 'E' and 'O'.

Query:

SELECT * FROM student
WHERE first_name BETWEEN 'E' AND 'O';

Output:

Example 4: Use BETWEEN with Dates

Firstly, create an employee table.

Query:

CREATE TABLE employee (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    hire_date DATE
);

INSERT INTO employee (employee_id, employee_name, hire_date)
VALUES
(1, 'John Smith', '2022-01-15'),
(2, 'Emma Johnson', '2022-06-10'),
(3, 'Michael Brown', '2023-03-20'),
(4, 'Olivia Davis', '2023-08-05');

Output:

• The following query retrieves employees hired between January 1, 2022 and December 31, 2022.

Query:

SELECT * FROM employee
WHERE hire_date BETWEEN '2022-01-01' AND '2022-12-31';

Output:

Example 5: Use BETWEEN with ORDER BY

The following query retrieves students whose age is between 20 and 23 and sorts them by age in descending order.

Query:

SELECT * FROM student
WHERE age BETWEEN 20 AND 23
ORDER BY age DESC;

Output: