All Products
Search
Document Center

Overview of SQL conditions

Last Updated: Jun 18, 2021

Conditions are used to determine data values and return TRUE, FALSE, or UNKNOWN. A condition is a combination of one or more components, such as expressions and logical (Boolean) operators. You must use valid syntax for conditions in SQL statements.

Notice

The NLS_COMP and NLS_SORT parameters jointly affect the sorting and comparison of characters. If the NLS_COMP parameter in the database is set to LINGUISTIC, all the entities that are described in this Developer Guide follow the rules that are specified by the NLS_SORT parameter. If the NLS_COMP parameter is not set to LINGUISTIC, the functions are not affected by NLS_SORT. You can directly specify the value of NLS_SORT. If you do not specify it, it inherits the value of NLS_LANGUAGE1.

You can use conditions in the WHERE clauses of these statements:

  • DELETE
  • SELECT
  • UPDATE

You can use conditions in these clauses of the SELECT statements:

  • WHERE
  • START WITH
  • CONNECT BY
  • HAVING

A condition can be called a logical data type, although ApsaraDB for OceanBase does not officially support such a data type.

For example, the simple condition 1 = 1 returns the result of TRUE.

The following more complex condition adds the value of salary to the value of commission_pct and checks whether the sum is greater than 25,000. The NVL function replaces NULL values in salary with 0.

NVL(salary, 0) + NVL(salary + (salary * commission_pct, 0) > 25000)

The logical AND condition can combine multiple conditions into a single condition.

(1 = 1) AND (5 < 7)

The following conditions are valid in SQL statements:

name = 'SMITH'
employees.department_id = departments.department_id
hire_date > '01-JAN-08'
job_id IN ('SA_MAN', 'SA_REP')
salary BETWEEN 5000 AND 10000
commission_pct IS NULL AND salary = 2100

Condition precedence

Condition precedence indicates the order in which ApsaraDB for OceanBase checks different conditions in the same expression. When an expression that contains multiple conditions is computed, the conditions that have higher precedence are checked first, and then the conditions that have lower precedence. The conditions that have equal precedence are checked from left to right. For example, multiple conditions that are connected by AND and OR cannot be checked from left to right. The AND condition is computed first, and then the OR condition.

List of SQL condition precedence

Condition type

Functionality

=, ! =, <, >, <=, >=

Comparison

IS [NOT] NULL, LIKE, [NOT] BETWEEN, [NOT] IN, EXISTS, and IS OF

Comparison

NOT

Exponentiation and logical negation

AND

Conjunction

OR

Disjunction

The levels of precedence are listed from high to low. Conditions that are listed on the same line have the same precedence.