Syntax

Note

There is a separate case when tuples appear in the `IN` clause of a `SELECT` query.
Query results can include tuples, but tuples cannot be saved to a database (except for tables using the [Memory](/docs/engines/table-engines/special/memory) engine).

### NULL[​](#null "Direct link to NULL")

`NULL` is used to indicate that a value is missing.
To store `NULL` in a table field, it must be of the [Nullable](/docs/sql-reference/data-types/nullable) type.

Note

The following should be noted for `NULL`:

- Depending on the data format (input or output), `NULL` may have a different representation. For more information, see [data formats](/docs/interfaces/formats).
- `NULL` processing is nuanced. For example, if at least one of the arguments of a comparison operation is `NULL`, the result of this operation is also `NULL`. The same is true for multiplication, addition, and other operations. We recommend to read the documentation for each operation.
- In queries, you can check `NULL` using the [`IS NULL`](/docs/sql-reference/functions/functions-for-nulls#isNull) and [`IS NOT NULL`](/docs/sql-reference/functions/functions-for-nulls#isNotNull) operators and the related functions `isNull` and `isNotNull`.

### Heredoc[​](#heredoc "Direct link to Heredoc")

A [heredoc](https://en.wikipedia.org/wiki/Here_document) is a way to define a string (often multiline), while maintaining the original formatting.
A heredoc is defined as a custom string literal, placed between two `$` symbols.

For example:

```
SELECT $heredoc$SHOW CREATE VIEW my_view$heredoc$;

┌─'SHOW CREATE VIEW my_view'─┐
│ SHOW CREATE VIEW my_view   │
└────────────────────────────┘
```

Note

- A value between two heredocs is processed "as-is".

Tip

- You can use a heredoc to embed snippets of SQL, HTML, or XML code, etc.

## Defining and Using Query Parameters[​](#defining-and-using-query-parameters "Direct link to Defining and Using Query Parameters")

Query parameters allow you to write generic queries that contain abstract placeholders instead of concrete identifiers.
When a query with query parameters is executed,
all placeholders are resolved and replaced by the actual query parameter values.

Query parameters can be defined in several ways:

- `SET param_<name>=<value>` — using a `SET` command in a query.
- `--param_<name>='<value>'` — as an argument to `clickhouse-client` on the command line.
- `param_<name>=<value>` — as a URL query string parameter for the HTTP interface.

A query parameter can be referenced in a query using `{<name>: <datatype>}`, where `<name>` is the query parameter name and `<datatype>` is the datatype it is converted to.

Example with SET command

For example, the following SQL defines parameters named `a`, `b`, `c` and `d` - each with a different data type:

```
SET param_a = 13;
SET param_b = 'str';
SET param_c = '2022-08-04 18:30:53';
SET param_d = {'10': [11, 12], '13': [14, 15]};

SELECT
   {a: UInt32},
   {b: String},
   {c: DateTime},
   {d: Map(String, Array(UInt8))};

13    str    2022-08-04 18:30:53    {'10':[11,12],'13':[14,15]}
```

Example with clickhouse-client

If you are using `clickhouse-client`, the parameters are specified as `--param_name=value`. For example, the following parameter has the name `message` and it is retrieved as a `String`:

```
clickhouse-client --param_message='hello' --query="SELECT {message: String}"

hello
```

If the query parameter represents the name of a database, table, function or other identifier, use `Identifier` for its type. For example, the following query returns rows from a table named `uk_price_paid`:

```
SET param_mytablename = "uk_price_paid";
SELECT * FROM {mytablename:Identifier};
```

Example with the HTTP interface

Query parameters can be passed as URL query string parameters with the `param_` prefix. For example:

```
curl -s "http://localhost:8123/?param_message=hello" --data-binary "SELECT {message: String}"

hello
```

Example with the Web UI

The built-in Web UI (`play.html`) automatically detects `{name:Type}` parameter placeholders in the query and displays labeled input fields for each parameter. The parameter values are included in the HTTP request and also persisted in the page URL for bookmarking and sharing.

Note

Query parameters are not general text substitutions which can be used in arbitrary places in arbitrary SQL queries.
They are primarily designed to work in `SELECT` statements in place of identifiers or literals.

## Functions[​](#functions "Direct link to Functions")

Function calls are written like an identifier with a list of arguments (possibly empty) in `()`.
In contrast to standard SQL, the brackets are required, even for an empty argument list.
For example:

```
now()
```

There are also:

- [Regular functions](/docs/sql-reference/functions/overview).
- [Aggregate functions](/docs/sql-reference/aggregate-functions).

Some aggregate functions can contain two lists of arguments in brackets. For example:

```
quantile (0.9)(x)
```

These aggregate functions are called "parametric" functions,
and the arguments in the first list are called "parameters".

Note

The syntax of aggregate functions without parameters is the same as for regular functions.

## Operators[​](#operators "Direct link to Operators")

Operators are converted to their corresponding functions during query parsing, taking their priority and associativity into account.

For example, the expression

```
1 + 2 * 3 + 4
```

is transformed to

```
plus(plus(1, multiply(2, 3)), 4)`
```

## Data Types and Database Table Engines[​](#data-types-and-database-table-engines "Direct link to Data Types and Database Table Engines")

Data types and table engines in the `CREATE` query are written the same way as identifiers or functions.
In other words, they may or may not contain an argument list in brackets.

For more information, see the sections:

- [Data types](/docs/sql-reference/data-types)
- [Table engines](/docs/engines/table-engines)
- [CREATE](/docs/sql-reference/statements/create).

## Expressions[​](#expressions "Direct link to Expressions")

An expression can be any of the following:

- a function
- an identifier
- a literal
- the application of an operator
- an expression in brackets
- a subquery
- an asterisk

It can also contain an [alias](#expression-aliases).

A list of expressions is one or more expressions separated by commas.
Functions and operators, in turn, can have expressions as arguments.

A constant expression is an expression whose result is known during query analysis, i.e. before execution.
For example, expressions over literals are constant expressions.

## Expression Aliases[​](#expression-aliases "Direct link to Expression Aliases")

An alias is a user-defined name for an [expression](#expressions) in a query.

```
expr AS alias
```

The parts of the syntax above are explained below.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Part of syntax Description Example Notes|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `AS` The keyword for defining aliases. You can define the alias for a table name or a column name in a `SELECT` clause without using the `AS` keyword. `SELECT table_name_alias.column_name FROM table_name table_name_alias`. In the [CAST](/docs/sql-reference/functions/type-conversion-functions#CAST) function, the `AS` keyword has another meaning. See the description of the function.| `expr` Any expression supported by ClickHouse. `SELECT column_name * 2 AS double FROM some_table` |  |  |  |  | | --- | --- | --- | --- | | `alias` Name for `expr`. Aliases should comply with the [identifiers](#identifiers) syntax. `SELECT "table t".column_name FROM table_name AS "table t"`.  | | | | | | | | | | | | | | | |

### Notes on Usage[​](#notes-on-usage "Direct link to Notes on Usage")

- Aliases are global for a query or subquery, and you can define an alias in any part of a query for any expression. For example:

```
SELECT (1 AS n) + 2, n`.
```