Operators

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