Scalar expressions are functions that calculate a single (scalar) value, even if executed on an entire column. They can be stored as a row value, as opposed to a table value which is a result set consisting of more than one column and/or row.
A scalar expression can be any one of these:
A column reference is a column name, alias, or ordinal.
For example, in
SELECT name FROM users, the column reference refers to the column titled
Column names may be aliased. For example in
SELECT name as "First name" from users, the column reference is the alias
"First name", which is quoted to maintain the case and use of space.
A column may also be referneced using an ordinal, for example in a
GROUP BY or
ORDER BY. In this query
SELECT AVG(Salary),Team FROM nba GROUP BY 2, the ordinal
2 refers to the second column in the select list,
Operators, frequently used for comparison, usually come in three forms:
A prefix or postfix to an expression or literals. For example,
-, which is used to negate numbers.
prefix_unary_operator ::= + | - | NOT postfix_unary_operator ::= IS NULL | IS NOT NULL
Two expressions or literals separated by an operator. For example,
+ which is used to add two numbers.
binary_operator ::= . | + | ^ | * | / | % | + | - | >= | <= | != | <> | || | LIKE | NOT LIKE | RLIKE | NOT RLIKE | < | > | = | OR | AND
Special operators for set membership¶
special_operator ::= value_expr IN ( value_expr [, ... ] ) | value_expr NOT IN ( value_expr [, ... ] ) | value_expr BETWEEN value_expr AND value_expr | value_expr NOT BETWEEN value_expr AND value_expr
These operators return TRUE if the
value_expr on the left matches the expression on the right for set membership or if the value is in-range.
The data type of the left
value_expr must match the type of the right side
Binary operators are frequently used to compare values.
Comparison operators (
IS NOT NULL always returns
These operators are:
||Smaller than or equal to|
||Greater than or equal to|
||Not equal to|
||Not identical to|
NULL values are handled differently than other value expressions:
NULLis always smaller than anything, including another
NULLis never equal to anything, including another
=). To check if a value is null, use
The table below lists the operators in decreasing order of precedence.
Use parentheses to avoid ambiguous situations when using binary operators.
The NOT variations, such as
NOT RLIKE have the same precedence as their non-