| Operator | Example Usage | Effect |
| =, >, <, >=,<=, <>, !=, !>, !< | <Column Name> = , <Other Column Name>, <Column Name> = ‘Bob’ | Standard Comparison Operators—these work as they do in pretty much any programming language with a couple of notable points: 1. What constitutes “greater than,” “less than,” and “equal to” can change depending on the collation order you have selected. For example, “ROMEY” = “romey” in places where case-insensitive sort order has been selected, but “ROMEY” < > “romey” in a case-sensitive situation. 2. != and <> both mean “not equal.” !< and !> mean “not less than” and “not greater than” respectively. |
| AND,OR, NOT | <Column1> = <Column2> AND <Column3> >= <Column 4> <Column1> != “MyLiteral” OR <Column2> = “MyOtherLiteral” | Standard Boolean logic. You can use these to combine multiple conditions into one WHERE clause. NOT is evaluated first, then AND, then OR. If you need to change the evaluation order, you can use parentheses. Note that XOR is not supported. |
| BETWEEN | <Column1> BETWEEN 1 AND 5 | Comparison is TRUE if the first value is between the second and third values inclusive. It is the functional equivalent of A>=B AND A<=C. Any of the specified values can be column names, variables, or literals. |
| LIKE | <Column1> LIKE “ROM%” | Uses the % and _ characters for wildcarding. % indicates a value of any length can replace the % character. _ indicates any one character can replace the _ character. Enclosing characters in [ ] symbols indicates any single character within the [ ] is OK ([ac] means a, b, and c are OK. [ab] indicates a or b are OK). ^ operates as a NOT operator—indicating that the next character is to be excluded. |
| IN | <Column1> IN (List of Numbers) <Column1> IN (“A”, “b”, “345”) | Returns TRUE if the value to the left of the IN keyword matches any of the values in the list provided after the IN keyword. This is frequently used in subqueries, |
| ALL, ANY, SOME | <column|expression> (comparision operator), <ANY|SOME> (subquery) | These return TRUE if any or all (depending on which you choose) values in a subquery meet the comparison operator (e.g. <, >, =, >=) condition. ALL indicates that the value must match all the values in the set. ANY and SOME are functional equivalents and will evaluate to TRUE if the expression matches any value in the set. |
| EXISTS | EXISTS (subquery) | Returns TRUE if at least one row is returned by the subquery. |
Tags:
Database