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:

Literals

Literals are constant values.

For more information about literals, see Literals.

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.
<> or !=	Not equal to.
IS	Identical to .
IS NOT	Not identical to.

Note

NULL values are handled differently than other value expressions:

• NULL is always smaller than anything, including another NULL.

• NULL is never equal to anything, including another NULL (=). To check if a value is null, use IS NULL

Operator Precedence

The following table lists the operators in decreasing order of precedence:

Precedence	Operator	Associativity
Highest	.	left
	+ - (unary)
	^	left
	* / %	left
	+ - (binary)	left
	||	right
	BETWEEN, IN, LIKE, RLIKE
	< > = <= >= <> !=
	IS NULL, IS NOT NULL
	NOT
	AND	left
Lowest	OR	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.