SQL BETWEEN: Filter a Range of Numbers, Dates or Text

BETWEEN is used in a WHERE clause to keep rows whose value falls inside a range. This query lists products priced from 100 to 200:

SELECT name, category, price
FROM products
WHERE price BETWEEN 100 AND 200
ORDER BY price
LIMIT 6;

▶ Run this query in the SQL Playground

SELECT columns
FROM table_name
WHERE column_name BETWEEN low_value AND high_value;

The lower value is written first. BETWEEN works with numbers, dates and text.

BETWEEN is shorthand for two comparisons joined with AND. Counting the same range both ways gives the same answer:

SELECT
  (SELECT COUNT(*) FROM products WHERE price BETWEEN 100 AND 200) AS with_between,
  (SELECT COUNT(*) FROM products WHERE price >= 100 AND price <= 200) AS with_comparisons;

▶ Run this query in the SQL Playground

Both columns show 7. A product priced at exactly 100 or exactly 200 is included in either version.

Date ranges are the most common use. This query counts the orders placed in the first quarter of 2024 and adds up their value:

SELECT COUNT(*) AS orders,
       ROUND(SUM(total), 2) AS revenue
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

▶ Run this query in the SQL Playground

NOT BETWEEN returns the rows outside the range. Here it finds salaries below 60,000 or above 150,000:

SELECT first_name, last_name, department, salary
FROM employees
WHERE salary NOT BETWEEN 60000 AND 150000
ORDER BY salary DESC
LIMIT 6;

▶ Run this query in the SQL Playground

Because BETWEEN includes both ends, NOT BETWEEN excludes them: a salary of exactly 60,000 is not returned.

With text, BETWEEN compares values in alphabetical order. That makes the upper end easy to get wrong. Which initials does this range return?

SELECT DISTINCT SUBSTR(last_name, 1, 1) AS initial
FROM employees
WHERE last_name BETWEEN 'A' AND 'C'
ORDER BY initial;

▶ Run this query in the SQL Playground

A name such as "Clark" sorts after the single letter "C", so it falls outside the range. To include every name that starts with C, extend the upper bound (BETWEEN 'A' AND 'Cz') or, more clearly, use last_name >= 'A' AND last_name < 'D'.

Three ways a BETWEEN filter returns the wrong rows.

SELECT name, price
FROM products
WHERE price BETWEEN 200 AND 100;

SELECT name, price
FROM products
WHERE price BETWEEN 100 AND 200;

WHERE created_at BETWEEN '2024-03-01'
                     AND '2024-03-31'

WHERE created_at >= '2024-03-01'
  AND created_at <  '2024-04-01'

WHERE age BETWEEN 18 AND 65

WHERE age > 18 AND age < 65

4 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy How many products have a rating from 4.0 to 4.5? Table: products

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS products
   FROM products
   WHERE rating BETWEEN 4.0 AND 4.5;

   ▶ Run this query in the SQL Playground

   Both 4.0 and 4.5 are included in the range.

2. 2Easy List employees hired during 2022, earliest first. Show ten rows. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT first_name, last_name, hire_date
   FROM employees
   WHERE hire_date BETWEEN '2022-01-01' AND '2022-12-31'
   ORDER BY hire_date
   LIMIT 10;

   ▶ Run this query in the SQL Playground

   hire_date stores a date with no time, so the last day of the year is fully included.

3. 3Medium Count orders with a total outside the range 100 to 1,000, grouped by status. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT status, COUNT(*) AS orders
   FROM orders
   WHERE total NOT BETWEEN 100 AND 1000
   GROUP BY status
   ORDER BY orders DESC;

   ▶ Run this query in the SQL Playground

   NOT BETWEEN keeps totals below 100 or above 1,000; GROUP BY then counts them per status.

4. 4Medium Find orders placed in December 2024 with a total between 500 and 2,000. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT id, order_date, total
   FROM orders
   WHERE order_date BETWEEN '2024-12-01' AND '2024-12-31'
     AND total BETWEEN 500 AND 2000
   ORDER BY order_date, id;

   ▶ Run this query in the SQL Playground

   Two BETWEEN conditions joined with AND: one for the date range and one for the amount.