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 )
  • literal: A literal of a type described in Databricks SQL data types.
  • column_reference: A reference to a column in a table or column alias.
  • field_reference: A reference to a field in a STRUCT type.
  • CAST expression: An expression casting the argument to a different type.
  • CASE expression: An expression allowing for conditional evaluation.
  • expr: An expression itself which is combined with an operator, or which is an argument to a function.
  • operator
  • function_invocation: An expression invoking a built-in or user defined function
  • ( expr ): Enforced precedence that overrides operator precedence.
  • scalar_subquery:
    • ( query ): An expression based on a query that must return a single column and and at most one row.

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
>