Overview of SQL conditions

A condition is a logical expression that evaluates to TRUE, FALSE, or UNKNOWN. It consists of one or more expressions and logical (Boolean) operators.

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

• DELETE

• SELECT

• UPDATE

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

• WHERE

• START WITH

• CONNECT BY

• HAVING

Conditions can be considered as logical data types. For example, the simple condition 1 = 1 evaluates to TRUE. Another example is when you add the values of salary and comm_pct (the function NVL() replaces NULL values in salary with 0) and check if the sum is greater than 2500.

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

Logical conditions can combine multiple conditions into a single condition. For example, the logical condition AND can combine multiple conditions into one.

(1 = 1) AND (5 < 7)

Here are some valid conditions in SQL statements:

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

OceanBase Database currently supports the following types of conditions:

• Comparison conditions

• Floating-point conditions

• Logical conditions

• Pattern-matching conditions

• Empty conditions

• Composite conditions

• BETWEEN conditions

• EXISTS conditions

• IN conditions