Multiple Value Comparisons

Some comparisons are more complex and require multiple values (Search Terms).

BETWEEN

BETWEEN requires two values and matches anything between and including the two values. Use this for date ranges, alphabetical ranges, or number ranges. In this example, the query searches for Contacts with a birth date between January 1, 1994 and January 1, 1995.

The resulting SQL looks like this:

SELECT Contact_ID, Display_Name, Date_of_Birth  
FROM Contacts  
WHERE Date_of_Birth BETWEEN '1994-01-01' AND '1995-01-01'

IN

IN requires a list of values separate by commas. Each value in the list must be an exact match.

The resulting SQL looks like this:

SELECT Contact_ID, Display_Name 
FROM Contacts 
WHERE __Age IN (18, 19, 20)

BETWEEN (With Range)

BETWEEN requires two values and matches anything between and including the two values. Use this for date ranges, alphabetical ranges, or number ranges. Because the value in this type of comparison is a range, the AND keyword is used for the range with the BETWEEN operator.

The resulting SQL for this comparison is:

Date_of_Birth BETWEEN '1/1/1994' AND '1/1/1995'

For the opposite values (the dates outside of this range), you can add the NOT keyword. In the Advanced Search Tool, select this from the first drop-down list under comparison.

Date_of_Birth NOT BETWEEN '1/1/1994' AND '1/1/1995'

IN (With List)

The keyword IN requires a list of values separate by commas. Each value in the list must be an exact match. The operator, in this case, is IN and the value is a comma-delimited list of values in parentheses. You do not have to enter the parentheses in the Advanced Search Tool. The tool will do this for you. In this example, the query searches for Contacts that are 18, 19, and 20 years old.

The comparison in the resulting SQL looks like this:

__Age IN (18, 19, 20)

For the opposite result (ages not in the list), you can add the NOT keyword. In the Advanced Search Tool, select this from the first drop-down list under comparison.

__Age NOT IN (18, 19, 20)

For more on the SQL generated by these comparisons, see Beginner SQL.