Databricks SQL expression

An expression is a formula that computes a result based on literals, or column, field, or variable references using functions or operators.

Syntax

{ literal |
  column_reference |
  field_reference |
  CAST expression |
  CASE expression |
  expr operator expr |
  operator expr |
  function_invocation |
  ( expr ) |
  scalar_subquery }

scalar_subquery
  ( query )

Notes

Functions and operators expect specific data types upon which they operate that are described with the respective function or operator. Databricks SQL performs implicit casting to expected types using Databricks SQL type precedence. If an operator or function is invalid for the provided argument, Databricks SQL raises an error.

Constant expression

An expression that is only based on literals or deterministic functions with no arguments. Databricks SQL can execute the expression and use resulting constant where ordinarily literals are required.

Boolean expression

An expression with a result type of BOOLEAN.

Scalar subquery

An expression of the form ( query ). The query must return a table that has one column and at most one row.

If the query returns no row the result is NULL. If the query returns more than one row, Databricks SQL returns an error. Otherwise, the result is the value returned by the query.

Simple expression

An expression that does not contain a query, such as a scalar subquery or an EXISTS predicate.

Examples

> SELECT 1;
  1

> SELECT (SELECT 1) + 1;
  2

> SELECT 1 + 1;
  2

> SELECT 2 * (1 + 2);
  6

> SELECT 2 * 1 + 2;
  4

> SELECT substr('Spark', 1, 2);
  Sp

> SELECT c1 + c2 FROM VALUES(1, 2) AS t(c1, c2);
  3
>