Overview of expressions

An expression is used to calculate the value of data. 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 Expression syntax.

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

• Simple expression
• Composite expression
• Conditional expression
• List expression
• DateTime expression
• Function expression
• Interval expression
• Scalar subquery expression
• Expression list