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.

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

Numeric Example

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

SELECT price, origin, dest
FROM FlightsData
WHERE price BETWEEN 300 and 600
ORDER BY price ASC

Aggregate and Window Functions Example

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%.

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

Date Example with TIMESTAMP

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

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

Date Example with EXTRACT

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).

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

Asymmetric and Symmetric Example

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.

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

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

SELECT price, origin, dest
FROM FlightsData
WHERE price ASYMMETRIC BETWEEN 600 and 300
ORDER 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.

SELECT price, origin, dest
FROM FlightsData
WHERE price SYMMETRIC BETWEEN 600 and 300
ORDER 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.

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