Scalar Expressions¶
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 one of the following:
Column References¶
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 name
.
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, Team
.
Operators¶
Operators are frequently used for comparison and usually come in one of the following forms:
Unary Operator¶
A unary operator is a prefix or postfix to an expression or literal, such as -
, which is used to negate numbers.
The following is the correct syntax for using a unary operator:
prefix_unary_operator ::=
+ | - | NOT
postfix_unary_operator ::=
IS NULL | IS NOT NULL
Binary Operator¶
A binary operator is two expressions or literals separated by an operator, such as +
, which is used to add two numbers.
The following is the correct syntax for using a binary operator:
binary_operator ::=
. | + | ^ | * | / | % | + | - | >= | <= | != | <> | ||
| LIKE | NOT LIKE | RLIKE | NOT RLIKE | < | > | = | OR | AND
Special Operators for Set Membership¶
The following is the correct syntax for special operators to 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.
Note
The data type of the left value_expr
must match the type of the right side value_expr
.
Comparing Values¶
Binary operators are frequently used to compare values.
The comparison operators in the following table always return BOOL
:
Operator |
Name |
---|---|
|
Smaller than. |
|
Smaller than or equal to. |
|
Greater than. |
|
Greater than or equal to. |
|
Equals. |
|
Not equal to. |
|
Identical to . |
|
Not identical to. |
Note
NULL
values are handled differently than other value expressions:
NULL
is always smaller than anything, including anotherNULL
.NULL
is never equal to anything, including anotherNULL
(=
). To check if a value is null, useIS NULL
Operator Precedence¶
The following table lists the operators in decreasing order of precedence:
Precedence |
Operator |
Associativity |
---|---|---|
Highest |
|
left |
|
||
|
left |
|
|
left |
|
|
left |
|
|
right |
|
|
||
|
||
|
||
|
||
|
left |
|
Lowest |
|
left |
Tip
Use parentheses to avoid ambiguous situations when using binary operators.
Note
The NOT
variations, such as NOT BETWEEN
, NOT IN
, NOT LIKE
, NOT RLIKE
have the same precedence as their non-NOT
variations.