How to Use BETWEEN in SQL: A Complete Guide with Examples
The BETWEEN operator in SQL is a logical operator that filters rows based on whether a value falls within a specific range. Now, instead of writing two separate comparison conditions, you can express "from this value to that value" in a single, readable line. This guide explains how to use BETWEEN in SQL with numbers, dates, and text, how it behaves with NULL values, how it affects query performance, and which common mistakes to avoid so your queries return exactly the data you expect Took long enough..
What Is the SQL BETWEEN Operator?
BETWEEN is a comparison operator that tests whether an expression lies within a range defined by a lower bound and an upper bound. It is most often used in the WHERE clause to filter rows, but it also works in HAVING, CASE expressions, and join conditions.
One detail matters more than any other: BETWEEN is inclusive on both ends. A value equal to the lower bound or the upper bound is considered inside the range. In other words:
column BETWEEN value1 AND value2
is exactly equivalent to:
column >= value1 AND column <= value2
Here, the AND keyword is part of the BETWEEN syntax itself. It separates the two bounds; it is not combining two independent conditions. Because of this, BETWEEN works with numbers, dates, and text, as long as the values can be compared in a meaningful order.
Basic Syntax and a Sample Table
The general pattern looks like this:
SELECT column_list
FROM table_name
The **BETWEEN operator in SQL** is a logical operator that filters rows based on whether a value falls within a specific range. Instead of writing two separate comparison conditions, you can express "from this value to that value" in a single, readable line. This guide explains how to use BETWEEN in SQL with numbers, dates, and text, how it behaves with NULL values, how it affects query performance, and which common mistakes to avoid so your queries return exactly the data you expect.
Real talk — this step gets skipped all the time.
## What Is the SQL BETWEEN Operator?
**BETWEEN is a comparison operator that tests whether an expression lies within a range defined by a lower bound and an upper bound.** It is most often used in the `WHERE` clause to filter rows, but it also works in `HAVING`, `CASE` expressions, and join conditions.
One detail matters more than any other: **BETWEEN is inclusive on both ends.** A value equal to the lower bound or the upper bound is considered inside the range. In other words:
```sql
column BETWEEN value1 AND value2
is exactly equivalent to:
column >= value1 AND column <= value2
Here, the AND keyword is part of the BETWEEN syntax itself. It separates the two bounds; it is not combining two independent conditions. Because of this, BETWEEN works with numbers, dates, and text, as long as the values can be compared in a meaningful order Simple as that..
Basic Syntax and a Sample Table
The general pattern looks like this:
SELECT column_list
FROM table_name
WHERE column BETWEEN low_bound AND high_bound;
To make the examples concrete, consider a products table:
| product_id | product_name | price | category_id | in_stock |
|---|---|---|---|---|
| 1 | Wireless Mouse | 25.00 | 3 | 1 |
| 3 | 27-inch Monitor | 300.In practice, 00 | 3 | 0 |
| 7 | External SSD 1TB | 110. 00 | 2 | 1 |
| 4 | USB-C Cable (2m) | 15.00 | 3 | 1 |
| 2 | Mechanical Keyboard | 75.That's why 00 | 4 | 0 |
| 5 | Laptop Stand | 45. 00 | 4 | 1 |
| 6 | Webcam 1080p | 60.00 | 2 | 1 |
| 8 | Notebook (A5) | 5. |
Using BETWEEN with Numeric Values
Numeric filtering is the most common use case. As an example, to find all products priced between $50 and $100:
SELECT product_id, product_name, price
FROM products
WHERE price BETWEEN 50 AND 100;
This returns:
| product_id | product_name | price |
|---|---|---|
| 2 | Mechanical Keyboard | 75.00 |
| 6 | Webcam 1080p | 60.00 |
Notice that both boundaries are included. A product priced exactly at 50 or 100 would also appear Less friction, more output..
Performance Tip
For large tables, ensure there is an index on the column used with BETWEEN. A standard B-tree index can efficiently handle range scans, making BETWEEN queries fast Worth knowing..
Using BETWEEN with Dates
Date ranges are equally straightforward. Suppose you have an orders table with an order_date column. To retrieve all orders placed in the last quarter of 2023:
SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date BETWEEN '2023-10-01' AND '2023-12-31';
Because BETWEEN is inclusive, the query includes orders from October 1st through December 31st. If your database stores timestamps, you might need to account for the time component. To give you an idea, to include the entire last day:
WHERE order_date BETWEEN '2023-10-01' AND '2023-12-31 23:59:59'
Alternatively, some databases support date literals or functions to simplify this, such as using DATE casts or TIMESTAMP ranges Practical, not theoretical..
Using BETWEEN with Text (Strings)
BETWEEN also works on character data, following collation order. As an example, to find all products whose names start with letters from 'A' to 'M':
SELECT product_id, product_name
FROM products
WHERE product_name BETWEEN 'A' AND 'M';
This would include names like 'External SSD 1TB' and 'Laptop Stand' but exclude 'Mechanical Keyboard' and 'Wireless Mouse'. String comparisons are lexicographic, so 'A' to 'M' includes any string that sorts between these two values in the defined collation Worth keeping that in mind..
Case Sensitivity
Collation determines case sensitivity. In a case-insensitive collation, 'a' and 'A'
Case Sensitivity (Continued)
Collation determines whether comparisons are case‑sensitive. In a case‑insensitive collation (common in many SQL dialects suchLady), 'a' and 'A' are treated as equal, so a condition like:
WHERE product_name BETWEEN 'a' AND 'z'
will match both “Laptop Stand” and “laptop stand”. Conversely, in a case‑sensitive collation, only strings that exactly match the letter case will be included.
-- Example in a case‑sensitive collation
WHERE product_name BETWEEN 'A' AND 'M'
would exclude “laptop stand” because 'l' sorts before 'A' in ASCII order. When designing queries that involve alphabetic ranges, verify the collation of your connection or database to avoid unexpected omissions Practical, not theoretical..
Common Pitfalls to Watch Out For
| Issue | Why it matters | Typical fix |
|---|---|---|
| NULL handling | BETWEEN returns UNKNOWN if either side of the expression is NULL. That said, a row containing NULL in the filtered column will be omitted from results, which can be surprising. This leads to |
Add explicit checks: WHERE (price IS NOT NULL) AND (price BETWEEN 10 AND 20) |
| Data‑type mismatch | Mixing types (e. g., VARCHAR vs NUMERIC) can cause implicit casts that affect ordering or performance. |
Ensure both sides are of compatible types, or cast explicitly: WHERE CAST(price AS DECIMAL) BETWEEN 10 AND 20 |
| Inclusive boundaries | BETWEEN includes the endpoints, which may unintentionally capture values you wanted to exclude. |
Use > and < (or >=/<=) if exclusive bounds are required. Now, |
| Index utilization | Without an appropriate index, a BETWEEN predicate can degenerate into a full table scan, especially on wide tables. In practice, |
Create a B‑tree index on the filtered column; for composite queries, include supporting columns. Here's the thing — |
| Date‑time precision | When order_date stores timestamps, a range like BETWEEN '2023-10-01' AND '2023-이어서' may miss events on the last day because time components are truncated. |
Extend the upper bound to include time ('2023-12-31 23:59:59'), or use functions like DATE_TRUNC to normalize. |
When to Choose IN vs BETWEEN
BETWEENis ideal for continuous ranges (numeric, temporal, or even ordered strings). It expresses intent as “a range of values” and can be more readable.INis better when you have a discrete set of values (often supplied programmatically). It also works with sub‑queries and can be optimized differently by the optimizer.
-- Range example (BETWEEN)
WHERE price BETWEEN 50 AND 100;
-- Discrete set example (IN)
WHERE product_id IN (2, 5, 8);
In some databases, IN can be rewritten as OR conditions internally, while BETWEEN may be more efficient for indexed range scans. Test both on your
When you run IN against a list of values that comes from an application program, the optimizer sometimes expands the predicate into a series of equality checks rather than a single scan. To see which approach truly outperforms the other on your platform, start by issuing EXPLAIN ANALYZE (or the equivalent profiling command) before and after applying either form. Look at the cost estimate and row count reported for each clause; if they differ dramatically, the chosen predicate may be forcing a full table lookup even though a simple index would suffice. Alternatively, BETWEEN is usually compiled into a single range‑scan operation, especially when the target column is indexed. For discrete value sets supplied at runtime—product codes, IDs, status constants—IN often reads more readable, and its internal conversion to multiple OR clauses can be optimized away by newer query planners. Conversely, for continuous numeric intervals such as price ranges, timestamps spanning days or months, stick with BETWEEN unless you have a high‑cardinality set of specific values that makes IN clearer Small thing, real impact..
The discussion of case‑sensitive collation also deserves a practical tip beyond the example you quoted. Still, when you deliberately want exact letter matching—as in the “laptop stand” example—these settings will silently drop entries whose first character differs in case. UTF-8_CI, or another variant, and explicitly cast or qualify columns (LOWER(product_name), COLLATE 'C') when you need predictable ordering. Always verify whether the connection uses ci, cs, en_US.Many databases store letters according to the default locale’s collation; if you switch to a case‑insensitive setting, 'a' and 'A' are treated identically during sorting. This habit prevents subtle bugs where a term like “Laptop Stand” appears missing simply because the engine treats upper‑ and lower‑case variants separately No workaround needed..
A handful of additional gotchas round out the checklist:
- NULL propagation – A
BETWEENclause returnsUNKNOWNif either bound isNULL. Guarding against this with explicit existence tests (WHERE col IS NOT NULL AND col BETWEEN …) keeps results honest. - Data‑type mismatches – Storing prices as
NUMERIC(10,2)and querying with aVARCHARliteral forces an implicit cast, potentially causing sorting errors or unexpected truncation. Casting either side (CAST(col AS DECIMAL(10,2)) BETWEEN 10 AND 20) eliminates ambiguity. - Date‑time truncation – If
order_dateholds full timestamps, aBETWEEN '2023-01-01' AND '2023-12-31'may miss events on the last day because the time portion is omitted. Extend the upper bound to'2023-12-31 23:59:59'or applyDATE_TRUNC('day', order_date) BETWEEN ...when day granularity suffices.
Finally, always create indexes that match the patterns you’ll filter on. A B‑tree index on price speeds up both BETWEEN and IN lookups, while a composite index covering (product_name, price) helps when you combine textual and numeric predicates. Re‑evaluating these structures after schema changes—or after adding new columns—ensures that future queries stay performant without resorting to full scans.
Conclusion
Writing reliable range‑based queries requires attention to collation choices, null safety
and null safety, but also disciplined schema design and testing. A query can be syntactically valid and still produce misleading results if the underlying data types, collations, or boundary conditions are not aligned with the business meaning Less friction, more output..
Before finalizing any range predicate, ask what “between” means in your context. Are endpoints inclusive? Does the system apply them inclusively? For continuous metrics, BETWEEN is usually the right tool; for an explicit set of acceptable values, IN is clearer and often more maintainable. When the set is large or dynamic, consider joining to a lookup table rather than embedding thousands of literals.
For time-based filters, normalize timestamps to a common precision and decide whether the comparison should be inclusive of the final instant. For numeric filters, avoid implicit casts between decimal and string types. For text filters, enforce a deterministic