BETWEEN operator in SQL

The BETWEEN .. AND operator in SQL is used to filter the data within a specific range.

This operator can be used with numbers, text (to filter within alphabetical ranges), and dates, making it versatile for various types of Data Analysis.

Syntax

The syntax for BETWEEN .. AND is straightforward. You specify the column name followed by the BETWEEN keyword, the start of the range, the AND keyword, and then the end of the range.

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2

For example, to select users with age between 21 and 65:

SELECT
  age,
  *
FROM users
WHERE
  age BETWEEN 21 AND 65

The BETWEEN operator is inclusive, meaning it includes the values specified by the BETWEEN and AND keywords to the result (value1 and value2 in our case).

In practice, it means that in this query’s result set you’ll find users with age 21 and 65.

Selecting dates within a range

The BETWEEN .. AND operator is particularly useful for performing range queries, such as finding records within a specific date range. Here’s a query that finds users who signed up within a specific date range:

SELECT *
FROM users
WHERE
  signup_date BETWEEN '2018-01-01' AND '2018-01-31'

Analyzing specific numerical ranges

To filter purchases within a specific amount range:

SELECT *
FROM purchases
WHERE
  amount BETWEEN 10 AND 20

This query is equivalent to the following:

SELECT *
FROM purchases
WHERE
  amount >= 10
  AND amount <= 20

As you can see, the BETWEEN..AND version is more concise and readable.

Understanding and using the BETWEEN .. AND operator is crucial for efficiently querying subsets of data based on specific criteria.

The only trick to remember is that the BETWEEN operator is inclusive.

See also

IN operator in SQL

IS operator in SQL

AND operator in SQL

OR operator in SQL