An Oracle Programmatic and General Reference
This chapter is from the book
This chapter is from the book
This chapter is from the book
Oracle supports a number of built in operators that fall into basic categories—simple arithmetic operators, comparison operators, logical operators, and operators that are used in select statements. In Oracle 9, support for User Defined Operators was also added.
Arithmetic Operators
Table 1-1. Arithmetic Operators
|
Operator |
What it does |
|---|---|
|
+ (unary) – (unary) |
Specifies a positive number or expression Specifies a negative number or expression |
|
+ (binary) - (binary) |
Addition Subtraction |
|
* / |
Multiplication Division |
Logical Operators
Table 1-2. Logical Operators
|
Operator |
What it does |
|---|---|
|
|| |
Concatenates two character (string) values |
|
NOT |
Reverses the meaning of another logical expression's result |
|
OR |
Logical OR—True if any are true, false else |
|
AND |
Logical AND—True if all are true, else false |
Comparison Operators
Table 1-3. Comparison Operators
|
Operator |
What it does |
|---|---|
|
= |
true if two expressions are equal |
|
!= ^= -= <> |
logically equivalent—true if two expressions are not equal |
|
> |
True if left expression is greater than right expression |
|
>= |
True if left expression is greater than or equal to right expression |
|
< |
True if left expression is less than right expression |
|
<= |
True if left expression is less than or equal to right expression |
|
IN |
Is equal to any member of a set or subquery |
|
NOT IN |
Does NOT equal any member of a set or subquery |
|
ANY, SOME |
True if one or more of the values in the list of expressions or subquery satisfies the condition |
|
ALL |
True if all of the values in the list of expressions or subquery satisfies the condition |
|
BETWEEN x AND y |
True if greater than or equal to x and less than or equal to y (can be reversed in meaning with NOT) |
|
EXISTS |
True if the subquery returns at least one row (can be reversed in meaning with NOT) |
|
LIKE pattern [ESCAPE 'c'] |
'True if expression or subquery matches pattern. '%' matches any sequence of characters, '_' matches any single character. If ESCAPE is used, the character 'c' causes the character following to be taken literally (can be reversed in meaning with NOT). |
|
IS NULL |
TRUE if the value is NULL (can be reversed in meaning with NOT) |
Select Operators
Also called SET operators
Table 1-4. Select Operators (Sets)
|
Operator |
What it does |
|---|---|
|
UNION |
This combines the results of two queries and returns the set of distinct rows returned by either query |
|
UNION ALL |
This combines the results of two queries and returns all rows returned by either query, including duplicates |
|
INTERSECT |
This combines the results of two queries and returns the set of distinct rows returned by both queries |
|
MINUS |
This combines the results of two queries and returns the distinct rows that were in the first query, but not in the second |
Table 1-5. Other Select Operators
|
Operator |
What it does |
|---|---|
|
(+) |
Denotes that the preceding column is an outer join |
|
* |
Wildcard operator. Equals all columns in a select statement |
|
PRIOR |
Denotes a parent-child relationship in a tree-structured query |
|
ALL |
Include all duplicate rows in a query (the default) |
|
DISTINCT |
Eliminate duplicates in a result set |
Precedence
Oracle evaluates expressions based on the order of precedence. Parentheses () override normal precedence. Lines are evaluated left to right for operators of equal precedence if there are no parentheses to override that order.
SQL Operator Precedence
|
+ – |
Unary arithmetic operators |
PRIOR operator |
|
* / |
Arithmetic operators |
|
|
+ – |
Binary arithmetic operators |
|| character operator |
All comparison operators
|
NOT |
Logical operator |
|
AND |
Logical operator |
|
OR |
Logical operator |
Arithmetic Operator Precedence
|
+ – |
Unary |
|
* / |
|
|
+ – |
Binary |