Scalar Expressions

Most SQL statements can contain scalar expressions that compute newvalues from data. For example, in the query SELECT ceil(price) FROM
items, the expression ceil(price) computes the rounded-up value ofthe values from the price column.

Scalar expressions produce values suitable to store in a single tablecell (one column of one row). They can be contrasted withtable expressions and selection queries, which produce resultsstructured as a table.

The following sections provide details on each of these options.

Constants

Constant expressions represent a simple value that doesn't change.They are described further in section SQL Constants.

Column references

An expression in a query can refer to columns in the current data source in two ways:

• Using the name of the column, e.g., price in SELECT price FROM
items.

  • If the name of a column is also aSQL keyword, the namemust be appropriately quoted. For example: SELECT "Default" FROM
configuration.
  • If the name is ambiguous (e.g., when joining across multipletables), it is possible to disambiguate by prefixing the columnname by the table name. For example, SELECT items.price FROM
items.
• Using the ordinal position of the column. For example, SELECT @1
FROM items selects the first column in items.

This is a CockroachDB SQL extension.

Warning:
Ordinal references should be used with care in production code! During schema updates, column ordinal positions can change and invalidate existing queries that use ordinal positions based on a previous version of the schema.

Unary and binary operations

An expression prefixed by a unary operator, or two expressionsseparated by a binary operator, form a new expression.

For a full list of CockroachDB operators, with details about their order of precedence and which data types are valid operands for each operator, see Functions and Operators.

Value comparisons

The standard operators < (smaller than), > (greater than), <=(lower than or equal to), >= (greater than or equal to), =(equals), <> and != (not equal to), IS (identical to), and IS
NOT (not identical to) can be applied to any pair of values from asingle data type, as well as some pairs of values from different datatypes.

See also this section over which data types are valid operandsfor each operator.

The following special rules apply:

• NULL is always ordered smaller than every other value, even itself.
• NULL is never equal to anything via =, even NULL. To checkwhether a value is NULL, use the IS operator or the conditionalexpression IFNULL(..).
  See also NULLs and Ternary Logic.

Typing rule

All comparisons accept any combination of argument types and result in type BOOL.

Comparison with NaN

CockroachDB recognizes the special value NaN(Not-a-Number) for scalars oftype FLOAT or DECIMAL.

As per the IEEE 754standard, NaN is considered to be different from every other numericvalue in comparisons.

There are two exceptions however, made for compatibility with PostgreSQL:

• NaN is considered to be equal with itself in comparisons. IEEE 754specifies that NaN is different from itself.
• NaN is considered to be smaller than every other value, including-INFINITY. IEEE 754 specifies that NaN does not order with anyother value, i.e., x <= NaN and x >= NaN are both false for everyvalue of x including infinities.
  These exceptions exist so that the value NaN can be used in WHEREclauses and indexes.

For example:

> SELECT FLOAT 'NaN' < 1, 1 < FLOAT 'NaN', FLOAT 'NaN' < FLOAT 'NaN';

+-----------------+-----------------+---------------------------+
| FLOAT 'NaN' < 1 | 1 < FLOAT 'NaN' | FLOAT 'NaN' < FLOAT 'NaN' |
+-----------------+-----------------+---------------------------+
|      true       |      false      |           false           |
+-----------------+-----------------+---------------------------+

> SELECT FLOAT 'NaN' = FLOAT 'NaN' AS result;

+--------+
| result |
+--------+
| true   |
+--------+

> SELECT FLOAT 'NaN' < FLOAT '-INFINITY' AS result;

+--------+
| result |
+--------+
| true   |
+--------+

Multi-valued comparisons

Syntax:

<expr> <comparison> ANY <expr>
<expr> <comparison> SOME <expr>
<expr> <comparison> ALL <expr>

The value comparison operators <, >, =, <=, >=, <> and!=, as well as the pattern matching operators [NOT] LIKE and[NOT] ILIKE, can be applied to compare a single value on the left tomultiple values on the right.

This is done by combining the operator using the keywords ANY/SOME or ALL.

The right operand can be either an array, a tuple or subquery.

The result of the comparison is true if and only if:

• For ANY/SOME, the comparison of the left value is true for anyelement on the right.
• For ALL, the comparison of the left value is true for everyelement on the right.
  For example:

> SELECT 12 = ANY (10, 12, 13);

+-----------------------+
| 12 = ANY (10, 12, 13) |
+-----------------------+
|         true          |
+-----------------------+

> SELECT 12 = ALL (10, 12, 13);

+-----------------------+
| 12 = ALL (10, 12, 13) |
+-----------------------+
|         false         |
+-----------------------+

> SELECT 1 = ANY ARRAY[2, 3, 1];

+------------------------+
| 1 = ANY ARRAY[2, 3, 1] |
+------------------------+
|          true          |
+------------------------+

> SELECT 1 = ALL (SELECT * FROM rows); -- using a tuple generated by a subquery

Typing rule

The comparison between the type on the left and the element type ofthe right operand must be possible.

Set membership

Syntax:

<expr> IN <expr>
<expr> IN ( ... subquery ... )
<expr> NOT IN <expr>
<expr> NOT IN ( ... subquery ... )

Returns TRUE if and only if the value of the left operand is part ofthe result of evaluating the right operand. In the subquery form, anyselection query can be used.

For example:

> SELECT a IN (1, 2, 3) FROM sometable;

> SELECT a IN (SELECT * FROM allowedvalues) FROM sometable;

> SELECT ('x', 123) IN (SELECT * FROM rows);

Note:
See Subqueries for more details and performance best practices.

Typing rule

IN requires its right operand to be a homogeneous tuple type and its left operandto match the tuple element type. The result has type BOOL.

String pattern matching

Syntax:

<expr> LIKE <expr>
<expr> ILIKE <expr>
<expr> NOT LIKE <expr>
<expr> NOT ILIKE <expr>

Evaluates both expressions as strings, then tests whether the string on the leftmatches the pattern given on the right. Returns TRUE if a match is foundor FALSE otherwise, or the inverted value for the NOT variants.

Patterns can contain _ to match any singlecharacter, or % to match any sequence of zero or more characters.ILIKE causes the match to be tested case-insensitively.

For example:

> SELECT 'monday' LIKE '%day' AS a, 'tuesday' LIKE 'tue_day' AS b, 'wednesday' ILIKE 'W%' AS c;

+------+------+------+
|  a   |  b   |  c   |
+------+------+------+
| true | true | true |
+------+------+------+

Typing rule

The operands must be either both STRING or both BYTES. The result has type BOOL.

String matching using POSIX regular expressions

Syntax:

<expr> ~ <expr>
<expr> ~* <expr>
<expr> !~ <expr>
<expr> !~* <expr>

Evaluates both expressions as strings, then tests whether the stringon the left matches the pattern given on the right. Returns TRUE ifa match is found or FALSE otherwise, or the inverted value for the! variants.

The variants with an asterisk * use case-insensitive matching;otherwise the matching is case-sensitive.

The pattern is expressed usingPOSIX regular expression syntax. UnlikeLIKE patterns, a regular expression is allowed to match anywhereinside a string, not only at the beginning.

For example:

> SELECT 'monday' ~ 'onday' AS a, 'tuEsday' ~ 't[uU][eE]sday' AS b, 'wednesday' ~* 'W.*y' AS c;

+------+------+------+
|  a   |  b   |  c   |
+------+------+------+
| true | true | true |
+------+------+------+

Typing rule

The operands must be either both STRING or both BYTES. The result has type BOOL.

String matching using SQL regular expressions

Syntax:

<expr> SIMILAR TO <expr>
<expr> NOT SIMILAR TO <expr>

Evaluates both expressions as strings, then tests whether the string on the leftmatches the pattern given on the right. Returns TRUE if a match is foundor FALSE otherwise, or the inverted value for the NOT variant.

The pattern is expressed using the SQL standard's definition of a regular expression.This is a mix of SQL LIKE patterns and POSIX regular expressions:

• _ and % denote any character or any string, respectively.
• . matches specifically the period character, unlike in POSIX where it is a wildcard.
• Most of the other POSIX syntax applies as usual.
• The pattern matches the entire string (as in LIKE, unlike POSIX regular expressions).
  For example:

> SELECT 'monday' SIMILAR TO '_onday' AS a, 'tuEsday' SIMILAR TO 't[uU][eE]sday' AS b, 'wednesday' SIMILAR TO 'w%y' AS c;

+------+------+------+
|  a   |  b   |  c   |
+------+------+------+
| true | true | true |
+------+------+------+

Typing rule

The operands must be either both STRING or both BYTES. The result has type BOOL.

Function calls and SQL special forms

General syntax:

<name> ( <arguments...> )

A built-in function name followed by an opening parenthesis, followedby a comma-separated list of expressions, followed by a closingparenthesis.

This applies the named function to the arguments betweenparentheses. When the function's namespace is not prefixed, thename resolution rules determine whichfunction is called.

See also the separate section on supported built-in functions.

In addition, the following SQL special forms are also supported:

Typing rule

In general, a function call requires the arguments to be of the typesaccepted by the function, and returns a value of the type determinedby the function.

However, the typing of function calls is complicated by the factSQL supports function overloading. See our blog post for more details.

Subscripted expressions

It is possible to access one item in an array value using the [ … ] operator.

For example, if the name a refers to an array of 10values, a[3] will retrieve the 3rd value. The first value has index1.

If the index is smaller or equal to 0, or larger than the size of the array, thenthe result of the subscripted expression is NULL.

Typing rule

The subscripted expression must have an array type; the index expressionmust have type INT. The result has the element type of thesubscripted expression.

Conditional expressions

Expressions can test a conditional expression and, depending on whetheror which condition is satisfied, evaluate to one or more additionaloperands.

These expression formats share the following property: some of theiroperands are only evaluated if a condition is true. This mattersespecially when an operand would be invalid otherwise. For example,IF(a=0, 0, x/a) returns 0 if a is 0, and x/a otherwise.

IF expressions

Syntax:

IF ( <cond>, <expr1>, <expr2> )

Evaluates <cond>, then evaluates <expr1> if the condition is true,or <expr2> otherwise.

The expression corresponding to the case when the condition is falseis not evaluated.

Typing rule

The condition must have type BOOL, and the two remaining expressionsmust have the same type. The result has the same type as theexpression that was evaluated.

Simple CASE expressions

Syntax:

CASE <cond>
  WHEN <condval1> THEN <expr1>
  [ WHEN <condvalx> THEN <exprx> ] ...
  [ ELSE <expr2> ]
END

Evaluates <cond>, then picks the WHEN branch where <condval> isequal to <cond>, then evaluates and returns the corresponding THENexpression. If no WHEN branch matches, the ELSE expression isevaluated and returned, if any. Otherwise, NULL is returned.

Conditions and result expressions after the first match are not evaluated.

Typing rule

The condition and the WHEN expressions must have the same type.The THEN expressions and the ELSE expression, if any, must have the same type.The result has the same type as the THEN/ELSE expressions.

Searched CASE expressions

Syntax:

CASE WHEN <cond1> THEN <expr1>
   [ WHEN <cond2> THEN <expr2> ] ...
   [ ELSE <expr> ]
END

In order, evaluates each <cond> expression; at the first <cond>expression that evaluates to TRUE, returns the result of evaluating thecorresponding THEN expression. If none of the <cond> expressionsevaluates to true, then evaluates and returns the value of the ELSEexpression, if any, or NULL otherwise.

Conditions and result expressions after the first match are not evaluated.

Typing rule

All the WHEN expressions must have type BOOL.The THEN expressions and the ELSE expression, if any, must have the same type.The result has the same type as the THEN/ELSE expressions.

NULLIF expressions

Syntax:

NULLIF ( <expr1>, <expr2> )

Equivalent to: IF ( <expr1> = <expr2>, NULL, <expr1> )

Typing rule

Both operands must have the same type, which is also the type of the result.

COALESCE and IFNULL expressions

Syntax:

IFNULL ( <expr1>, <expr2> )
COALESCE ( <expr1> [, <expr2> [, <expr3> ] ...] )

COALESCE evaluates the first expression first. If its value is notNULL, its value is returned directly. Otherwise, it returns theresult of applying COALESCE on the remaining expressions. If all theexpressions are NULL, NULL is returned.

Arguments to the right of the first non-null argument are not evaluated.

IFNULL(a, b) is equivalent to COALESCE(a, b).

Typing rule

The operands must have the same type, which is also the type of the result.

Logical operators

The Boolean operators AND, OR and NOT are available.

Syntax:

NOT <expr>
<expr1> AND <expr2>
<expr1> OR <expr2>

AND and OR are commutative. Moreover, the input to ANDand OR is not evaluated in any particular order. Some operand maynot even be evaluated at all if the result can be fully ascertained usingonly the other operand.

Note:
This is different from the left-to-right "short-circuit logic" found in other programming languages. When it is essential to force evaluation order, use a conditional expression.

See also NULLs and Ternary Logic.

Typing rule

The operands must have type BOOL. The result has type BOOL.

Aggregate expressions

An aggregate expression has the same syntax as a function call, with a specialcase for COUNT:

<name> ( <arguments...> )
COUNT ( * )

The difference between aggregate expressions and function calls isthat the former useaggregate functionsand can only appear in the list of rendered expressions in aSELECT clause.

An aggregate expression computes a combined value, depending onwhich aggregate function is used, across all the rows currentlyselected.

Typing rule

The operand and return types are determined like for regular function calls.

Window function calls

A window function call has the syntax of a function call followed by an OVER clause:

<name> ( <arguments...> ) OVER <window>
<name> ( * ) OVER <window>

It represents the application of a window or aggregate function over asubset ("window") of the rows selected by a query.

Typing rule

The operand and return types are determined like for regular function calls.

Explicit type coercions

Syntax:

<expr> :: <type>
CAST (<expr> AS <type>)

Evaluates the expression and converts the resulting value to thespecified type. An error is reported if the conversion is invalid.

For example: CAST(now() AS DATE)

Note that in many cases a type annotation is preferrable to a typecoercion. See the section ontype annotations below for moredetails.

Typing rule

The operand can have any type.The result has the type specified in the CAST expression.

As a special case, if the operand is a literal, a constant expressionor a placeholder, the CAST type is used to guide the typing of theoperand. See our blog post for more details.

Collation expressions

Syntax:

<expr> COLLATE <collation>

Evaluates the expression and converts its result to a collated stringwith the specified collation.

For example: 'a' COLLATE de

Typing rule

The operand must have type STRING. The result has type COLLATEDSTRING.

Array constructors

Syntax:

ARRAY[ <expr>, <expr>, ... ]

Evaluates to an array containing the specified values.

For example:

> SELECT ARRAY[1,2,3] AS a;

+---------+
|    a    |
+---------+
| {1,2,3} |
+---------+

The data type of the array is inferred from the values of the providedexpressions. All the positions in the array must have the same data type.

If there are no expressions specified (empty array), orall the values are NULL, then the type of the array must bespecified explicitly using a type annotation. For example:

> SELECT ARRAY[]:::int[];

Tip:
To convert the results of a subquery to an array, use ARRAY(…) instead.

Tip:
CockroachDB also recognizes the syntax ARRAY(a, b, c) as an alias for ARRAY[a, b, c]. This is an experimental, CockroachDB-specific SQL extension and may be removed in a later version of CockroachDB.

Typing rule

The operands must all have the same type.The result has the array type with the operand type as element type.

Tuple constructor

Syntax:

(<expr>, <expr>, ...)
ROW (<expr>, <expr>, ...)

Evaluates to a tuple containing the values of the provided expressions.

For example:

> SELECT ('x', 123, 12.3) AS a;

+----------------+
|       a        |
+----------------+
| ('x',123,12.3) |
+----------------+

The data type of the resulting tuple is inferred from the values.Each position in a tuple can have a distinct data type.

Typing rule

The operands can have any type.The result has a tuple type whose item types are the types of the operands.

Explicitly typed expressions

Syntax:

<expr>:::<type>
ANNOTATE_TYPE(<expr>, <type>)

Evaluates to the given expression, requiring the expression to havethe given type. If the expression doesn't have the given type, anerror is returned.

Type annotations are specially useful to guide the arithmetic onnumeric values. For example:

> SELECT (1 / 0):::FLOAT;

+-----------------+
| (1 / 0):::FLOAT |
+-----------------+
| +Inf            |
+-----------------+

> SELECT (1 / 0);         --> error "division by zero"

pq: division by zero

> SELECT (1 / 0)::FLOAT;  --> error "division by zero"

pq: division by zero

Type annotations are also different from cast expressions (see above) inthat they do not cause the value to be converted. For example,now()::DATE converts the current timestamp to a date value (anddiscards the current time), whereas now():::DATE triggers an errormessage (that now() does not have type DATE).

Check our blog formore information about context-dependent typing.

Typing rule

The operand must be implicitly coercible to the given type.The result has the given type.

Subquery expressions

Scalar subqueries

Syntax:

( ... subquery ... )

Evaluates the subquery, asserts that it returns a single row and single column,and then evaluates to the value of that single cell. Any selection querycan be used as subquery.

For example, the following query returns TRUE if there are more rows in table users than in tableadmins:

> SELECT (SELECT COUNT(*) FROM users) > (SELECT COUNT(*) FROM admins);

Note:
See Subqueries for more details and performance best practices.

Typing rule

The operand must have a table type with only one column.The result has the type of that single column.

Existence test on the result of subqueries

Syntax:

EXISTS ( ... subquery ... )
NOT EXISTS ( ... subquery ... )

Evaluates the subquery and then returns TRUE or FALSE depending onwhether the subquery returned any row (for EXISTS) or didn't returnany row (for NOT EXISTS). Any selection querycan be used as subquery.

Note:
See Subqueries for more details and performance best practices.

Typing rule

The operand can have any table type. The result has type BOOL.

Conversion of subquery results to an array

Syntax:

ARRAY( ... subquery ... )

Evaluates the subquery and converts its results to an array. Anyselection query can be used as subquery.

Note:
See Subqueries for more details and performance best practices.

Tip:
To convert a list of scalar expressions to an array, use ARRAY[…] instead.

See also

• Constants
• Selection Queries
• Table Expressions
• Data Types
• Functions and Operators
• Subqueries

Was this page helpful?
YesNo