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 expressionsGeneral expressions are expressions
that might result in a value of any type.
Table of general expressions
Expression Type
Explanation
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 FROM
clause. The table name is no longer visible as a column-Name 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.
Boolean expressionsBoolean 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. See .
Numeric expressionsNumeric 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
Table 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 . 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.
SUM
Returns the sum of a set of numeric values.
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 expressionsCharacter expressions are
expressions that result in a CHAR or VARCHAR value. Most general expressions
can result in a CHAR or VARCHAR value.
Table
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 .
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 expressionsA 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.
Table
of date and time 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 .