Snowflake BETWEEN: Filter Data Within a Specific Range
The BETWEEN condition is like a gatekeeper in SQL that checks whether a value falls within a certain range. It’s used to filter data based on whether a value (which can be a number, date, or text) lies in a specified interval between two boundaries.
What it does?
BETWEEN helps you determine if a value is inside a specified lower and upper limit. If the value is within the range, the condition returns TRUE; otherwise, it returns FALSE.
Syntax
Here’s how you can use the BETWEEN condition in a SQL query:
_10<expr> [ NOT ] BETWEEN <lower_bound> AND <upper_bound>
where:
<expr>: The value or column you want to check.<lower_bound>and<upper_bound>: The minimum and maximum values of the range.NOT BETWEEN: Optionally, you can use NOT to check if the expression is outside the range.
Important notes
- Inclusive Range: The BETWEEN condition is inclusive, meaning the boundaries
lower_boundandupper_boundare included in the check. This is equivalent to writingexpr >= lower_bound AND expr <= upper_bound. - Boundary Order: Ensure that the
lower_boundis less than theupper_bound; otherwise, the condition will always returnFALSE.
Examples
Filtering Data using Between
Imagine you have a table called customers with information about customers, including their ages, and you want to find all customers whose ages are between 18 and 25.
Input
Table: customers
| customer_id | name | age |
|---|---|---|
| 1 | John Doe | 28 |
| 2 | Jane Smith | 34 |
| 3 | Alice Johnson | 19 |
| 4 | Dave Wilson | 22 |
| 5 | Clara Oswald | 31 |
Snowflake SQL Query
_10SELECT name, age_10FROM customers_10WHERE age BETWEEN 18 AND 25;
Output
| customer_id | name | age |
|---|---|---|
| 3 | Alice Johnson | 19 |
| 4 | Dave Wilson | 22 |
