Oracle DML Operators

An operator given a NULL value other than the concatenation operator (||) has a result of NULL. There are a number of types of operators. Operator precedence is the order of evaluation. In general precedence is evaluated from left to right. Parentheses (( ... )) will override precedence. Parentheses contents are always evaluated first. More specifically precedence is governed as in the table shown below.

* and /

+ and -

Binary (=) and concatenation (||)

All comparison operators including NOT, AND and OR

Unary Operators

Unary operators operate on a single operand.

Operator operand

Binary Operators

Binary operators operate on two operands. There are also some special operators for operations on more than two operators.

Operator operand, operand

Set Operators

UNION - all non-duplicated results from the union of two queries.
UNION ALL - all results from the union of two queries regardless of duplication.
INTERSECT - common results only from two queries.
MINUS - non-common results only from two queries.

Comparison Operators

The AND logical operator and the equals (=) operators are the most frequently used.

= - equality.
!= - inequality.
~= - inequality.
<> - inequality.
> - greater than.
< - less than.
>= - greater than or equal to.
<= - less than or equal to.
IN - in a set.
NOT IN - not in a set.
ANY SOME - compares a value to each value in a list returned from a query, FALSE if no rows returned.
ALL - a value must relate to the entire list or the subquery as indicated.
[NOT] BETWEEN x AND y - inclusion between x and y inclusive of both.
EXISTS TRUE - subquery returns atleast a single row.
x[NOT] LIKE y [ESCAPE 'z'] TRUE - x matching the pattern y. y can contain wildcard characters % and _. Any non-wildcard character can follow ESCAPE to allow for matching of restricted characters such as wildcard characters.
```
Like 'G%VIN' matches 'G ... VIN'.

% = matches 0 or more characters.
_ - matches exactly one character.
```
IS [NOT] NULL - tests for NULL values.