BETWEEN for range filtering

Concepts covered: Range Filtering

BETWEEN low AND high keeps a row when the value is at least low and at most high. It is the same test as value >= low AND value <= high written as a single operator, and both ends are included. The order of the bounds matters: total BETWEEN 1500 AND 500 asks for a number at least 1500 and at most 500, no number satisfies that, and the result is empty rather than an error. The animation applies 2 ranges to a set of orders.

SELECT order_id, order_date, total
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'
  AND total BETWEEN 500 AND 1500

Two of the 5 orders survive. Order 1 sits well inside both ranges. Order 4 lands on 2024-03-31 with a total of 1500, the upper bound of each range, and passes because BETWEEN includes its endpoints. Order 2's total of 300 is under 500, order 3 falls in April, and order 5 belongs to the previous year. The order_date column holds text in year-month-day form, and text in that form compares in calendar order, which is why a range of string literals works here.

The products table from the last section has prices 12, 45, 45, 90, 90, 120, 310 and 310. How many rows does WHERE price BETWEEN 45 AND 90 return?

• 2, the rows priced at exactly 45
• 5, the rows priced from 45 up to 120
• 0, because the bounds should be written high to low
• 4, the 2 rows at 45 and the 2 rows at 90

The last day problem

A timestamp column carries a time of day, and the bound TIMESTAMP '2024-03-31 00:00:00' is the first instant of March 31, not the last. A BETWEEN that ends there keeps events at exactly midnight and drops everything else that day. The events table below stores occurred_at as text from an event log, so each query casts it to TIMESTAMP before comparing.

BETWEEN

Both bounds included, with the upper bound at midnight on the last day of the quarter.

SELECT
  event_id,
  occurred_at
FROM events
WHERE CAST(occurred_at AS TIMESTAMP)
  BETWEEN TIMESTAMP '2024-01-01 00:00:00'
  AND TIMESTAMP '2024-03-31 00:00:00'

Half-open

Lower bound included, upper bound excluded and set to the first instant of the next quarter.

SELECT
  event_id,
  occurred_at
FROM events
WHERE CAST(occurred_at AS TIMESTAMP) >= TIMESTAMP '2024-01-01 00:00:00'
  AND CAST(occurred_at AS TIMESTAMP) < TIMESTAMP '2024-04-01 00:00:00'

The BETWEEN version returns 3 events and loses event 4, which happened at 18:20 on March 31, inside the quarter by any sensible definition. The half-open version returns 4. Its lower bound is included and its upper bound is excluded, set to the first instant of the next period, so every moment of March 31 passes. Event 5 at midnight on April 1 falls out of both, which is right, since it opens the next quarter. Writing the upper bound as 23:59:59 instead rescues whole seconds but still drops an event stamped 23:59:59.500.

BETWEEN on text

Text ranges compare character by character. name BETWEEN 'A' AND 'M' keeps 'Apple' and 'Lemon' but drops 'Mango', because 'Mango' shares its first character with 'M' and then has more characters, and a longer string sorts after its own prefix. To keep every name beginning with M, use the half-open shape again: name >= 'A' AND name < 'N' admits all of them and stops at the first N.

The AND inside BETWEEN belongs to BETWEEN. In total BETWEEN 500 AND 1500 AND status = 'shipped' the engine reads the first AND as the range separator and the second as a connector, and BETWEEN resolves before that connector, the same as any comparison. NOT BETWEEN keeps the rows outside the range, and a NULL total matches neither form, since NULL compared to either bound is NULL.

About this lesson

This section is part of the Filtering: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.