Home > Articles > Data > Oracle

An Oracle Programmatic and General Reference

  • Print
  • + Share This
Need to look up the Oracle operators, functions, format models, reserved words, and privileges? This handy set of charts, from the Oracle DBA SQL Quick Reference, will help you find what you need.
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

  • + Share This
  • 🔖 Save To Your Account