Autocomplete reference

This page is the reference behind the suggestion list: when it opens, what it offers in each part of a query, how it orders the items and where it stops. To use suggestions while you write, start with Write and check SQL.

When the list appears

The list opens under your cursor while you type in the main SQL editor. Press Ctrl+Space to open it at any time; the shortcut window lists this key as Show suggestions, as described in Editor reference: shortcuts and tools.
After a dot, the list appears at once. db. lists the tables of that database, and table., alias. or cte. lists the columns of that source. A dot never shows the AI suggestion; the list there is for names only.
Every other suggestion waits about a tenth of a second after you stop typing, so the list does not flicker while the editor reads the statement.
The list is wider than the editor's standard menus and uses a monospaced font, so long names stay readable.
Some smaller SQL boxes in popups are keyword-only. They offer keywords and snippets, but no tables or columns.
In a few compact editors in other screens, the list does not open while you type. Press Ctrl+Space to open it.

What appears in each part of a query

Where the cursor is	What the list offers
After `FROM` or `JOIN`, with nothing typed	Database names
After a database name and a dot	That database's tables
After a table, alias or CTE name and a dot	That source's columns
In the select list	Columns, functions and snippets, plus the `*` and full-column-list items
In a condition, such as `WHERE`, `PREWHERE`, `HAVING` or `ON`	Columns, functions and tables, and `NOT`
After `{`	Your parameter names
After `{name:`	ClickHouse type names
After `SETTINGS`, or a comma inside it	Setting names
After a setting and `=`	That setting's values

If you type an opening quote and then accept an item, the quote you typed is used, not doubled, so the inserted name is quoted once.

How the list is ordered

Items are ranked in groups. Each position sets the order of its groups; the fallback order is tables, then functions, then snippets, then keywords.
Inside a group, an exact name comes first, then a name that starts with what you typed, then a match at the start of a word inside the name, after an underscore or a capital letter, and then a looser match.
Typing two or more characters of a snippet trigger, such as sel, lifts that snippet above every group (see the snippet table below).
Right after SELECT * or alias.*, the first item is Expand column list, which replaces the asterisk with the columns of the tables in the query. For how the rewrite behaves, see Write and check SQL.
In a dot context, the list narrows to that source's columns only.

The snippet triggers

Type a trigger and pick the snippet from the list; it expands into the SQL shown.

Trigger	What it inserts
`sel`	`SELECT *` and a new line with `FROM`
`selw`	A `SELECT ... FROM ... WHERE` skeleton
`selg`	A `SELECT` with `count()` and a `GROUP BY`
`ij`	An `INNER JOIN` with an `ON` condition
`lj`	A `LEFT JOIN` with an `ON` condition
`cnt`	`count()`, the number of rows
`cntd`	`uniq(...)`, the number of distinct values
`with`	A whole CTE: `WITH ... AS (...) SELECT * FROM ...`
`,cte`	Another CTE, appended to an existing `WITH` clause
`rn`	`row_number() OVER (PARTITION BY ... ORDER BY ...)`
`rnk`	`rank() OVER (PARTITION BY ... ORDER BY ...)`
`bet`	`BETWEEN ... AND ...`
`in`	An `IN (...)` list of values to match
`case`	A `CASE WHEN ... THEN ... ELSE ... END` block

The list also includes seld for SELECT DISTINCT, selo for SELECT ... ORDER BY, and sub for an inline subquery.

What each kind of item inserts

Item	What it inserts
Database	Its name and a dot, and the list moves on to that database's tables.
Table	The table name in double quotes, such as `"orders"`. Its card lists the table's columns and marks the `NOT NULL` and `PRIMARY KEY` columns.
Column	The column name in double quotes, or `alias."column"` when a source is in play.
`*` items	`*`, or the whole column list of a table as quoted names separated by commas. These items appear in the select list only; they are not offered in `WHERE`, `GROUP BY`, `ORDER BY`, `HAVING`, `QUALIFY` or `PREWHERE`.
Constraint join	A ready-made join condition, labelled `constraint join:` with the table's database and name, built from the relationships known for your tables.
CTE or alias	The name. A CTE is offered only after its definition and only in the same or a deeper part of the query; when a CTE and an alias share a name, the CTE wins.
Function	The call with its argument list ready to fill in. The item shows the function's category next to its name, and its card carries a description, a Returns: line and an Example:.
Setting	The setting's name and `=`, and the list moves on to its values. The card shows the setting's type and default, and a Tier: marker when the setting is not in Production; such settings are listed after production ones. A boolean setting offers `1` and `0`.
Parameter	The full `{name:Type}` placeholder. See Query parameters.

Clause keywords the list offers

When one clause is complete, the list offers the keywords that can follow, so you do not have to remember the clause order.

Position	Keywords offered
At the start of a statement	`SELECT`, `WITH`
After `SELECT *`	`EXCEPT`, `FROM`, `WHERE`, `PREWHERE`, `GROUP BY`, `HAVING`, `ORDER BY`, `LIMIT`, `SETTINGS`, `FORMAT`, `AS`
After a complete condition in `WHERE`	`AND`, `OR`, `GROUP BY`, `HAVING`, `QUALIFY`, `ORDER BY`, `LIMIT BY`, `LIMIT`, `SETTINGS`, `FORMAT`, `WINDOW`, `UNION ALL`, `UNION DISTINCT`
After a column in a condition	The comparison operators, such as `=`, `!=`, `<` and `>`, and word operators such as `IN`, `LIKE`, `IS NULL` and `BETWEEN`, with their negative forms
After `GROUP BY`	`ALL`, `CUBE`, `ROLLUP`, `GROUPING SETS`, and aggregate functions among the names
After a table in `FROM`	The join keywords, from plain `JOIN` to the `GLOBAL`, `ANY`, `ALL`, `SEMI`, `ANTI` and `ASOF` variants, and also `AS`, `FINAL` and `SAMPLE`
After `GROUP`, `ORDER` or `PARTITION` alone	`BY`
After `ARRAY` alone	`JOIN`
After `FORMAT`	The output formats, such as `CSV`, `JSONEachRow` and `Parquet`

Where suggestions stop

Long queries. In a query longer than 50,000 characters, the list stops appearing.
One statement at a time. A semicolon ends the statement, and only the statement at the cursor is read, so names from an earlier statement are not offered.
CTE and alias scope. A CTE is offered only after its definition, in the same or a deeper part of the query. When two names match, the nearest definition wins.
Masked databases are listed last. When your roles include a masked (PII_) copy of a database, its items are listed after the readable ones, so masked values are not reached by accident. See SQL dialect and data scope.
Infrastructure settings. Settings that only tune the database's infrastructure are left out; the list keeps the settings that change how a query runs.
Loaded lists. The function and setting lists come from your database. The editor loads them when it opens; if a load fails, it keeps its built-in list and tries again later. The copy behind the list is refreshed about once an hour, so a function or setting added very recently can take a short while to appear.

Questions, answered

Why did suggestions stop in a very long query?

The list stops for queries longer than 50,000 characters. Shorten the query, or split the work into a few queries, to get suggestions back.

Why does typing `sel` show a snippet above the columns?

From the second character of a snippet trigger, that snippet is lifted above every group, so you can reach it without typing the whole word. Accept it with Enter or Tab.

Why did accepting a name insert double quotes?

Table and column names are inserted in double quotes, so names with spaces or reserved words stay valid. A column is inserted as alias."column" when the editor knows which source it belongs to. If you typed the opening quote yourself, it is used, not doubled.

Why is there no `*` item in WHERE or GROUP BY?

The * and whole-column-list items are offered in the select list only, because a wildcard is not valid in those clauses. Everywhere else, pick the columns you need.

Why does a masked copy of a database sit lower in the list?

The masked (PII_) copy mirrors the names of its readable twin, so it is ranked below it; reading masked values is meant to be a deliberate choice. See SQL dialect and data scope.

Why can't I see my CTE before I define it?

A CTE becomes available after its definition is complete, and only in that part of the query and its subqueries. If a CTE and an alias share a name, the CTE is used.

Why do some popups offer only keywords?

Some smaller SQL boxes, such as those in popups, use a keyword-only editor. They offer keywords and snippets, with no tables or columns. Write the query in the Editor when you want the full list.

Why is a function I just added missing from the list?

The function and setting lists are loaded from your database, and the copy behind them is refreshed about once an hour. Reopen the Editor to reload the list; a very new function can take a short while to appear.

Why don't I get suggestions from the statement above?

Only the statement at the cursor is read, and a semicolon ends a statement. Move the cursor back into the statement you are writing.

My query is half-written. Why do I still get table suggestions?

When a statement does not parse yet, the editor still reads the tables from the FROM part of your text, so the tables you named stay in the list while you finish the query.