By default, the between operator is inclusive. This means that it will include the start and end values of the provided range.
Let us explore how to use the between operator in SQL.
SQL Between Operator
One common use of the between operator is in conjunction with the where clause. This allows you to filter for results that match the specified range.
FROM TABLE_NAME
WHERE column_name BETWEEN start_expression AND end_expression
The above query allows you to filter for results that match the range within the specified column.
For example, assume that we have a table as shown below:
Let us use the between operator to get the results where the price is between 50 and 1000. We can run a code as shown below:
*
FROM
sample.inventory
WHERE
price BETWEEN 50 AND 1000;
We select all the columns from the inventory table in the sample database in the query. We then use the where clause to filter the prices between 50 and 1000.
This should return a result:
SQL Between Dates
Another common use of the between operator is filtering for matching records on a specific date range.
Consider the example shown below:
*
FROM
sample.inventory
WHERE
price BETWEEN '2021-01-01'
AND '2021-12-31';
The above query filters for records that are between January 1st 2021 to December 31st 2021.
Final Thoughts
In this article, the between operator allows you to filter for results that match within a date or numerical range. It also provides testing whether an expression falls within a set range.
