SQL Comparisons

 

SQL supports many types of comparisons. Comparisons start with the basic pattern:

<field> <operator> <value>

Different Operators For Different Types of Values

Valid operators change depending on the 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

Some comparisons require an extended syntax, with examples below. These include IN, BETWEEN, and NULL.

Overview

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

Text Comparisons and Operators
ComparisonDescriptionExample TermExample ResultOperator/Value
Exact MatchReturns only the exact search termJohnJohn (but not Johnny or Jonathan)= 'John'
Begins WithLooks at the beginning of the text fieldJoJohn, Johnny, Jonathan, JoannaLIKE 'Jo%'
Ends WithLooks at the end of the text fieldcom.com email addresses but not comcast@sbcglobal.netLIKE '%com'
ContainsLooks anywhere in the field to find a matchGroupSmall Group, Group Life, Red Group, GroupLIKE '%Group%'
INLooks in a supplied list for exact matchesMember, AttendeeMember, AttendeeIN ('Member','Attendee')
BETWEENLooks in a range between two values (inclusive)J to KJansen, Johnson, Jylon (but not Kane, because it's after K alphabetically)BETWEEN 'J' AND 'K'
NULLLooks for empty fields Will return records where the field is emptyIS NULL
NOT NULLLooks for fields with values  IS NOT NULL
Not EqualLooks for anything but exact matchJohnJonnny, Jonathan, Jerry<> 'John'
Number Comparisons
ComparisonDescriptionExample TermExample ResultOperator/Value
=Equals44= 4
>Greater Than45, 6, 7, (and up)> 4
<Less Than40, 1, 2, 3 (and negative values if the field supports them)< 4
>=Great Than or Equal To44, 5, 6, (and up)>= 4
<=Less Than or Equal To40, 1, 2, 3, 4 (and negative values if the field supports them)<= 4
<>Not Equal To40, 1, 2, 3, 5<> 4
INLooks in a supplied list for exact matches. Supports the NOT keyword as in "NOT IN".1, 3, 51, 3, 5IN (1,3,5)
BETWEENLooks in a range between two values (inclusive). Supports the NOT keyword as in "NOT BETWEEN"4 to 64, 5, 6BETWEEN 4 AND 6
NULLLooks for empty fields. Supports the NOT keyword as in "IS NOT NULL" Will return records where the field is emptyIS NULL

The Advanced Search Tool provides options for creating many types of filters. Each filter is a Comparison that 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 date:

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 requires 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 will 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