Skip Headers

Oracle® Database Lite SQL Reference Release 10.3 Part Number E12092-02 |
Home | Book List | Contents | Index | Contact Us

Previous | Next
| View PDF

# 2 SQL Operators

This document discusses SQL operators used with Oracle Database Lite. Topics include:

- Section 2.1, "SQL Operators Overview"

- Section 2.2, "Arithmetic Operators"

- Section 2.3, "Character Operators"

- Section 2.4, "Comparison Operators"

- Section 2.5, "Logical Operators"

- Section 2.6, "Set Operators"

- Section 2.7, "Other Operators"

## 2.1 SQL Operators Overview

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.

### 2.1.1 Unary Operators

A unary operator uses only one operand. A unary operator typically appears with its operand in the following format.

    operator operand

### 2.1.2 Binary Operators

A binary operator uses two operands. A binary operator appears with its operands in the following format.

    operand1 operator operand2

### 2.1.3 Set Operators

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

### 2.1.4 Other Operators

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.

## 2.2 Arithmetic Operators

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 | `SELECT +3 FROM DUAL;`
- (unary) | Negates operand | `SELECT -4 FROM DUAL;`
/ | Division (numbers and dates) | `SELECT SAL / 10 FROM EMP;`
* | Multiplication | `SELECT SAL * 5 FROM EMP;`
+ | Addition (numbers and dates) | `SELECT SAL + 200 FROM EMP;`
- | Subtraction (numbers and dates) | `SELECT SAL - 100 FROM EMP;`

## 2.3 Character Operators

Character operators used in expressions to manipulate character strings are listed in Table 2-3.

Table 2-3 Character Operators

Operator | Description | Example
--- | --- | ---
|| | Concatenates character strings | `SELECT 'The Name of the employee is: ' || ENAME FROM EMP;`

### 2.3.1 Concatenating 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.

## 2.4 Comparison Operators

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. | `SELECT ENAME "Employee" FROM EMP WHERE SAL = 1500;`
!=, ^=, <> | Inequality test. | `SELECT ENAME FROM EMP WHERE SAL ^= 5000;`
> | Greater than test. | `SELECT ENAME "Employee", JOB "Title" FROM EMP WHERE SAL > 3000;`
< | Less than test. | `SELECT * FROM PRICE WHERE MINPRICE < 30;`
>= | Greater than or equal to test. | `SELECT * FROM PRICE WHERE MINPRICE >= 20;`
<= | Less than or equal to test. | `SELECT ENAME FROM EMP WHERE SAL <= 1500;`
IN | "Equivalent to any member of" test. Equivalent to "`=ANY`". | `SELECT * FROM EMP WHERE ENAME IN ('SMITH', 'WARD');`
ANY/ SOME | Compares a value to each value in a list or returned by a query. Must be preceded by `=, !=, >, <, <= or >=`. Evaluates to `FASLE` if the query returns no rows. | `SELECT * FROM DEPT WHERE LOC = SOME ('NEW YORK','DALLAS');`
NOT IN | Equivalent to "`!=ANY`". Evaluates to `FALSE` if any member of the set is `NULL`. | `SELECT * FROM DEPT WHERE LOC NOT IN ('NEW YORK', 'DALLAS');`
ALL | Compares a value with every value in a list or returned by a query. Must be preceded by `=, !=, >, <, <= or >=.` Evaluates to `TRUE` if the query returns no rows. | `SELECT * FROM emp WHERE sal >= ALL (1400, 3000);`
[NOT] BETWEEN x and y | [Not] greater than or equal to x and less than or equal to y. | `SELECT ENAME, JOB FROM EMP WHERE SAL BETWEEN 3000 AND 5000;`
EXISTS | `TRUE` if a sub-query returns at least one row. | `SELECT * FROM EMP WHERE EXISTS (SELECT ENAME FROM EMP WHERE MGR IS NULL);`
x [NOT] LIKE y [ESCAPE z] | `TRUE` if x does [not] match the pattern y. Within y, the character "%" matches any string of zero or more characters except null. The character "_" matches any single character. Any character following `ESCAPE` is interpreted literally, useful when y contains a percent (%) or underscore (_). | `SELECT * FROM EMP WHERE ENAME LIKE '%E%';`
IS [NOT] NULL | Tests for nulls. This is the only operator that should be used to test for nulls. | `SELECT * FROM EMP WHERE COMM IS NOT NULL AND SAL > 1500;`

## 2.5 Logical Operators

Logical operators which manipulate the results of conditions are listed in Table 2-5.

Table 2-5 Logical Operators

Operator | Description | Example
--- | --- | ---
NOT | Returns `TRUE` if the following condition is `FALSE`. Returns `FALSE` if it is `TRUE`. If it is `UNKNOWN`, it remains `UNKNOWN`. | `SELECT * FROM EMP WHERE NOT (job IS NULL)` `SELECT * FROM EMP WHERE NOT (sal BETWEEN 1000 AND 2000)`
AND | Returns `TRUE` if both component conditions are `TRUE`. Returns `FALSE` if either is `FALSE`; otherwise returns `UNKNOWN`. | `SELECT * FROM EMP WHERE job='CLERK' AND deptno=10`
OR | Returns `TRUE` if either component condition is `TRUE`. Returns `FALSE` if both are `FALSE`. Otherwise, returns `UNKNOWN`. | `SELECT * FROM emp WHERE job='CLERK' OR deptno=10`

## 2.6 Set Operators

Set operators which combine the results of two queries into a single result are listed in Table 2-6.

Table 2-6 Set Operators

Operator | Description | Example
--- | --- | ---
`UNION` | Returns all distinct rows selected by either query. | `SELECT * FROM` `(SELECT ENAME FROM EMP WHERE JOB = 'CLERK'` `UNION` `SELECT ENAME FROM EMP WHERE JOB = 'ANALYST');`
`UNION ALL` | Returns all rows selected by either query, including all duplicates. | `SELECT * FROM` `(SELECT SAL FROM EMP WHERE JOB = 'CLERK'` `UNION` `SELECT SAL FROM EMP WHERE JOB = 'ANALYST');`
`INTERSECT` and `INTERSECT ALL` | Returns all distinct rows selected by both queries. | `SELECT * FROM orders_list1` `INTERSECT` `SELECT * FROM orders_list2`
`MINUS` | Returns all distinct rows selected by the first query but not the second. | `SELECT * FROM (SELECT SAL FROM EMP WHERE JOB = 'PRESIDENT'` `MINUS` `SELECT SAL FROM EMP WHERE JOB = 'MANAGER');`

Note: :

The syntax for `INTERSECT ALL` is supported, but it returns the same results as `INTERSECT`.

## 2.7 Other Operators

Other operators used by Oracle Database Lite are listed in Table 2-7.

Table 2-7 Other Operators

Operator | Description | Example
--- | --- | ---
(+) | Indicates that the preceding column is the outer join column in a join. | `SELECT ENAME, DNAME FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO (+);`
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 `CONNECT BY` clause to define the relationship between the parent and child rows. | `SELECT EMPNO, ENAME, MGR FROM EMP CONNECT BY PRIOR EMPNO = MGR;`