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.
Text Comparisons & Operators
Here are comparisons used in the Advanced Search Tool and their corresponding operators and values in SQL:
| Comparison | Description | Example Term | Example Result | Operator/Value |
|---|---|---|---|---|
| Exact Match | Returns ony 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 | Look 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 |
| > = | Greater 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 to create many types of filters. Each filter is a comparison that further limits the number of resulting records.