Syntax

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/syntax.md)

In this section, we will take a look at ClickHouse's SQL syntax.
ClickHouse uses a syntax based on SQL but offers a number of extensions and optimizations.

## Query Parsing[​](#query-parsing "Direct link to Query Parsing")

There are two types of parsers in ClickHouse:

- *A full SQL parser* (a recursive descent parser).
- *A data format parser* (a fast stream parser).

The full SQL parser is used in all cases except for the `INSERT` query, which uses both parsers.

Let's examine the query below:

```
INSERT INTO t VALUES (1, 'Hello, world'), (2, 'abc'), (3, 'def')
```

As mentioned already, the `INSERT` query makes use of both parsers.
The `INSERT INTO t VALUES` fragment is parsed by the full parser,
and the data `(1, 'Hello, world'), (2, 'abc'), (3, 'def')` is parsed by the data format parser, or fast stream parser.

Turning on the full parser

You can also turn on the full parser for the data
by using the [`input_format_values_interpret_expressions`](/docs/operations/settings/formats#input_format_values_interpret_expressions) setting.

When the aforementioned setting is set to `1`,
ClickHouse first tries to parse values with the fast stream parser.
If it fails, ClickHouse tries to use the full parser for the data, treating it like an SQL [expression](#expressions).

The data can have any format.
When a query is received, the server calculates no more than [max\_query\_size](/docs/operations/settings/settings#max_query_size) bytes of the request in RAM
(by default, 1 MB), and the rest is stream parsed.
This is to allow for avoiding issues with large `INSERT` queries, which is the recommended way to insert your data in ClickHouse.

When using the [`Values`](/docs/interfaces/formats/Values) format in an `INSERT` query,
it may appear that data is parsed the same as for expressions in a `SELECT` query however this is not the case.
The `Values` format is much more limited.

The rest of this section covers the full parser.

Note

For more information about format parsers, see the [Formats](/docs/interfaces/formats) section.

## Spaces[​](#spaces "Direct link to Spaces")

- There may be any number of space symbols between syntactical constructions (including the beginning and end of a query).
- Space symbols include the space, tab, line feed, CR, and form feed.

## Comments[​](#comments "Direct link to Comments")

ClickHouse supports both SQL-style and C-style comments:

- SQL-style comments begin with `--`, `#!` or `#`  and continue to the end of the line. A space after `--` and `#!` can be omitted.
- C-style comments:
  - `//` (or more than 2 `/` characters) followed by text until the end of the line. Spaces after `/` are not required.
  - Can span from `/*` to `*/` for multiline comments. Spaces are not required either.
  - C-style comments can be nested.

For example:

```
/*
 * Compute the number of days between two dates.
 * /* Returns NULL if either argument is NULL */
 */
SELECT
    dateDiff('day', toDate('2024-01-01'), toDate('2024-12-31')) AS days_in_year, -- 365
    dateDiff('day', toDate('2020-01-01'), today()) AS days_since  #! since 2020
    ///////////////////////////////////////////////////////////////////
    # TODO: add hour/minute variants
```

## Keywords[​](#keywords "Direct link to Keywords")

Keywords in ClickHouse can be either *case-sensitive* or *case-insensitive* depending on the context.

Keywords are **case-insensitive** when they correspond to:

- SQL standard. For example, `SELECT`, `select` and `SeLeCt` are all valid.
- Implementation in some popular DBMS (MySQL or Postgres). For example, `DateTime` is the same as `datetime`.

Note

You can check whether a data type name is case-sensitive in the [system.data\_type\_families](/docs/operations/system-tables/data_type_families) table.

In contrast to standard SQL, all other keywords (including functions names) are **case-sensitive**.

Furthermore, Keywords are not reserved.
They are treated as such only in the corresponding context.
If you use [identifiers](#identifiers) with the same name as the keywords, enclose them into double-quotes or backticks.

For example, the following query is valid if the table `table_name` has a column with the name `"FROM"`:

```
SELECT "FROM" FROM table_name
```

## Identifiers[​](#identifiers "Direct link to Identifiers")

Identifiers are:

- Cluster, database, table, partition, and column names.
- [Functions](#functions).
- [Data types](/docs/sql-reference/data-types).
- [Expression aliases](#expression-aliases).

Identifiers can be quoted or non-quoted, although the latter is preferred.

Non-quoted identifiers must match the regex `^[a-zA-Z_][0-9a-zA-Z_]*$` and cannot be equal to [keywords](#keywords).
See the table below for examples of valid and invalid identifiers:

|  |  |  |  |
| --- | --- | --- | --- |
| Valid identifiers Invalid identifiers|  |  | | --- | --- | | `xyz`, `_internal`, `Id_with_underscores_123_` `1x`, `[email protected]`, `äußerst_schön` | | | |

If you want to use identifiers the same as keywords or you want to use other symbols in identifiers, quote it using double quotes or backticks, for example, `"id"`, `` `id` ``.

Note

The same rules that apply for escaping in quoted identifiers also apply for string literals. See [String](#string) for more details.

Avoid using dots in column names

Column names containing dots, columns sharing a common dot-prefix, and columns with the `Array` type can each be interpreted as part of a flattened Nested structure when `flatten_nested = 1` (the default). This can cause unexpected array-length validation on inserts and renaming restrictions.

Avoid using dots in column names if possible.
Use underscores (`_`) or another separator instead of dots in column names unless you intentionally need `Nested` semantics.

## Literals[​](#literals "Direct link to Literals")

In ClickHouse, a literal is a value which is directly represented in a query.
In other words it is a fixed value which does not change during query execution.

Literals can be:

- [String](#string)
- [Numeric](#numeric)
- [Compound](#compound)
- [`NULL`](#null)
- [Heredocs](#heredoc) (custom string literals)

We take a look at each of these in more detail in the sections below.

### String[​](#string "Direct link to String")

String literals must be enclosed in single quotes. Double quotes are not supported.

Escaping works by either:

- using a preceding single quote where the single-quote character `'` (and only this character) can be escaped as `''`, or
- using the preceding backslash with the following supported escape sequences listed in the table below.

Note

The backslash loses its special meaning i.e. it is interpreted literally should it precede characters other than the ones listed below.