BETWEEN Operator

Use BETWEEN to check whether values fall within a given range. BETWEEN accepts numeric, string, and date data types, and can be used with aggregate, window, and math functions.

BETWEEN takes this syntax.

1<EXPR> BETWEEN (SYMMETRIC | ASYMMETRIC) <LOWER_BOUND> AND <UPPER_BOUND>

This example filters results on flights whose prices are between $300 and $600.

1SELECT price, origin, dest
2FROM FlightsData
3WHERE price BETWEEN 300 and 600
4ORDER BY price ASC

This example checks whether the sum of profits for each account is less than 5% of the total sum of profits for all accounts. It returns the account name, account ID, and IsLowPercent, a boolean value that is true if the sum of profits is less than 5%.

1SELECT Account_ID, Account_Name, SUM(Profit) BETWEEN 0 AND 5 * SUM(SUM(Profit)) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) / 100 AS IsLowPercent
2FROM "Opportunity"
3GROUP BY Account_ID, Account_Name
4LIMIT 10;

This example returns ten CloseDate values that are between two TIMESTAMP values, 2014-12-31 and 2015-12-31.

1SELECT CloseDate
2FROM "Opportunity"
3WHERE CloseDate BETWEEN TIMESTAMP '2014-12-31 00:00:00' AND TIMESTAMP '2015-12-31 00:00:00'
4LIMIT 10;

This example uses SQL for CRM Analytics’s EXTRACT function to access the MONTH values from the CloseDate field and return dates between April (4) and July (7).

1SELECT CloseDate
2FROM "Opportunity"
3WHERE EXTRACT(MONTH FROM CloseDate) BETWEEN 4 AND 7
4LIMIT 10;

By default, the BETWEEN statement evaluates a value that falls between the lower and upper bounds in order of least to greatest as true. If the lower and upper bound values are reversed, BETWEEN evaluates the statement as false. To specify this behavior, use the ASYMMETRIC operator.

Let’s go back to the numeric example.

Here, the range is specified as ASYMMETRIC BETWEEN 300 and 600. The statement evaluates to true.

1SELECT price, origin, dest
2FROM FlightsData
3WHERE price ASYMMETRIC BETWEEN 300 and 600
4ORDER BY price ASC

When the range is specified as ASYMMETRIC BETWEEN 600 and 300, the statement evaluates to false.

1SELECT price, origin, dest
2FROM FlightsData
3WHERE price ASYMMETRIC BETWEEN 600 and 300
4ORDER BY price ASC

By using the SYMMETRIC operator, the order of the upper and lower bounds of your BETWEEN statement remain true if reversed. The order is irrelevant.

1SELECT price, origin, dest
2FROM FlightsData
3WHERE price SYMMETRIC BETWEEN 600 and 300
4ORDER BY price ASC

By using SYMMETRIC, the BETWEEN statement evaluates to true.

Here’s the date example using EXTRACT and the SYMMETRIC operator. By including SYMMETRIC, the query returns the same results, even though the months are filtered BETWEEN 7 AND 4.

1SELECT CloseDate
2FROM "Opportunity"
3WHERE EXTRACT(MONTH FROM CloseDate) SYMMETRIC BETWEEN 7 AND 4
4LIMIT 10;

j

Introducing SQL for CRM Analytics

Quick Start: Run Your First Query

SQL vs SAQL

SQL Statements

SELECT Clause

FROM Clause

WHERE Clause

Boolean Expressions in SELECT Clause

GROUP BY Clause

GROUP BY ROLLUP

GROUPING()

HAVING Clause

ORDER BY Clause

LIKE Clause

BETWEEN Operator

LIMIT Clause

FETCH Clause

OFFSET Clause

CASE Statements

COALESCE()

NULLIF()

UNION Operator

Subquery

Window Functions

Date Functions

Aggregate Functions

Math Functions

String Functions and Operators

Release Notes

Previous Versions