Syntax

Learn the key elements of ClickHouse SQL syntax.

ClickHouse SQL syntax follows standard SQL conventions with some unique features. This guide covers the essential elements of ClickHouse SQL syntax.

Key syntax elements

Spaces and comments

• Spaces between syntactical constructions are optional but recommended for readability.
• SQL-style comments: Start with --, #!, or # and continue to the end of the line.
• C-style comments: Enclosed between /* and */, can be multiline.

Keywords and identifiers

• Keywords are case-insensitive for SQL standard terms (e.g., SELECT, select, SeLeCt).
• Other keywords, including function names, are case-sensitive.
• Identifiers (e.g., table names, column names) can be quoted or non-quoted.
  • Non-quoted: Must match ^[a-zA-Z_][0-9a-zA-Z_]*$
  • Quoted: Use double quotes or backticks (e.g., "id", `id`)

Literals

• Numeric: Integers, decimals, exponential notation, hex, and binary (e.g., 1, 0.1, 1e100, 0xc0fe, 0b1101)
• String: Enclosed in single quotes (e.g., 'Hello, world')
• Array: Square brackets (e.g., [1, 2, 3])
• Tuple: Round brackets (e.g., (1, 'Hello', 2))
• NULL: Represents a missing value

Functions and operators

• Function calls: function_name(arg1, arg2, ...)
• Operators are converted to their corresponding functions during parsing

Expressions and aliases

• Expressions can include functions, identifiers, literals, operators, and subqueries.
• Aliases: expression AS alias_name

Examples

Basic SELECT query

SELECT taco_name, sauce_name
FROM taco_orders
WHERE taco_type = 'spicy'
ORDER BY taco_name DESC
LIMIT 10;

Using functions and operators

SELECT
  toDate(order_time) AS order_date,
  count() AS taco_count,
  avg(taco_price) AS average_price
FROM taco_sales
GROUP BY order_date
HAVING taco_count > 100
ORDER BY order_date;

Complex expression with alias

SELECT
  (count() * 100 / sum(count()) OVER ()) AS taco_percentage,
  toStartOfMonth(order_time) AS month
FROM taco_orders
GROUP BY month
ORDER BY taco_percentage DESC;

Frequently asked questions

Are ClickHouse keywords case-sensitive?

Keywords corresponding to SQL standards are case-insensitive (e.g., SELECT, FROM, WHERE). However, other keywords, including function names, are case-sensitive.

How do I comment my ClickHouse SQL queries?

You can use SQL-style comments (starting with --, #!, or #) for single-line comments, or C-style comments (/* */) for multi-line comments.

Can I use spaces freely in my ClickHouse queries?

Yes, spaces between syntactical constructions are optional. You can use them freely to improve the readability of your queries.

What types of literals does ClickHouse support?

ClickHouse supports numeric literals (integers, decimals, exponential notation, hex, and binary), string literals (enclosed in single quotes), array literals (using square brackets), tuple literals (using round brackets), and NULL.