LIMIT could do more

Anyone who writes analytical SQL builds queries the same way: start with one table, add a join, check the output, add another join, check again. The workflow depends on being able to look at a small sample at every step. SQL’s LIMIT is bad at exactly this.

The problem

You start innocently:

SELECT * FROM events LIMIT 10;

Then you add joins, and this happens:

SELECT *
FROM events AS e
LIMIT 10
INNER JOIN users AS u ON e.user_id = u.user_id;      -- syntax error

LIMIT is the last clause. It limits the result, after all the joining is done. Remove it, and the query scans and joins the full tables to give you the rows you wanted to glance at. On a large database that means minutes and gigabytes to test a typo.

The workaround is to limit each source in a subquery:

SELECT *
FROM (SELECT * FROM events LIMIT 10) AS e
INNER JOIN (SELECT * FROM users LIMIT 100) AS u ON e.user_id = u.user_id;

It works, but it makes the query harder to write and to read, and when it is right you have to unwrap every (SELECT ... LIMIT ?) back to the plain table, which is exactly the kind of mechanical edit that introduces mistakes. (A caveat worth knowing: a join between two limited samples may produce no rows at all, because the sampled keys do not meet. That is a feature of sampling, not of the syntax.)

A second confusion: LIMIT before ORDER BY

SELECT * FROM events LIMIT 100 ORDER BY user_id;     -- syntax error

The intent is clear and natural to read left to right: take the first 100 rows of the table, then sort them. Standard SQL says the opposite: ORDER BY first, LIMIT last. So the person who wants “any 100 rows, sorted” has to write a subquery, and the person who wants “the top 100 by key” must not.

The evidence that the need is real

ClickHouse already admits the pattern. The old way to get ten numbers was SELECT * FROM system.numbers LIMIT 10; numbers(10) took over, a table function with the limit baked in. If a limit on the source is so useful that it earned its own syntax for generated tables, it is useful for real ones.

The proposal

Let a table reference carry its own limit:

SELECT * FROM my_table(100);
-- or, explicit:
SELECT * FROM my_table(limit = 100);

so the “iteratively build a join” workflow becomes FROM events(limit = 10) AS e JOIN users(limit = 100) AS u ... and the production version is the same query with the arguments removed.

Related, and in the same spirit as a “developer-friendly SQL”: allow LIMIT to come before ORDER BY with the “limit the source, then sort” meaning, a + for string concatenation, and a FROM table SELECT columns form so that autocompletion knows the table before it is asked for columns.

What exists today

I checked each against ClickHouse 26.4:

Idea	Status
FROM table SELECT cols	Works. FROM numbers(3) SELECT number returns 0, 1, 2.
LIMIT before ORDER BY	Syntax error. Not implemented.
+ for strings	Error: Illegal types String and String of arguments of function plus. The concatenation operator is ||; concat() also works.
table(limit = N)	Not implemented.

For the sampling workflow, the nearest practical tools are:

• A CTE per source: WITH e AS (SELECT * FROM events LIMIT 10000) SELECT ... FROM e JOIN ..., which puts all the limits at the top of the query, one line each, so removing them is a one-place edit.
• The max_rows_to_read setting with read_overflow_mode = 'break', which makes ClickHouse stop reading and return a partial result. It is approximate (it stops at block granularity), so use it to keep an exploratory query cheap, not to produce a precise sample.
• A SAMPLE clause, if the table has a sampling key.

Those are workarounds; the request stands.

The filed issue and its outcome are summarised in Enhanced LIMIT: the request.