BETWEEN: Filter a Range of Values
The BETWEEN keyword filters rows where a value is inside a range.
It works for numbers, dates and even text (in alphabetical order).
Syntax
SELECT column1, column2, ... FROM table_name
WHERE column BETWEEN value1 AND value2;
This returns rows where the value is greater than or equal to value1 and less than or equal to value2. Both ends of the range are included.
Example 1: Employees aged between 30 and 32
SELECT name, age FROM employees
WHERE age BETWEEN 30 AND 32;
Output:
name | age
-------+-----
Bob | 32
David | 30
Grace | 31
Lucy | 30
Paul | 32
Tina | 31
(6 rows)
This returns employees aged 30, 31 or 32. The ages 30 and 32 are included.
Example 2: Departments between 'Finance' and 'IT' (alphabetically)
SELECT DISTINCT department FROM employees
WHERE department BETWEEN 'Finance' AND 'IT'
ORDER BY department;
Output:
department
------------
Finance
HR
IT
(3 rows)
This works because text can be compared in alphabetical order. HR comes after Finance and before IT.
Tip
You can also combine BETWEEN with NOT:
SELECT name, age FROM employees
WHERE age NOT BETWEEN 25 AND 32;
Output:
name | age
--------+-----
Mike | 24
Jack | 33
Anna | 24
Thomas | 33
(4 rows)
This returns employees whose age is outside the range 25 to 32.
In the exercises, use BETWEEN and NOT BETWEEN on the age column.