Syntax for many statements and expressions includes the term Expression, or a term for a specific kind of expression such as TableSubquery. Expressions are allowed in these specified places within statements.
Some locations allow only a specific type of expression or one with a specific property. If not otherwise specified, an expression is permitted anywhere the word Expression appears in the syntax. This includes:
Of course, many other statements include these elements as building blocks, and so allow expressions as part of these elements.
The following tables list all the possible SQL expressions and indicate where the expressions are allowed.
General expressions are expressions that might result in a value of any type. The following table lists the types of general expressions.
|Column reference||A column-Name that references the value of the column made visible to the expression containing the Column reference.
You must qualify the column-Name by the table name or correlation name if it is ambiguous.
The qualifier of a column-Name must be the correlation name, if a correlation name is given to a table that is in a
|Constant||Most built-in data types typically have constants associated with them (as shown in the Data types section).|
|Dynamic parameter||A dynamic parameter is a parameter to an SQL statement for which the value is not specified when the statement is created. Instead, the statement has a question mark (?) as a placeholder for each dynamic parameter. See Dynamic parameters.
Dynamic parameters are permitted only in prepared statements. You must specify values for them before the prepared statement is executed. The values specified must match the types expected.
Allowed anywhere in an expression where the data type can be easily deduced. See Dynamic parameters.
||Allows you to specify the type of NULL or of a dynamic parameter or convert a value to another type. See
|Scalar subquery||Subquery that returns a single row with a single column. See ScalarSubquery.|
|Table subquerry||Subquery that returns more than one column and more than one row. See TableSubquery.
Allowed as a tableExpression in a FROM clause and with EXISTS, IN, and quantified comparisons.
|Conditional expression||A conditional expression chooses an expression to evaluate based on a boolean test. Conditional expressions include the
Boolean expressions are expressions that result in boolean values. Most general expressions can result in boolean values. Boolean expressions commonly used in a WHERE clause are made of operands operated on by SQL operators.
Numeric expressions are expressions that result in numeric values. Most of the general expressions can result in numeric values. Numeric values have one of the following types:
- DOUBLE PRECISION
The following table lists the types of numeric expressions.
Evaluate the expected math operation on the operands. If both operands are the same type, the result type is not promoted, so the division operator on integers results in an integer that is the truncation of the actual numeric result. When types are mixed, they are promoted as described in the Data types section.
Character expressions are expressions that result in a
VARCHAR value. Most general expressions can result in a
VARCHAR value. The following table lists the types of character
|Concatenation expression||In a concatenation expression, the concatenation operator,
|Built-in string functions||The built-in string functions act on a String and return a string. See
Date and Time Expressions
A date or time expression results in a
value. Most of the general expressions can result in a date or time
value. The following table lists the types of date and time expressions.
||Returns the current date. See the
||Returns the current time. See the
||Returns the current timestamp. See the