Filtering with Precision

Comparison operators

RevOpsQL supports two forms of operators, interchangeable based on your preference:

Both forms are strictly equivalent. You can write:

WHERE amount > 5000
-- or
WHERE amount GREATER THAN 5000

Tip: The keyword form (GREATER THAN, AT LEAST…) is more readable when sharing a query with a non-technical colleague.

BETWEEN: within a range

To filter on a range of values:

SELECT dealname, amount
FROM deal
WHERE amount BETWEEN 5000 AND 50000

Equivalent to amount >= 5000 AND amount <= 50000, but more readable.

IN and NOT_IN: within a list of values

To filter on multiple possible values:

SELECT email, firstname
FROM contact
WHERE lifecyclestage IN ('customer', 'lead', 'opportunity')

The inverse, to exclude values:

SELECT dealname, dealstage
FROM deal
WHERE dealstage NOT_IN ('closedwon', 'closedlost')

Tip: IN ('a', 'b', 'c') is much more readable than = 'a' OR = 'b' OR = 'c'. Use it whenever you have more than two possible values.

HAS_PROPERTY and NOT_HAS_PROPERTY: property presence

To filter objects where a property is filled in:

SELECT email, firstname
FROM contact
WHERE email HAS_PROPERTY
  AND phone HAS_PROPERTY

To filter objects where a property is not filled in:

SELECT email, firstname
FROM contact
WHERE company NOT_HAS_PROPERTY

CONTAINS_TOKEN: text search

To search for a word in a text property:

SELECT dealname, amount
FROM deal
WHERE dealname CONTAINS_TOKEN 'renew'

Returns all deals whose name contains the word "renew". NOT_CONTAINS_TOKEN excludes matches.