Oracle Certification - SQL Fundamentals I - SQL Operators
SQL Operator Precedence
Operators in SQL statements follow an order of precedence as follows:
| Precedence | Symbol | Operation |
| Highest | () | Brackets |
| Medium | / | Division |
| Medium | * | Multiplication |
| Lowest | - | Subtraction |
| Lowest | + | Addition |
Operators of the same precedence are evaluated from left to right, and round brackets are used to enforce an non-standard operator precedence.
The double || symbol is the concatenation operator, and it joins character expressions or columns together to create a single larger expression:
There are two ways in which single quote marks can be included in string literal. Either by repetition of the single quote, or using the alternative quote operator. I've never found the need to use the alternative quote operator, so this will be useful to know in the exam:
The Q-quote is specified by providing a single quote enclosed unique character at the beginning of the string. To close the string, provide the same character followed by the single quote. The single and the provided character form the two character string enclosure.
If you decide to use braces, Oracle expects matching enclosures, i.e. Open brackets should be represented by closed brackets at the end of the string. Other characters can be represented as they are at both ends.
Comparison Operators
Comparison operators compare two values,and return a boolean value of true/false or null.
| Symbol | Operation | Returns |
| = | Equality | TRUE if the values on either side are equal |
| != <> ^= | Inequality | TRUE if the values on either side are NOT equal |
| < | Less Than | TRUE if the left value is less than the right |
| > | Greater Than | TRUE if the left value is greater than the right |
| <= | Less Than Equal | TRUE if the left value is less than or equal the right |
| >= | Greater Than Equal | TRUE if the left value is greater than or equal the right |
| ALL | All Operator | Can be combined with the above against a list of values |
| ANY SOME | ANY/SOME Operator | Can be combined with the above against a list of values |
Logical Operators
Logical operators NOT, OR and AND can be used to combine the results of two comparison operations using boolean logic. When a logical operator is applied to a NULL value to result is UNKNOWN. UNKNOWN differs from FALSE in that NOT UNKNOWN is UNKNOWN, but NOT FALSE is TRUE.
Other Operators
IN / NOT IN
IN is used to test a value for equality against a list of values. TRUE is returned if an equality is found in the list of values. The list of values can be a subquery.
Note: When using NOT IN with a NULL any occurance of NULL on either side will result in FALSE being returned. i.e. SYM NOT IN ('Alpha','Beta',NULL) will expand to SYM != 'Alpha' AND SYM != 'Beta' AND SYM != NULL, and any comparison on NULL results in NULL.
BETWEEN
BETWEEN A AND B tests for a range of values that are >= A and <= B.
Note: FIRST_NAME BETWEEN 'A' AND 'B' tests for FIRST_NAME >= 'A' AND FIRST_NAME <= 'B', which means 'Adam', 'Ayumi' & 'B' will pass the test but 'Batty' will not.
Not much to say on this topic, so on to the next The WHERE Clause.