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 |
  expr [ expr ] |
  function_invocation |
  ( expr ) |
  scalar_subquery }

scalar_subquery
  ( query )

The brackets [ expr ] are actual brackets and do not indicate optional syntax.

Parameters

  • 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 (Databricks SQL).
  • 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: A unary or binary operator.
  • [ expr ]: A reference to an array element or a map key.
  • 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 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 SQL data type rules (Databricks SQL). 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. A boolean expression is also sometimes referred to as a condition or a predicate.

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

> SELECT a[1] FROM VALUES(array(10, 20)) AS T(a);
  20

> SELECT true;
  true