Reserved words and schemas
Applies to: Databricks SQL 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
orDATABRICKS
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