This document discusses SQL operators used with Oracle Database Lite. Topics include:
An operator manipulates individual data items and returns a result. The data items are called operands or arguments. Operators are represented by special characters or by keywords. For example, the multiplication operator is represented by an asterisk (*) and the operator that tests for nulls is represented by the keywords IS NULL
. There are two general classes of operators: unary and binary. Oracle Database Lite SQL also supports set operators.
A unary operator uses only one operand. A unary operator typically appears with its operand in the following format.
operator operand
A binary operator uses two operands. A binary operator appears with its operands in the following format.
operand1 operator operand2
Set operators combine sets of rows returned by queries, instead of individual data items. All set operators have equal precedence. Oracle Database Lite supports the following set operators.
UNION
UNION ALL
INTERSECT
MINUS
The levels of precedence among the Oracle Database Lite SQL operators from high to low are listed in Table 2-1. Operators listed on the same line have the same level of precedence.
Table 2-1 Levels of Precedence of the Oracle Database Lite SQL Operators
Precedence Level | SQL Operator |
---|---|
1 |
Unary + - arithmetic operators, PRIOR operator |
2 |
* / arithmetic operators |
3 |
Binary + - arithmetic operators, || character operators |
4 |
All comparison operators |
5 |
NOT logical operator |
6 |
AND logical operator |
7 |
OR logical operator |
Other operators with special formats accept more than two operands. If an operator receives a null operator, the result is always null. The only operator that does not follow this rule is CONCAT.
Arithmetic operators manipulate numeric operands. The '-' operator is also used in date arithmetic. Supported arithmetic operators are listed in Table 2-2.
Table 2-2 Arithmetic Operators
Operator | Description | Example |
---|---|---|
+ (unary) |
Makes operand positive |
|
- (unary) |
Negates operand |
|
/ |
Division (numbers and dates) |
|
* |
Multiplication |
|
+ |
Addition (numbers and dates) |
|
- |
Subtraction (numbers and dates) |
|
Character operators used in expressions to manipulate character strings are listed in Table 2-3.
Operator | Description | Example |
---|---|---|
|| |
Concatenates character strings |
|
With Oracle Database Lite, you can concatenate character strings with the following results.
Concatenating two character strings results in another character string.
Oracle Database Lite preserves trailing blanks in character strings by concatenation, regardless of the strings' datatypes.
Oracle Database Lite provides the CONCAT
character function as an alternative to the vertical bar operator. For example,
SELECT CONCAT (CONCAT (ENAME, ' is a '),job) FROM EMP WHERE SAL > 2000;
This returns the following output.
CONCAT(CONCAT(ENAME ------------------------- KING is a PRESIDENT BLAKE is a MANAGER CLARK is a MANAGER JONES is a MANAGER FORD is a ANALYST SCOTT is a ANALYST 6 rows selected.
Oracle Database Lite treats zero-length character strings as nulls. When you concatenate a zero-length character string with another operand the result is always the other operand. A null value can only result from the concatenation of two null strings.
Comparison operators used in conditions that compare one expression with another are listed in Table 2-4. The result of a comparison can be TRUE
, FALSE
, or UNKNOWN
.
Table 2-4 Comparison Operators
Operator | Description | Example |
---|---|---|
= |
Equality test. |
|
!=, ^=, <> |
Inequality test. |
|
> |
Greater than test. |
|
< |
Less than test. |
|
>= |
Greater than or equal to test. |
|
<= |
Less than or equal to test. |
|
IN |
"Equivalent to any member of" test. Equivalent to " |
|
ANY/ SOME |
Compares a value to each value in a list or returned by a query. Must be preceded by |
|
NOT IN |
Equivalent to " |
|
ALL |
Compares a value with every value in a list or returned by a query. Must be preceded by |
|
[NOT] BETWEEN x and y |
[Not] greater than or equal to x and less than or equal to y. |
|
EXISTS |
|
|
x [NOT] LIKE y [ESCAPE z] |
|
|
IS [NOT] NULL |
Tests for nulls. This is the only operator that should be used to test for nulls. |
|
Logical operators which manipulate the results of conditions are listed in Table 2-5.
Operator | Description | Example |
---|---|---|
NOT |
Returns |
|
AND |
Returns |
|
OR |
Returns |
|
Set operators which combine the results of two queries into a single result are listed in Table 2-6.
Operator | Description | Example |
---|---|---|
|
Returns all distinct rows selected by either query. |
|
|
Returns all rows selected by either query, including all duplicates. |
|
|
Returns all distinct rows selected by both queries. |
|
|
Returns all distinct rows selected by the first query but not the second. |
|
Note: :
The syntax forINTERSECT ALL
is supported, but it returns the same results as INTERSECT
.Other operators used by Oracle Database Lite are listed in Table 2-7.
Operator | Description | Example |
---|---|---|
(+) |
Indicates that the preceding column is the outer join column in a join. |
|
PRIOR |
Evaluates the following expression for the parent row of the current row in a hierarchical, or tree-structured query. In such a query, you must use this operator in the |
|