SQL Comparisons

SQL supports many types of comparisons. All comparisons share the same basic pattern:

<field> <operator> <value>

Different Operators For Different Types of Values

Valid operators change depending on value you are comparing:

  • The operator for NULL is different than the operators for numbers or text
  • The operators for a list or range is different than the operator for a single value
  • The operator for exact text is different than text with a wildcard

Overview

Here are comparisons used in the Advanced Search Tool and their corresponding operators/values in SQL:

Text Comparisons and Operators

Comparison Description Example Term Example Result Operator/Value
Exact Match Returns only the exact search term John John (but not Johnny or Jonathan) = 'John'
Begins With Looks at the beginning of the text field Jo John, Johnny, Jonathan, Joanna LIKE 'Jo%'
Ends With Looks at the end of the text field com .com email addresses but not comcast@sbcglobal.net LIKE '%com'
Contains Looks anywhere in the field to find a match Group Small Group, Group Life, Red Group, Group LIKE '%Group%'
IN Looks in a supplied list for exact matches Member, Attendee Member, Attendee IN ('Member','Attendee')
BETWEEN Looks in a range between two values (inclusive) J to K Jansen, Johnson, Jylon (but not Kane, because it's after K alphabetically) BETWEEN 'J' AND 'K'
NULL Looks for empty fields   Will return records where the field is empty IS NULL
NOT NULL Looks for fields with values     IS NOT NULL
Not Equal Looks for anything but exact match John Jonnny, Jonathan, Jerry <> 'John'

Number Comparisons

Comparison Description Example Term Example Result Operator/Value
= Equals 4 4 = 4
> Greater Than 4 5, 6, 7, (and up) > 4
< Less Than 4 0, 1, 2, 3 (and negative values if the field supports them) < 4
>= Great Than or Equal To 4 4, 5, 6, (and up) >= 4
<= Less Than or Equal To 4 0, 1, 2, 3, 4 (and negative values if the field supports them) <= 4
<> Not Equal To 4 0, 1, 2, 3, 5 <> 4
IN Looks in a supplied list for exact matches 1, 3, 5 1, 3, 5 IN (1,3,5)
BETWEEN Looks in a range between two values (inclusive) 4 to 6 4, 5, 6 BETWEEN 4 AND 6
NULL Looks for empty fields   Will return records where the field is empty IS NULL
NOT NULL Looks for fields with values   Will return records where the field is not empty IS NOT NULL

The Advanced Search Tool provides options for creating many types of filters. Each filter is a Comparison which further limits the number of resulting records.

Basic Comparisons

See also: Beginning SQL

Comparisons Without Search Terms

Some comparisons check for values or missing values using the NULL keyword. NULL represents a missing value and NOT NULL represents an existing value.

The valid operators when comparing NULL are "IS" and "IS NOT." 

Note: Values are never equal to NULL (not even NULL is equal to NULL). This means comparisons to NULL can not use equality operators and comparisons which do use equality operators will always drop out the NULL values. A helpful SQL Function often used to deal with this limitation of NULL is the ISNULL Function.

The following query returns records with birth dates:

The comparison in the resulting SQL looks like this:

Date_of_Birth IS NOT NULL

The following query returns records without birth dates:

The comparison in the resulting SQL looks like this:

Date_of_Birth IS NULL

Single Value Comparisons

The majority of filter types are single value comparisons. Each of these require a single Search Term.

 

These can be thought of as mathematical comparisons, even for text and dates. Any piece of text may be compared to another using these comparisons because the text is treated like a string of numbers in which A < B < C and so on. Dates are also treated as numbers internally.

Text Comparisons

Text values are expressed in single-quotes. If you omit the quotes, the Advanced Search Tool with add them for you.

Text comparisons support exact matches or wildcards so you can match when text is "like" a search term or only when it is exact.

Exact Match

The Exact Match comparison uses equals:

Display_Name = 'Smith, John'
Wildcard Searches

See also: Beginning SQL

Wildcards use the LIKE keyword with a wildcard (%) because they are not exact matches.

Note: If you use equals rather than LIKE, you will only get exact matches (the system will match the percent symbol rather than use it as a wildcard).
Contains

The Contains Wildcard search uses wildcards at the beginning and end. This example would return "Johnson, Kate" and "Smith, John"

Display_Name LIKE '%John%'
Starts With and Ends With

Starts With uses a wildcard at the end. The name "Johnson, Kate" would be returned, but not "Smith, John"

Display_Name LIKE 'John%'

End With uses a wildcard at the beginning:

Display_Name LIKE '%John'

It would return "Smith, John" but not "Smith, Johnny"

Like

Like allows you to enter your own wildcard combination. It is useful for more complex comparisons, such as:

Email_Address LIKE john%@%.com

Complex Comparisons

See also: Beginning SQL

Multiple Value Comparisons

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

BETWEEN (With Range)

Between requires two values and matches anything between and including the two values. This may be used 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 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.

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 under comparison.

__Age NOT IN (18, 19, 20)


For more on the SQL generated by these comparisons, see SQL Queries - Beginning

Tags: Diagram

Last Modified: 2/4/2019

Did this article help?
× Thank You for the Feedback