SQL expressions 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. SQL expressionscolumn reference SQL expressionsconstantSQL expressionsNULLSQL expressionsdynamic parameterSQL expressionsCAST SQL expressionsscalar subquery SQL expressionstable subquery SQL expressionsconditionalSQL expressionsnumericSQL expressionscharacter SQL expressionsstring with wildcards SQL expressionsconcatenation function SQL expressionsCURRENT_DATE function SQL expressionsCURRENT_TIME function SQL expressionsCURRENT_TIMESTAMP function SQL expressionsmathematical (+ -, *, /, unary + and - )SQL expressionsLENGTH function expressionscolumn reference expressionsconstantexpressionsNULL expressionsdynamic parameter expressionsCASTexpressionsscalar subqueryexpressionstable subquery expressionsconditionalexpressionsnumeric expressionscharacterexpressionsstring with wildcardsexpressionsconcatenation functionexpressionsCURRENT_DATE functionexpressionsCURRENT_TIME functionexpressionsCURRENT_TIMESTAMP functionexpressionsmathematical (+ -, *, /, unary + and - )expressionsLENGTH function

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:

  • ORDER BY clause
  • selectExpression
  • UPDATE statement (SET portion)
  • VALUES expression
  • WHERE clause

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

General expressions are expressions that might result in a value of any type. The following table lists the types of general expressions.

General expressionsThis table lists and describes the types of general SQL 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 columnName by the table name or correlation name if it is ambiguous.

The qualifier of a columnName must be the correlation name, if a correlation name is given to a table that is in a FROM clause. The table name is no longer visible as a columnName qualifier once it has been aliased by a correlation name.

Allowed in selectExpressions, UPDATE statements, and the WHERE clauses of data manipulation statements.

 Constant Most built-in data types typically have constants associated with them (as shown in ). 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 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 .

 CAST expression Lets you 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 . Table subquery 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 CASE expression, the NULLIF function, and the COALESCE function.
Boolean expressions

Boolean expressions are expressions that result in boolean values. Most general expressions can result in boolean values. See for more information and a table of operators.

Numeric expressions

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.

Numeric expressionsThis table lists and describes the types of numeric SQL 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 .

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 . SUM Returns the sum of a set of numeric values. See . LENGTH Returns the number of characters in a character or bit string. See . LOWER See . COUNT Returns the count of a set of values. See , .
Character expressions

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.

Character expressionsThis table lists and describes the types of character SQL 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 . Built-in string functions The built-in string functions act on a String and return a string. See , , , , , and . USER functions User functions return information about the current user as a String. See , , and .
Date and time expressions

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.

Date and time expressionsThis table lists and describes the types of date and time SQL expressions. Expression Type Explanation CURRENT_DATE Returns the current date. See . CURRENT_TIME Returns the current time. See . CURRENT_TIMESTAMP Returns the current timestamp. See .