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.

Expression Type | Explanation |
---|---|

Column reference | A columnName that
references the value of the column made visible to the expression containing
the Column reference. You must qualify the The qualifier
of a Allowed in |

Constant | Most built-in data types typically have constants associated with them (as shown in Data types). |

NULL | NULL is an untyped constant representing the unknown value. Allowed in CAST expressions or in INSERT VALUES lists and UPDATE SET clauses. Using it in a CAST expression gives it a specific data type. |

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. |

CAST expression | Lets you specify the type of NULL or of a dynamic parameter or convert a value to another type. See CAST function. |

Scalar subquery | Subquery that returns a single row with a single column.
See scalarSubquery. |

Table subquery | Subquery that returns more than one column and more than
one row. See tableSubquery.Allowed
as a |

Conditional expression | A conditional expression chooses an expression to evaluate based on a boolean test. Conditional expressions include the CASE expression, the NULLIF function, and the COALESCE function. |

Boolean expressions are expressions that result in boolean values. Most general expressions can result in boolean values. See Boolean expressions for more information and a table of 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:

- BIGINT
- DECIMAL
- DOUBLE PRECISION
- INTEGER
- REAL
- SMALLINT

The following table lists the types of numeric expressions.

Expression Type | Explanation |
---|---|

+, -, *, /, unary + and - 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 Data types. Unary + is a noop (i.e., +4 is the same as 4). Unary - is the same as multiplying the value by -1, effectively changing its sign. |

AVG | Returns the average of a set of numeric values. See AVG function. |

SUM | Returns the sum of a set of numeric values. See SUM function. |

LENGTH | Returns the number of characters in a character or bit string. See LENGTH function. |

LOWER | See LCASE or LOWER function. |

COUNT | Returns the count of a set of values. See COUNT function, COUNT(*) function. |

Character expressions are expressions that result in a CHAR or VARCHAR value. Most general expressions can result in a CHAR or VARCHAR value. The following table lists the types of character expressions.

Expression Type | Explanation |
---|---|

A CHAR or VARCHAR value that uses wildcards. | The wildcards % and _ make a character string a pattern against which the LIKE operator can look for a match. |

Concatenation expression | In a concatenation expression, the concatenation operator, "||", concatenates its right operand to the end of its left operand. Operates on character and bit strings. See Concatenation operator. |

Built-in string functions | The built-in string functions act on a String and return a string. See LTRIM function, LCASE or LOWER function, RTRIM function, TRIM function, SUBSTR function, and UCASE or UPPER function. |

USER functions | User functions return information about the current user as a String. See CURRENT_USER function, SESSION_USER function, and USER function. |

A date or time expression results in a DATE, TIME, or TIMESTAMP 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.

Expression Type | Explanation |
---|---|

CURRENT_DATE | Returns the current date. See CURRENT_DATE function. |

CURRENT_TIME | Returns the current time. See CURRENT_TIME function. |

CURRENT_TIMESTAMP | Returns the current timestamp. See CURRENT_TIMESTAMP function. |

**selectExpression**

A selectExpression is the basic SELECT-FROM-WHERE construct used to build a table value based on filtering and projecting values from other tables.**tableExpression**

A tableExpression specifies a table, view, or function in a FROM clause.**NEXT VALUE FOR expression**

The NEXT VALUE FOR expression retrieves the next value from a sequence generator.**VALUES expression**

The VALUES expression allows construction of a row or a table from other values.**Expression precedence**

Precedence of operations from highest to lowest is as follows.**Boolean expressions**

Boolean expressions are expressions that result in boolean values.**CASE expression**

The CASE expression can be used for conditional expressions in Derby.**Dynamic parameters**

You can prepare statements that are allowed to have parameters for which the value is not specified when the statement is prepared using PreparedStatement methods in the JDBC API. These parameters are called dynamic parameters and are represented by a question mark (?).