SQL supports many types of comparisons. Comparisons start with the basic pattern:
<field> <operator> <value>
Valid operators change depending on the value you are comparing:
Some comparisons require an extended syntax, with examples below. These include IN, BETWEEN, and NULL.
Here are comparisons used in the Advanced Search Tool and their corresponding operators/values in SQL:
| 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' |
| 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. Supports the NOT keyword as in "NOT IN". | 1, 3, 5 | 1, 3, 5 | IN (1,3,5) |
| BETWEEN | Looks in a range between two values (inclusive). Supports the NOT keyword as in "NOT BETWEEN" | 4 to 6 | 4, 5, 6 | BETWEEN 4 AND 6 |
| NULL | Looks for empty fields. Supports the NOT keyword as in "IS NOT NULL" | Will return records where the field is empty | IS 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.
See also: Beginning SQL
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."
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
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 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'
See also: Beginning SQL
Wildcards use the LIKE keyword with a wildcard (%) because they are not exact matches.
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
See also: Beginning SQL
Some comparisons are more complex and require multiple values (Search Terms).
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'
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
