---
title: "BETWEEN"
description: "The BETWEEN operator tests whether a value falls within an inclusive range, equivalent to combining two comparisons with AND."
canonical: "https://motherduck.com/glossary/between/"
related:
  - title: "DuckDB SQL | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/"
  - title: "Perf is not enough"
    url: "https://motherduck.com/blog/perf-is-not-enough/"
  - title: "Running dual execution (or hybrid) queries | MotherDuck Docs"
    url: "https://motherduck.com/docs/key-tasks/running-hybrid-queries/"
---

# BETWEEN

> The BETWEEN operator tests whether a value falls within an inclusive range, equivalent to combining two comparisons with AND.

## Overview
`expr BETWEEN low AND high` is shorthand for `expr >= low AND expr <= high` -- it's inclusive on both ends. It works with numbers, dates, timestamps, and any other orderable type, and it reads more naturally than the equivalent compound condition for simple range checks.

```sql
SELECT order_id, order_total
FROM orders
WHERE order_total BETWEEN 50 AND 200;

SELECT event_name, event_date
FROM events
WHERE event_date BETWEEN DATE '2026-01-01' AND DATE '2026-03-31';
```

<glossary-callout guide="duckdb-cheatsheet-full" />

## NOT BETWEEN
Negating the range is just as readable:
```sql
SELECT * FROM orders WHERE order_total NOT BETWEEN 50 AND 200;
```

## Common pitfall: date ranges
A frequent mistake is using `BETWEEN` with a bare date on one side and a `TIMESTAMP` column on the other, expecting it to cover the whole end day. `col BETWEEN '2026-01-01' AND '2026-01-31'` on a `TIMESTAMP` column effectively means `... AND '2026-01-31 00:00:00'`, silently excluding any timestamps later in that final day. The safer pattern is an explicit half-open range:

```sql
SELECT * FROM events
WHERE event_ts >= TIMESTAMP '2026-01-01 00:00:00'
  AND event_ts <  TIMESTAMP '2026-02-01 00:00:00';
```

## BETWEEN SYMMETRIC (DuckDB)
DuckDB supports `BETWEEN SYMMETRIC`, which works even if the "low" and "high" bounds are supplied in the wrong order -- it automatically treats whichever value is smaller as the lower bound:

```sql
SELECT 5 BETWEEN SYMMETRIC 10 AND 1;  -- TRUE, equivalent to 5 BETWEEN 1 AND 10
```

## Performance
Because `BETWEEN` decomposes into two comparisons, it can use the same indexes or zone-map pruning that a range scan on `>=`/`<=` would use -- there's no performance penalty for using `BETWEEN` over the equivalent `AND` expression.