Overview of SQL conditions

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.

Notice

The NLS_COMP and NLS_SORT parameters jointly affect the sorting and comparison of characters. If you specify LINGUISTIC for NLS_COMP in the database, all entities mentioned in this development guide will follow the rule specified by NLS_SORT. If the value of NLS_COMP is not LINGUISTIC, functions will not be affected by NLS_SORT. You can directly specify a value for NLS_SORT. If no value is specified for NLS_SORT, it will inherit the value of NLS_LANGUAGE.

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 null values 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)

Example 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