Arithmetic operators

Arithmetic operators are used to perform four arithmetic operations. Arithmetic operators have four binary operators for addition, subtraction, multiplication, and division and two unary operators for expressing positive and negative numbers.

String operators

The only string operator is the concatenation operator (||) for concatenating two strings

The following example shows the concatenation of two strings, 'ABC' and 'DEF':

'ABC' || 'DEF' = 'ABCDEF'

Set operator

Set operators are related to the results of two queries and are not performed directly on a table. These operators always receive the result of a SELECT statement as an operand and return one result table that consists of zero or more rows.

The following table describes the set operators provided by Tibero.

When using arithmetic and string operators, verify that the result is within the expected range. For example, if an operation that results in the value 1234.0 is stored in a column declared as the NUMBER(3,0) type, or the string 'ABCDEF' is stored in a column declared as the VARCHAR(5) type, an error will occur.

Complex operations for arithmetic and string data can be performed using built-in functions. The built-in functions provided in Tibero are described in “Chapter 4. Functions”.

Logical operators

Logical operators always receive a conditional expression as an operand. The logical operators provided in Tibero are the NOT, the AND, and the OR operators. The NOT operator is a unary operator, and the AND and OR operators are binary operators.

Logical operators provided by Tibero differ from general logic operators. These logical operators can return UNKNOWN as well as TRUE and FALSE as a result.

The followings are the truth tables for the NOT, AND, and OR operators when the operands have a value of TRUE, FALSE, or UNKNOWN. The commutative rule is valid for the AND and OR operations.

Comparison operators

Comparison operators receive arithmetic values, strings, lists, etc as operands. Comparison operations are divided into two types: operators for comparing two simple values and operators for comparing a simple value with a list. The next table displays arithmetic comparison operators used between simple values, provided in Tibero.

It is possible to compare a single value with a list using arithmetic operations combined with the operator ANY, SOME, or ALL (> ALL, = SOME, etc.), IN, etc. The meaning and syntax of conditional expressions that include comparison operators are described in “3.4. Conditional Expressions”.

Operator precedence

Several operators can be used in one expression. For this, there is a fixed order for deciding which operator will be evaluated first. This order is called operator precedence.

The following table shows the precedence of operators provided by Tibero. An operator with a lower precedence rating is evaluated earlier than an operator with a higher precedence rating.

Arithmetic operators (+, -, *, and /) and logical operators (AND and OR) can be used multiple times in a single expression. If multiple operators have the same precedence, they are evaluated in left-to-right order. Comparison operators and unary operators cannot be used more than once in one expression. Operators in parentheses are evaluated before those outside of parentheses. If nested parentheses are used, the most deeply nested operator is calculated first. To specify the operation order clearly, using parentheses is recommended.

Syntax

Component

Simple expressions are not case-sensitive for a schema, table, view, column name, or NULL, but they are case-sensitive for a string enclosed in single quotation marks (' ').

Syntax

Component

Syntax

Component

Example

The following example illustrates the use of the CASE expression:

SELECT CASE WHEN age > 19 THEN 'adult' ELSE 'minor' END FROM people;

In a GROUP BY clause of a SELECT statement

As the default value of a column

In a conditional expression of a CHECK constraint

Syntax

Component

Syntax

Component

Example

The following illustrates the use of lists:

EMPNO, ENAME, ADDR
(35, 'John', 'Houston')
(20, 30, 50)

Syntax

Component

Syntax

Component

Example

The following example illustrates the use of group conditions:

EMPNO = ANY (35, 54, 27, 69)
SALARY * 1.05 >= ALL (SELECT SALARY FROM EMP WHERE DEPTNO = 5)

On the first line, if the value of EMPNO is the same as least one value in the list, the expression will return true and will otherwise return false. On the second line, if the value of SALARY multiplied by 1.05 is greater than or equal to every value in the result of the subquery, the expression will return true and will otherwise return false.

If NULL is included in a right-side list, any group condition that includes the ANY operator will always return TRUE or UNKNOWN, and any group condition that includes the ALL operator will always return FALSE or UNKNOWN.

For example, the following two lines return the same result:

DEPTNO != ALL (4, 5, NULL)
(DEPTNO != 4) AND (DEPTNO != 5) AND (DEPTNO != NULL)

On the second line, if the value of column DEPTNO is 4 or 5, the result of the last simple condition that compares the value with NULL will be UNKNOWN, and since the entire condition is connected by AND operators, the result will also be UNKNOWN.

Syntax

Component

Syntax

Component

Example

A BETWEEN condition is equivalent to two arithmetic conditions connected with the AND operator.

For example, the following two lines return the same result:

SALARY BETWEEN 30000 AND 50000
(SALARY >= 30000) AND (SALARY <= 50000)

Syntax

Component

Syntax

Component

Example

An IN condition can be changed to a group condition using the operators '= ANY' or '= SOME'.

For example, the following two lines return the same result:

EMPNO IN (35, 54, 27, 69)
EMPNO = ANY (35, 54, 27, 69)

As an IN condition can be changed to a group condition using '= ANY', if the right-side list includes NULL, the IN condition always returns TRUE or UNKNOWN. Likewise, the NOT IN condition always returns FALSE or UNKNOWN.

Syntax

Component

Syntax

Component

While the equality (=) operator is used to determine if two strings are exactly the same, the LIKE operator can be used to determine if a string contains an arbitrary character and string and wildcard characters corresponding to the string.

Wildcard characters have the following characteristics:

Syntax

Component

Example

The following illustrates the use of the REGEXP_LIKE condition:

SQL> SELECT 1 FROM DUAL WHERE REGEXP_LIKE('12345', '3.?4');

         1
----------
         1

1 row selected.