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.