RANDOM()
SQL · Math Functions
Theory
RANDOM returns a random value each time it is evaluated, typically once per row. Its most common use is random sampling: ORDER BY RANDOM() LIMIT 3 gives each row a random sort key, shuffles the table and keeps the first three, so you get a different three employees on every run.
The exact function and its output differ by database. PostgreSQL's RANDOM() returns a decimal between 0 and 1. SQLite's RANDOM() returns a large signed integer, so you use ABS(RANDOM()) % 100 for a 0–99 value. MySQL uses RAND(), SQL Server uses RAND() for a single value but NEWID() for per-row shuffling (because RAND() returns the same value for every row in a query), and Snowflake's RANDOM() returns a 64-bit integer. ORDER BY RANDOM() must sort the entire table, which is slow on millions of rows; databases offer TABLESAMPLE or SAMPLE clauses for large tables. Results are not repeatable unless you set a seed (for example setseed() in PostgreSQL).
Why we use it
Analysts often need a fair, unbiased subset: a sample for manual QA, random A/B test groups, or a quick look at data without reading everything. RANDOM gives every row an equal chance.
When to use it
Use ORDER BY RANDOM() LIMIT n for small to medium tables and one-off samples. For big tables use TABLESAMPLE/SAMPLE. For repeatable splits (stable A/B groups), use MOD on a hash or id instead of RANDOM.
How it works
- Query reads all candidate rows
- Evaluate RANDOM() once per row as a sort key
- Sort rows by that random key
- Is LIMIT n present? Yes: Keep only the first n rows. No: Return all rows in shuffled order.
- Different sample on every run
Syntax
RANDOM()Example
SELECT * FROM employees ORDER BY RANDOM() LIMIT 3;Key points
- Range and type differ by database — SQLite returns a large signed integer
- ORDER BY RANDOM() LIMIT n is a common random-sampling pattern
- MySQL and SQL Server use RAND(); SQL Server shuffles rows with ORDER BY NEWID()
- ABS(RANDOM()) % 100 gives a 0–99 value in SQLite
- Sorting the whole table is slow on big data — use TABLESAMPLE/SAMPLE there