> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# SHUFFLE

> Documentation for SHUFFLE Clause

The `SHUFFLE` clause returns the rows of the query result in a uniformly random order. It is used in place of the [ORDER BY](https://clickhouse.com/docs/reference/statements/select/order-by.md) clause and cannot be combined with it.

It returns the same result as `ORDER BY rand()`, but works in O(n) time instead of O(n \* log(n)).

`SHUFFLE` can be used without `LIMIT`, to randomize the order of all rows, or with `LIMIT n`, to take a uniformly random sample of `n` rows (also returned in a random order). With `LIMIT n`, every stream keeps only `n` rows, so the memory usage is O(n) regardless of the number of rows in the query result. `OFFSET` and `LIMIT BY` can be used as with `ORDER BY`.

> [!NOTE]
>
> This is an experimental feature. Enable it with the setting [`allow_experimental_shuffle_query`](https://clickhouse.com/docs/reference/settings/session-settings#allow_experimental_shuffle_query).

## Implementation details

The random choices are made just in time: no random values are generated or stored for the rows in advance. Without `LIMIT`, every row is sent to a random one of `max_threads` streams, every stream is shuffled in parallel with the Fisher-Yates algorithm (in parts that fit in the CPU cache), and the streams are concatenated. With `LIMIT n`, every stream keeps a uniformly random sample of `n` rows by reservoir sampling, which skips the rows that cannot get into the sample without looking at them, and the samples are merged: the number of rows taken from every stream is drawn as sampling without replacement from the numbers of rows of the streams. In a distributed query, the remote servers shuffle their data and attach sorted random keys to the rows, and the initiator merges the shuffled streams by the keys, which takes O(1) memory.

Without `LIMIT`, `SHUFFLE` keeps all rows in memory. Unlike `ORDER BY`, it does not spill data to disk.

`ORDER BY rand()` and `ORDER BY rand64()` can be executed the same way as `SHUFFLE` (the setting `query_plan_use_shuffle_for_order_by_rand`) when the random value is not used anywhere else in the query and external sorting is disabled. When external sorting is configured, this optimization is disabled even with a finite `LIMIT`, because the sample may still exceed memory and `SHUFFLE` cannot spill to disk. `ORDER BY rand64()` with a finite limit retains sorting to preserve all bits of the random values.

## Examples

```sql theme={null}
SET allow_experimental_shuffle_query = 1;

SELECT number FROM numbers(5) SHUFFLE;
```

```text theme={null}
┌─number─┐
│      3 │
│      0 │
│      4 │
│      1 │
│      2 │
└────────┘
```

A random sample of three rows:

```sql theme={null}
SELECT number FROM numbers(1000000) SHUFFLE LIMIT 3;
```

```text theme={null}
┌─number─┐
│ 493721 │
│  20857 │
│ 871324 │
└────────┘
```

`SHUFFLE` is a keyword after the `FROM` clause. To use `shuffle` as a table alias, write `AS shuffle`.