BETWEEN Operator in SQL
Introduction
The BETWEEN operator filters values within a lower and upper boundary. It provides concise syntax for closed numeric, date, and ordered-value ranges. A query can use it to find salaries from 60,000 through 90,000, scores from 70 through 100, or orders dated within a reporting period.
SELECT product_id, product_name, price
FROM products
WHERE price BETWEEN 100.00 AND 500.00;
Both boundaries are included. That one rule is the source of many production defects, especially when developers expect an exclusive upper limit or use DATE boundaries against TIMESTAMP data. Reliable ranges require explicit boundary semantics, compatible types, deliberate NULL behavior, and tests immediately around every endpoint.
Basic BETWEEN Syntax
expression BETWEEN lower_boundary AND upper_boundary
BETWEEN is a range predicate. The AND inside its syntax separates lower and upper bounds; it is not a second independent WHERE condition. The lower boundary should normally be less than or equal to the upper boundary.
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary BETWEEN :minimum_salary AND :maximum_salary;
Bind both values as the same compatible type as salary. Do not construct the range by concatenating user input into SQL.
BETWEEN Is Inclusive
BETWEEN includes the lower and upper boundaries. A price exactly 100.00 and a price exactly 500.00 both satisfy the first example.
| Price | BETWEEN 100 AND 500 |
|---|---|
| 99.99 | FALSE |
| 100.00 | TRUE |
| 300.00 | TRUE |
| 500.00 | TRUE |
| 500.01 | FALSE |
Translate requirements such as "from," "through," "at least," and "up to" carefully. Natural language can be ambiguous, so confirm examples at both boundaries before choosing BETWEEN.
Equivalent Comparison Operators
price BETWEEN 100.00 AND 500.00
-- Equivalent for known comparable values:
price >= 100.00 AND price <= 500.00
The expanded form makes inclusiveness visible. Optimizers generally understand both forms, so choose based on semantic clarity rather than assuming one is inherently faster. NULL produces UNKNOWN in either form.
Numeric BETWEEN
Numeric ranges work well for integers, exact decimals, counts, scores, ages, ratings, percentages, and monetary bands when the stored type matches the domain.
SELECT student_id, final_score
FROM exam_results
WHERE final_score BETWEEN 70 AND 100;
This includes 70 and 100. If valid scores must also stay in that range for every write, add a CHECK constraint rather than relying only on read filters.
Decimal and Money Boundaries
Money should generally use DECIMAL or NUMERIC with documented scale. Boundary tests must use the smallest meaningful unit. For a two-decimal price band, test 99.99, 100.00, 100.01, 499.99, 500.00, and 500.01.
Do not store money as text or use approximate floating-point types when exact equality and range endpoints matter. Currency, rounding, tax basis, and inclusive policy are separate business facts that the predicate alone cannot define.
Inclusive, Exclusive, and Mixed Ranges
BETWEEN expresses a closed range: lower included and upper included. It cannot directly express a fully exclusive or mixed-boundary range.
-- Exclusive range.
WHERE value > :lower_bound
AND value < :upper_bound
-- Lower included, upper excluded.
WHERE value >= :lower_bound
AND value < :upper_bound
Use explicit comparisons whenever endpoint treatment differs. That makes the rule reviewable and prevents accidental overlap between adjacent ranges.
Why Half-Open Ranges Matter
A half-open range includes its start and excludes its end. Adjacent intervals can meet at one boundary without overlap.
WHERE value >= :range_start
AND value < :next_range_start
This design is especially useful for timestamps, pricing tiers, partitions, and effective-dated records. The end of one interval can be exactly the start of the next.
BETWEEN with DATE Values
BETWEEN is appropriate when the column and boundaries are true DATE values and both calendar dates should be included.
SELECT holiday_id, holiday_date, holiday_name
FROM holidays
WHERE holiday_date BETWEEN DATE '2026-11-01' AND DATE '2026-11-30';
Use typed parameters or literals supported by the database. Avoid ambiguous locale-dependent strings such as 11/01/2026.
DATE versus TIMESTAMP
A DATE stores a calendar date. A TIMESTAMP stores date and time, often with fractional seconds and product-specific time-zone behavior. Applying a DATE end boundary to a TIMESTAMP column typically represents midnight at the start of that date, not the entire day.
-- Often misses most events on November 30.
WHERE created_at BETWEEN :november_1_midnight AND :november_30_midnight
Events later than midnight on November 30 fall above the inclusive upper boundary.
Safe Timestamp Ranges
Use a half-open interval with the start of the following period.
SELECT order_id, created_at, total_amount
FROM orders
WHERE created_at >= :month_start
AND created_at < :next_month_start;
This includes every representable timestamp in the month without guessing the final fractional second. It also works when timestamp precision changes and lets adjacent reports share exact boundaries.
Avoid End-of-Day Guesses
Techniques such as 23:59:59 or 23:59:59.999 are fragile because database precision varies. They can create gaps, rounding surprises, and duplicate rows between consecutive windows.
Calculate the next day's midnight and use <. Transform parameters rather than wrapping the indexed timestamp column in a date-extraction function whenever possible.
Time Zones and Daylight Saving Time
A business day depends on a time zone. Convert the requested local boundaries to the storage convention, commonly UTC, before comparison. Do not add a fixed twenty-four hours blindly across daylight-saving transitions, because some local days contain twenty-three or twenty-five hours.
Record the zone used for reporting and test clock-change dates. The database type and driver must preserve the intended instant rather than silently applying a session zone.
BETWEEN with Strings
Text BETWEEN follows the column's collation and character ordering, not ordinary alphabetic intuition.
WHERE product_code BETWEEN 'A100' AND 'A999'
Case, accents, punctuation, numeric-looking suffixes, trailing spaces, and locale rules can affect results. Use text ranges only for well-defined ordered codes. For categories or exact values, IN or a reference table is usually clearer.
Collation Controls Text Ranges
A case-insensitive collation can treat uppercase and lowercase as equivalent, while a binary-like collation compares encoded values. Accent sensitivity and language-specific ordering also matter. Therefore, the same textual range can return different rows under different collations.
Document the required collation for business identifiers and test representative Unicode values. Do not use a broad text BETWEEN as a substitute for domain modeling.
BETWEEN and NULL
If the expression, lower boundary, or upper boundary is NULL, BETWEEN normally evaluates to UNKNOWN. WHERE excludes UNKNOWN.
WHERE amount BETWEEN :minimum_amount AND :maximum_amount
A row with NULL amount does not qualify. A NULL parameter does not mean open-ended automatically; it can cause the entire range test to become UNKNOWN.
Open-Ended Ranges
Represent a missing lower or upper filter explicitly rather than substituting fake extreme values.
WHERE (:minimum_amount IS NULL OR amount >= :minimum_amount)
AND (:maximum_amount IS NULL OR amount <= :maximum_amount)
This static optional-filter pattern is convenient, though it can produce parameter-sensitive plans on large tables. A server-controlled dynamic query can add only supplied predicates while parameterizing every value.
Why Fake Boundaries Are Risky
Replacing an absent lower date with 1900-01-01 or an absent upper amount with an arbitrary huge number embeds assumptions that may become invalid. The real data domain may include earlier dates or larger values, and sentinel values can interfere with indexes and constraints.
Model open bounds as optional predicates or explicit NULL endpoints. Validate the range request before executing SQL.
Validating User-Supplied Bounds
Ensure lower and upper inputs have the correct type, permitted scale, supported time zone, and sensible order. Most databases do not automatically swap reversed BETWEEN boundaries.
-- Usually no known value can satisfy this reversed range.
WHERE price BETWEEN 500.00 AND 100.00
Return a validation error or deliberately normalize bounds according to a documented API contract. Silent swapping can hide caller defects.
Parameterized BETWEEN
Bind range values as parameters of the column's actual type.
SELECT transaction_id, amount
FROM transactions
WHERE tenant_id = :tenant_id
AND amount BETWEEN :minimum_amount AND :maximum_amount;
Parameterization prevents values from changing SQL structure and avoids locale-dependent string formatting. It does not validate business order or authorization; those remain explicit responsibilities.
BETWEEN with Mandatory Security Scope
A range is a business filter, not an authorization boundary. Tenant and ownership conditions must remain mandatory.
WHERE tenant_id = :authenticated_tenant_id
AND customer_id = :authorized_customer_id
AND total_amount BETWEEN :minimum_amount AND :maximum_amount
Never trust tenant or owner values supplied as ordinary search fields. Derive them from authenticated context and test with out-of-scope rows inside the requested range.
BETWEEN in UPDATE
UPDATE can target a range, but broad mutation requires careful review.
UPDATE products
SET discount_rate = :discount_rate
WHERE tenant_id = :tenant_id
AND price BETWEEN :minimum_price AND :maximum_price
AND status = 'ACTIVE';
Preview the exact predicate with SELECT, verify expected counts, use a transaction, check affected rows, and understand concurrent changes. A range that was correct when previewed can change before execution unless the operation controls concurrency.
BETWEEN in DELETE
DELETE ranges are especially sensitive because both endpoints are included. A retention cleanup should normally use a clear cutoff rather than an arbitrary closed range.
DELETE FROM audit_events
WHERE tenant_id = :tenant_id
AND occurred_at < :retention_cutoff;
If BETWEEN is genuinely required, preview targets, batch large deletes on a stable key, monitor logs and replication, and test recovery. Never assume that BETWEEN sorts or limits the affected rows.
BETWEEN in HAVING and CASE
HAVING can filter groups whose aggregate falls within a band.
SELECT customer_id, SUM(total_amount) AS lifetime_value
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) BETWEEN 5000.00 AND 20000.00;
CASE can classify values by bands, but overlapping inclusive boundaries can make later branches unreachable. Order branches carefully or use nonoverlapping half-open ranges.
BETWEEN in CHECK Constraints
CHECK can enforce a closed valid range.
CHECK (percentage BETWEEN 0.00 AND 100.00)
CHECK commonly accepts UNKNOWN, so add NOT NULL when percentage must be present. Choose DECIMAL precision that represents the allowed values exactly and test storage rounding at the boundaries.
BETWEEN in Range Joins
A range join maps a value to a band.
SELECT e.employee_id, b.band_name
FROM employees AS e
JOIN salary_bands AS b
ON e.salary BETWEEN b.minimum_salary AND b.maximum_salary;
Closed adjacent ranges can overlap at shared boundaries, matching one employee to two bands. Continuous band tables are usually safer with lower-inclusive, upper-exclusive conditions.
Designing Nonoverlapping Range Tables
JOIN salary_bands AS b
ON e.salary >= b.minimum_salary
AND e.salary < b.maximum_salary
Each boundary belongs to exactly one band. Define how the final upper end is represented and enforce no gaps or overlaps through controlled maintenance, exclusion constraints where available, or validation procedures.
BETWEEN with AND and OR
BETWEEN can be combined with other predicates. Parenthesize multiple range alternatives so global rules apply to every branch.
WHERE tenant_id = :tenant_id
AND status = 'ACTIVE'
AND (
score BETWEEN 0 AND 20
OR score BETWEEN 80 AND 100
)
Without grouping, the second range could escape tenant or status scope because AND has higher precedence than OR.
NOT BETWEEN
NOT BETWEEN selects known values outside the inclusive range.
WHERE score NOT BETWEEN 40 AND 80
-- Equivalent for known values:
WHERE score < 40 OR score > 80
Scores exactly 40 and 80 are inside BETWEEN and therefore excluded by NOT BETWEEN. NULL remains UNKNOWN. Include it explicitly only if missing values belong to the outside category.
BETWEEN versus IN, LIKE, and Equality
BETWEEN describes an ordered interval. IN describes membership in a discrete set. LIKE performs text pattern matching. Equality matches one exact value.
Do not use BETWEEN for a list such as statuses A, C, and F; it can include intermediate ordered values. Select the predicate that matches the business shape rather than choosing by concise syntax.
BETWEEN Does Not Sort Results
BETWEEN filters rows but does not control output order. Add ORDER BY when order matters.
SELECT transaction_id, amount
FROM transactions
WHERE amount BETWEEN :minimum_amount AND :maximum_amount
ORDER BY amount, transaction_id;
The unique tie-breaker makes pagination and repeated output deterministic.
BETWEEN Does Not Guarantee Continuous Data
A range predicate does not create missing dates, sequence numbers, or score values. If no record exists for a date inside the interval, no placeholder row appears. Calendar tables or generated series are needed for gap reporting.
Likewise, BETWEEN does not validate all stored values; it only filters the current query. Use constraints for durable integrity and separate data-quality queries for existing violations.
BETWEEN and Floating-Point Values
Approximate floating-point storage can place a calculated value slightly above or below a decimal boundary. BETWEEN then follows the stored binary value, not the displayed rounded value.
Use DECIMAL for exact business thresholds such as money and percentages. For scientific measurements, define tolerance and measurement uncertainty rather than assuming displayed equality at endpoints.
Implicit Conversion and Correct Types
Comparing a numeric column with text parameters or a timestamp with untyped date strings can trigger errors, product-specific conversion, or inefficient plans. Bind values using the column type and validate input before SQL execution.
Transform parameters to the storage convention rather than casting the indexed column for every row. This preserves clarity and often supports index range access.
BETWEEN and Indexes
BETWEEN is generally sargable when used directly on an indexed column with compatible types. An ordered index can seek to the lower boundary and scan through the upper boundary.
WHERE tenant_id = :tenant_id
AND created_at BETWEEN :start_time AND :end_time
A composite index beginning with tenant_id and followed by created_at may support this equality-plus-range pattern. Index usefulness still depends on selectivity, result size, sorting, and table statistics.
Functions Can Prevent Efficient Range Access
-- Often less index-friendly.
WHERE CAST(created_at AS DATE)
BETWEEN :start_date AND :end_date
-- Prefer typed timestamp boundaries.
WHERE created_at >= :start_timestamp
AND created_at < :exclusive_end_timestamp
Expression indexes can support intentional function-based queries in some products, but direct range predicates are usually simpler. Confirm behavior with actual execution plans.
Range Selectivity and Query Plans
A narrow range can be highly selective; a range covering most rows may make a scan cheaper than an index lookup. BETWEEN is not automatically faster than equivalent comparisons. Optimizers usually normalize them to similar internal predicates.
Inspect estimated and actual rows, reads, sorting, and parameter sensitivity. A plan suitable for one-day history may perform poorly for a ten-year export.
BETWEEN and Partition Pruning
Direct boundaries on a partition key can allow irrelevant partitions to be skipped. Match query boundaries to partition definitions and use compatible types.
For monthly timestamp partitions, a half-open interval aligned to month starts is clearer than an inclusive end timestamp. Verify pruning in the execution plan and test records exactly at partition boundaries.
Portability Across SQL Database Products
The central meaning of BETWEEN is portable across MySQL, PostgreSQL, SQL Server, Oracle Database, and SQLite: the lower and upper endpoints are included. Portability problems usually come from the data types around the predicate rather than from the keyword itself. Date literals, timestamp precision, implicit casts, collations, Boolean expressions, and time-zone types differ between products.
Use parameters instead of embedding product-sensitive text literals whenever an application supplies the bounds. If a script must contain literals, write them in an unambiguous form supported by the target database and test them under the deployment session settings. A string that one product converts automatically may be rejected or interpreted differently by another.
-- The predicate is portable; parameter binding handles type details.
WHERE invoice_total BETWEEN :minimum_total AND :maximum_total
Some databases offer additional range types or operators that model containment and overlap more directly. Those features can be valuable, but they are not interchangeable with ordinary BETWEEN. Record the chosen SQL dialect and boundary convention in repository documentation so a later migration does not silently change behavior.
BETWEEN in Reports and Dashboards
Reporting interfaces commonly expose a From and To control, which makes BETWEEN look like an automatic implementation choice. First identify whether users select calendar dates, exact instants, accounting periods, or numeric thresholds. A date report may reasonably include both selected dates, while an event report usually needs a start instant and an exclusive end instant.
Translate display values into database values in one application layer. For example, a user selecting November 1 through November 7 can be converted to the half-open interval beginning at November 1 midnight and ending at November 8 midnight in the reporting time zone. This prevents every query from inventing its own end-of-day rule.
WHERE occurred_at >= :report_start
AND occurred_at < :report_end_exclusive
AND account_id = :account_id
Show the applied time zone and range interpretation in exported reports. The SQL can be correct while the report remains misleading if readers assume a different zone or believe that an exclusive timestamp is an inclusive displayed date.
Designing API Range Filters
APIs often expose query parameters such as minPrice, maxPrice, createdFrom, and createdTo. The contract should state whether each endpoint is inclusive, what format and time zone are accepted, whether either bound is optional, and what happens when the lower value exceeds the upper value. Do not leave these decisions to the SQL implementation.
Validate and normalize the request before constructing the database command. Reject malformed, impossible, excessively broad, or unauthorized ranges with a clear client error. Bind normalized values as parameters, then append only the predicates justified by supplied fields.
-- Both optional bounds were supplied and validated.
WHERE price >= :minimum_price
AND price <= :maximum_price
-- A timestamp API defines "to" as exclusive.
WHERE created_at >= :created_from
AND created_at < :created_to
Return the normalized range in response metadata when practical. That makes pagination, caching, audit logs, and defect reproduction easier because clients can see the interval the server actually applied.
BETWEEN with Pagination and Stable Ordering
A range predicate limits eligible rows, but pagination still needs deterministic ordering. If many rows share the ordered column, add a unique tiebreaker. Without one, rows can move between pages or appear twice as the optimizer chooses a different physical order.
SELECT transaction_id, posted_at, amount
FROM transaction
WHERE posted_at >= :period_start
AND posted_at < :period_end
ORDER BY posted_at, transaction_id
FETCH FIRST 100 ROWS ONLY;
For large or changing result sets, keyset pagination is often more stable than a growing offset. The continuation condition begins after the last ordered pair while the original range remains in force. This is another reason to distinguish filtering from ordering: BETWEEN defines membership, and ORDER BY plus the continuation key defines traversal.
Safe Batch Processing with Ranges
Archiving, cleanup, migration, and reconciliation jobs frequently process identifiers or timestamps in ranges. Large inclusive batches can overlap accidentally when one job ends at the same value where the next begins. Half-open batches avoid that duplication: one batch handles values greater than or equal to its start and less than its end.
-- Batch 1
WHERE record_id >= 100000 AND record_id < 110000
-- Batch 2 begins exactly where Batch 1 stops.
WHERE record_id >= 110000 AND record_id < 120000
Persist the batch boundaries, row count, completion state, and retry identifier. Make the operation idempotent where possible, and commit at a size the database can recover and replicate safely. Before a destructive job, run the same predicate as a SELECT, compare the count with expectations, and verify that mandatory business and tenant filters are present.
Generated Values and Functional Range Searches
Sometimes users search a derived concept such as order year, normalized score, or case-insensitive code. Applying a function to every stored value can make an otherwise simple range expensive. A generated column or expression index may provide a deliberate searchable representation when that query is frequent and the database supports it.
Do not create derived storage merely to avoid understanding the original type. Timestamp searches should normally use timestamp boundaries, and monetary searches should use exact numeric columns. Consider generated values only when the derived domain is itself a stable business attribute with clear maintenance and indexing benefits.
-- Prefer a direct timestamp interval for an occasional year filter.
WHERE created_at >= :year_start
AND created_at < :next_year_start
Compare plans and maintenance cost before adding an index. Every additional index can speed reads but adds storage, write work, and operational complexity.
Troubleshooting Unexpected BETWEEN Results
When a range returns too many or too few rows, inspect the actual stored values and types before rewriting the predicate. Display timestamp precision, time-zone conversion, trailing spaces, collation, decimal scale, and NULL values. A formatted client grid may hide the fraction or offset that explains the mismatch.
Run diagnostic queries for each boundary separately. Count values below, equal to, inside, equal to the upper endpoint, above, and NULL. Compare the parameter types reported by the driver with the column definition. Then inspect the execution plan only after confirming that the result semantics are correct.
SELECT
SUM(CASE WHEN measured_value < :low THEN 1 ELSE 0 END) AS below_range,
SUM(CASE WHEN measured_value = :low THEN 1 ELSE 0 END) AS at_lower,
SUM(CASE WHEN measured_value BETWEEN :low AND :high THEN 1 ELSE 0 END) AS in_range,
SUM(CASE WHEN measured_value = :high THEN 1 ELSE 0 END) AS at_upper,
SUM(CASE WHEN measured_value IS NULL THEN 1 ELSE 0 END) AS missing
FROM measurement;
This staged approach separates data-quality, conversion, boundary, and performance problems. It also produces evidence that can be added to a defect report instead of relying on a screenshot of an apparently missing row.
Real-World Range Examples
-- Customer age band represented as a stored integer.
WHERE age_years BETWEEN 18 AND 64
-- Risk score requiring manual review.
WHERE risk_score BETWEEN 60 AND 79
-- Inventory quantity in a reorder band.
WHERE available_quantity BETWEEN 0 AND reorder_level
-- Calendar dates in an inclusive reporting period.
WHERE settlement_date BETWEEN :first_date AND :last_date
-- Timestamp events in one half-open month.
WHERE event_time >= :month_start
AND event_time < :next_month_start
Each example requires correct types, domain boundaries, NULL policy, and tenant or authorization scope where applicable.
Testing Numeric Ranges
For every closed range, test below the lower bound, exactly the lower bound, just above it, a middle value, just below the upper bound, exactly the upper bound, and above it. Include NULL and storage rounding.
For integer ranges, adjacent values are clear. For decimals, use the column's scale. For floating-point measurements, test represented values and documented tolerances rather than only formatted output.
Testing Date and Timestamp Ranges
Test the instant before start, exactly at start, inside, exactly at the intended end, and immediately after. For half-open intervals, the next period's start must be excluded from the current period and included in the next.
Include time-zone conversion, daylight-saving transitions, fractional seconds, and session settings. Test DATE and TIMESTAMP columns separately because their boundary semantics differ.
Testing Range Tables
Range tables require tests for every shared boundary, gap, overlap, first value, and last value. A value exactly at the boundary between two bands should match exactly one row under half-open design.
Use data-quality queries to detect pairs of overlapping rows and validate new bands within the same transaction or controlled maintenance process. Correct query syntax cannot compensate for inconsistent range data.
Common BETWEEN Mistakes
- Forgetting that both boundaries are included.
- Using a DATE end boundary against a TIMESTAMP column for a full day.
- Guessing an end-of-day time such as 23:59:59.
- Reversing lower and upper bounds or assuming automatic swapping.
- Treating NULL parameters as automatic open boundaries.
- Comparing numeric or date information stored as text.
- Assuming text ranges follow simple alphabetic intuition.
- Creating overlapping or gapped closed range tables.
- Using BETWEEN for a discrete list of exact values.
- Assuming BETWEEN sorts output or guarantees continuous data.
- Wrapping an indexed range column in a function unnecessarily.
- Running a broad UPDATE or DELETE range without preview and recovery.
BETWEEN Review Checklist
- Confirm that a closed, inclusive range matches the requirement.
- Use explicit comparisons for exclusive or mixed boundaries.
- Bind lower and upper values using the column's actual type.
- Validate boundary order, scale, time zone, and permitted domain.
- Define how NULL values and missing bounds should behave.
- Use half-open intervals for timestamp periods and adjacent bands.
- Keep tenant, ownership, and visibility predicates mandatory.
- Preview and count range-based mutations before execution.
- Avoid unnecessary functions on indexed range columns.
- Test every endpoint, adjacent value, NULL, and conversion case.
- Check range-table gaps and overlaps.
- Inspect actual plans under narrow and broad parameter ranges.
Interview Questions and Answers
What does BETWEEN do? It tests whether a value lies within an inclusive lower and upper boundary.
Is BETWEEN inclusive? Yes, both endpoints are included.
What is its equivalent form? x >= lower AND x <= upper for comparable known values.
Why avoid BETWEEN for a full-day timestamp filter? An inclusive date or midnight end can miss later timestamps; a half-open next-day boundary is safer.
What happens with reversed bounds? Most systems do not swap them; ordinary values usually do not match.
Does BETWEEN include NULL? No. If any relevant expression is NULL, the result is generally UNKNOWN.
What is NOT BETWEEN? For known values, it selects values below the lower endpoint or above the upper endpoint.
Can BETWEEN use an index? Yes, direct type-compatible range predicates commonly support index range access.
Does BETWEEN sort results? No. ORDER BY is required.
Why use half-open ranges? Adjacent ranges meet without overlap and timestamp windows avoid precision-dependent end values.
Key Takeaway
BETWEEN is a concise inclusive range predicate. It works well for true closed numeric and date intervals, but it should not hide boundary decisions. Timestamp periods, adjacent bands, nullable endpoints, text collations, and destructive range changes require more explicit design.
RELIABLE RANGE = CORRECT DATA TYPE
+ ORDERED BOUNDARIES
+ EXPLICIT INCLUSIVENESS
+ DEFINED NULL POLICY
+ HALF-OPEN TIME WINDOWS
+ MANDATORY SECURITY SCOPE
+ INDEX-AWARE PREDICATES
+ COMPLETE BOUNDARY TESTS
Use BETWEEN when both endpoints genuinely belong to the range. Otherwise use direct comparisons that state the required boundary semantics precisely.