Overview of expressions

An expression is used to evaluate a value. It consists of one or more numbers, operators, and SQL functions.

An expression typically adopts the data type of its components. For example, 2*2 is a simple composite expression that evaluates to 4.

An expression can be used in multiple locations in an SQL statement, such as in the ORDER BY or HAVING clause of a SELECT statement, in the WHERE clause of a SELECT, DELETE, or UPDATE statement, or in a SET statement. You can use values from multiple sources to write an expression. These sources include literals, column values, NULL, variables, and built-in functions and operators.

Here is an example:

obclient> CREATE TABLE t1 (a INT);
obclient> INSERT INTO t1 VALUES (1), (2), (0);

obclient> SELECT ABS(a + 1)
  FROM t1
 WHERE a > 0;

In the preceding example, the parameters are described as follows:

• a is a column reference.
• 0 and 1 are constants.
• > and + are operators.
• ABS is a function.

For the complete syntax hierarchy, see Syntax of expressions.

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

• Simple expression
• Composite expression
• Conditional expression
• List expression
• Date and time expression
• Function expression
• Interval expression
• Scalar subquery expression
• Expression list