Overview

Conditions are used to determine data values. A condition consists of one or multiple expressions and logical (Boolean) operators and returns TRUE, FALSE, or UNKNOWN.

You can use conditions in the WHERE clause of the following SQL statements:

• DELETE

• SELECT

• UPDATE

You can use conditions in the following clauses of the SELECT statement:

• WHERE

• START WITH

• CONNECT BY

• HAVING

A condition can be known as a logical data type. For example, the result of the simple condition 1 = 1 is TRUE. The following example adds the values of salary and comm_pct (the NVL() function replaces the NULLs in salary with 0), and determines whether the sum of the values is greater than 2500.

NVL(salary, 0) + NVL(salary + (salary * comm_pct, 0) > 2500)

A logical condition, such as AND, can combine multiple conditions into one.

(1 = 1) AND (5 < 7)

The following are examples of valid conditions in an SQL statement:

name = 'ALICE'
emp.deptno = dept.deptno
hire_date > '01-JAN-22'
job_id IN ('SA_MAN', 'SA_REP')
salary BETWEEN 5000 AND 8000
comm_pct IS NULL AND salary = 10000

The current version of OceanBase Database supports the following types of conditions:

• Comparison conditions

• Floating-point conditions

• Logical conditions

• Pattern-matching conditions

• NULL conditions

• Compound conditions

• BETWEEN condition

• EXISTS condition

• IN condition