Reserved words and schemas

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Reserved words are literals used as keywords by the SQL language which should not be used as identifiers to avoid unexpected behavior.

Reserved schema names have special meaning to Azure Databricks.

Reserved words

Azure Databricks does not formally disallow any specific literals from being used as identifiers.

However, to use any of the following list of identifiers as a table alias, you must surround the name with back-ticks (`).

  • ANTI
  • CROSS
  • EXCEPT
  • FULL
  • INNER
  • INTERSECT
  • JOIN
  • LATERAL
  • LEFT
  • MINUS
  • NATURAL
  • ON
  • RIGHT
  • SEMI
  • UNION
  • USING

Special words in expressions

The following list of identifiers can be used anywhere, but Azure Databricks treats them preferentially as keywords within expressions in certain contexts:

  • NULL

    The SQL NULL value.

  • DEFAULT

    Indicates a column default.

  • TRUE

    The SQL boolean true value.

  • FALSE

    The SQL boolean false value.

  • LATERAL

    Used as a column qualifier to indicate explicit lateral correlation.

Use back-ticks (NULL and DEFAULT) or qualify the column names with a table name or alias.

Azure Databricks uses the CURRENT_ prefix to refer to some configuration settings or other context variables. The underbar (_) prefix is intended for Azure Databricks pseudo columns. In Databricks Runtime, an existing pseudo column is the _metadata column.

Identifiers with these prefixes are not treated preferentially. However, avoid columns or column aliases using these prefixes to avoid unexpected behavior.

Reserved catalog names

Azure Databricks reserves the following list of catalog names for current or future use:

  • Catalog names starting with SYS
  • Catalog names starting with DATABRICKS

Avoid using these names.

Reserved schema names

Azure Databricks reserves the following list of schema names for current or future use:

  • BUILTIN

    Future use to qualify builtin functions.

  • SESSION

    Future use to qualify temporary views and functions.

  • INFORMATION_SCHEMA

    Holds the SQL Standard information schema.

  • Schema names starting with SYS or DATABRICKS

Avoid using these names.

ANSI Reserved words

Azure Databricks does not enforce ANSI reserved words. The following list of SQL2016 keywords is provided for informational purposes only.

  • A

    ALL, ALTER, AND, ANY, ARRAY, AS, AT, AUTHORIZATION

  • B

    BETWEEN, BOTH, BY

  • C

    CASE, CAST, CHECK, COLLATE, COLUMN, COMMIT, CONSTRAINT, CREATE, CROSS, CUBE, CURRENT, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_USER

  • D

    DELETE, DESCRIBE, DISTINCT, DROP

  • E

    ELSE, END, ESCAPE, EXCEPT, EXISTS, EXTERNAL, EXTRACT

  • F

    FALSE, FETCH, FILTER, FOR, FOREIGN, FROM, FULL, FUNCTION

  • G

    GLOBAL, GRANT, GROUP, GROUPING

  • H

    HAVING

  • I

    IN, INNER, INSERT, INTERSECT, INTERVAL, INTO, IS

  • J

    JOIN

  • L

    LEADING, LEFT, LIKE, LOCAL

  • N

    NATURAL, NO, NOT, NULL

  • O

    OF, ON, ONLY, OR, ORDER, OUT, OUTER, OVERLAPS

  • P

    PARTITION, POSITION, PRIMARY

  • R

    RANGE, REFERENCES, REVOKE, RIGHT, ROLLBACK, ROLLUP, ROW, ROWS

  • S

    SELECT, SESSION_USER, SET, SOME, START

  • T

    TABLE, TABLESAMPLE, THEN, TIME, TO, TRAILING, TRUE, TRUNCATE

  • U

    UNION, UNIQUE, UNKNOWN, UPDATE, USER, USING

  • V

    VALUES

  • W

    WHEN, WHERE, WINDOW, WITH

Examples

-- Using SQL keywords
> CREATE TEMPORARY VIEW where(where) AS (VALUES (1));

> SELECT where from FROM where select;
  1

-- Usage of NULL
> SELECT NULL, `null`, T.null FROM VALUES(1) AS T(null);
 NULL   1       1

-- current_date is eclipsed by the column alias T.current_date
> SELECT (SELECT current_date), current_date, current_date()
    FROM VALUES(1) AS T(current_date);
 2021-10-23     1       2021-10-23

-- Reserved keyword ANTI cannot be used as table alias
> SELECT * FROM VALUES(1) AS ANTI;
Error in query: no viable alternative at input 'ANTI'

> SELECT * FROM VALUES(1) AS `ANTI`;
  1