Snowflake
Filter Data Within Range

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_bound and upper_bound are included in the check. This is equivalent to writing expr >= lower_bound AND expr <= upper_bound.
  • Boundary Order: Ensure that the lower_bound is less than the upper_bound; otherwise, the condition will always return FALSE.

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_idnameage
1John Doe28
2Jane Smith34
3Alice Johnson19
4Dave Wilson22
5Clara Oswald31

Snowflake SQL Query


_10
SELECT name, age
_10
FROM customers
_10
WHERE age BETWEEN 18 AND 25;

Output

customer_idnameage
3Alice Johnson19
4Dave Wilson22
Y42 SQL completion user interface

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Start for free