The BETWEEN operator in Snowflake is a conditional operator used to filter rows where a value falls within a specified inclusive range. It is a shorthand for value >= lower AND value <= upper and works with numeric, date, timestamp, and string data types.
What Does BETWEEN Do in Snowflake?
In Snowflake SQL, BETWEEN is not a function but a comparison operator. It evaluates whether a given expression lies between two boundary values, including both boundaries. For example, WHERE salary BETWEEN 50000 AND 70000 returns rows where salary is 50000, 70000, or any value in between. This inclusive behavior is a key difference from some other languages where boundaries might be exclusive.
Snowflake supports BETWEEN in WHERE clauses, CASE expressions, and JOIN conditions. It is also valid in QUALIFY clauses when used with window functions.
BETWEEN Syntax and Basic Examples
The syntax is straightforward:
SELECT column1, column2
FROM table_name
WHERE column1 BETWEEN lower_bound AND upper_bound;
Here are concrete examples across data types:
- Numeric:
SELECT * FROM orders WHERE total_amount BETWEEN 100 AND 200;– returns orders with total from 100 to 200 inclusive. - Date:
SELECT * FROM sales WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';– includes all of 2023. - Timestamp:
SELECT * FROM events WHERE event_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59';– note the explicit time to capture the full day. - String:
SELECT * FROM products WHERE product_code BETWEEN 'A001' AND 'A100';– compares lexicographically, so ‘A050’ is included, but ‘A1000’ is not because it is longer and ‘A100’ is a prefix.
How BETWEEN Handles NULLs and Edge Cases
If the tested value is NULL, the BETWEEN condition evaluates to NULL (not TRUE), so the row is excluded from results. Similarly, if either boundary is NULL, the whole condition is NULL and the row is not returned. This is consistent with standard SQL three-valued logic.
For strings, comparison is based on the collation in use. By default, Snowflake uses binary collation (UTF-8 byte order), which is case-sensitive. So 'apple' BETWEEN 'Apple' AND 'banana' would be false because ‘apple’ sorts after ‘Apple’ in binary order. To do case-insensitive comparisons, you can use LOWER(column) BETWEEN LOWER('A') AND LOWER('Z') or set a collation on the column.
BETWEEN vs. >= AND <=: Is There a Difference?
Functionally, BETWEEN is identical to value >= lower AND value <= upper. There is no performance difference in Snowflake; the query optimizer treats them the same. However, using BETWEEN can make the query more readable and less error-prone, especially when the boundaries are long expressions.
One subtle point: if you need to exclude one boundary, you must use explicit comparisons. For example, to include only values greater than 10 and less than 20, you write WHERE value > 10 AND value < 20 – BETWEEN cannot express that directly.
Common Mistakes and How to Avoid Them
Here are frequent pitfalls when using BETWEEN in Snowflake:
- Reversed boundaries: If the lower bound is greater than the upper bound, the condition is always false. Snowflake does not swap them; it evaluates as written.
- Date/time boundary confusion: For timestamps, using a date literal like
'2023-01-01'is interpreted as'2023-01-01 00:00:00'. To include the entire day, you must specify the end time as'2023-01-01 23:59:59'or use< '2023-01-02'. - String length issues: Lexicographic comparison means that ‘A10’ is less than ‘A9’ because ‘1’ < ‘9’. Be careful with codes that have varying lengths; consider padding or using numeric casts.
- NULL boundaries: If a boundary column contains NULL, the condition will not match any rows. Use
COALESCEif you need a default.
Using BETWEEN with Dates and Timestamps Effectively
When working with date ranges, the most common mistake is missing rows from the last day. For a date column, BETWEEN '2023-01-01' AND '2023-01-31' works perfectly because dates have no time component. For a timestamp column, the same condition would exclude everything after midnight on Jan 31. Instead, use:
SELECT * FROM events
WHERE event_time >= '2023-01-01'
AND event_time < '2023-02-01';
This pattern is safe and also allows index usage (if Snowflake uses micro-partitions, it filters efficiently). Alternatively, you can use event_time BETWEEN '2023-01-01' AND '2023-01-31 23:59:59.999', but the less-than-next-day pattern is cleaner and avoids missing leap-second or millisecond edge cases.
BETWEEN in JOINs and CASE Statements
BETWEEN is not limited to WHERE. You can use it in a CASE expression to categorize data:
SELECT order_id, total_amount,
CASE
WHEN total_amount BETWEEN 0 AND 100 THEN 'Low'
WHEN total_amount BETWEEN 101 AND 500 THEN 'Medium'
ELSE 'High'
END AS order_size
FROM orders;
It can also be used in a join condition to match a value to a range in another table:
SELECT e.employee_id, g.grade
FROM employees e
JOIN salary_grades g
ON e.salary BETWEEN g.min_salary AND g.max_salary;
This is a common pattern for bucketing or lookup tables.
Frequently Asked Questions
Is BETWEEN inclusive of the boundaries in Snowflake?
Yes, BETWEEN is inclusive of both the lower and upper bounds. For example, WHERE age BETWEEN 18 AND 65 includes ages 18 and 65.
Can I use BETWEEN with strings in Snowflake?
Yes, strings are compared lexicographically based on the column’s collation. By default, it is case-sensitive binary comparison. Use LOWER() or set a case-insensitive collation if needed.
Does BETWEEN work with NULL values?
No, if the tested value or either boundary is NULL, the condition evaluates to NULL, which is treated as false in a WHERE clause. The row will not be returned.
What is the difference between BETWEEN and IN in Snowflake?
BETWEEN checks if a value falls within a continuous range. IN checks if a value matches any value from a discrete list. For example, WHERE id BETWEEN 1 AND 5 matches 1,2,3,4,5. WHERE id IN (1,3,5) matches only 1,3,5.
Can I use BETWEEN in a subquery or CTE?
Yes, BETWEEN can be used anywhere a conditional expression is allowed, including in subqueries and CTEs. It is just a comparison operator.
How do I exclude the upper bound in a BETWEEN condition?
You cannot exclude a bound with BETWEEN. Instead, write explicit comparisons: WHERE value >= lower AND value < upper. This is common for date ranges.
Performance Considerations and Best Practices
In Snowflake, BETWEEN is not a performance bottleneck; the query optimizer uses micro-partition pruning to skip data blocks that do not match the range. To get the best performance, ensure the column is used in a filter and is of a comparable type. Avoid wrapping the column in a function (e.g., WHERE DATE(ts) BETWEEN ...) because that prevents pruning. Instead, use direct column comparisons as shown above.
For large date ranges, consider using BETWEEN with dates rather than timestamps, and always use explicit time boundaries when dealing with timestamps to avoid missing data at the end of the day.
Related
- Arduino String Data Type: How It Works and When to Use It
- Snowflake SPLIT: Turn a String Into an Array
- C++ String Data Type: How std::string Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Filter Dates Within a Range: Steps and FixesTo filter Excel dates within a range, select a cell in your data, choose Data > Filter, open…
- Excel Text Filter Options: A Practical GuideExcel’s text filters let you show rows whose text matches a condition: choose options such as Equals, Begins…
- Excel Conditional Formatting Formula Rules ExplainedIn Excel, a conditional-formatting formula rule applies a format when a formula evaluates to TRUE. On Windows, select…
- How to Add IFERROR in Excel to Handle Formula ErrorsTo add IFERROR in Excel, wrap your existing formula inside IFERROR and specify what to show if that…
- Excel Find and Replace Paragraph Mark: The Right MethodIn Excel for Microsoft 365 on Windows, press Ctrl+H, click the Find what box, and press Ctrl+J to…
- Jersey Inventory Counting Spreadsheet: Setup and FormulasTo make a jersey inventory counting spreadsheet in Excel, give each distinct jersey variant its own row and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.