RANDOM()

Syntax

How it works

Every major database ships a built-in pseudo-random generator, but the spelling and the return type differ. PostgreSQL and SQLite call it RANDOM(), while MySQL and SQL Server call it RAND(). This is one of the most common cross-dialect gotchas: the exact same query fails or behaves differently when you move it between engines.

The return type is not uniform either. In PostgreSQL and MySQL, RANDOM() and RAND() return a floating point value in the half-open range [0, 1), meaning 0 is possible but 1 is never returned. SQL Server RAND() behaves the same way. SQLite is the odd one out: its RANDOM() returns a large signed 64-bit integer between -9223372036854775808 and 9223372036854775807. To get a float in [0, 1) in SQLite you divide, for example (RANDOM() + 9223372036854775808) / 18446744073709551615.0, or more simply use ABS(RANDOM() % 100) style arithmetic for integers.

Because the generator is pseudo-random, the sequence is deterministic given a seed. MySQL and SQL Server accept a seed directly with RAND(seed), and PostgreSQL seeds the session with SETSEED(value) before calling RANDOM(). Seeding is what makes a shuffle reproducible for tests. To scale a float into an integer range, combine RANDOM/RAND with FLOOR(), for example FLOOR(RAND() * N) + 1 for values from 1 to N.

Examples

Random float between 0 and 1

-- PostgreSQL / MySQL / SQL Server all return a float in [0, 1)
SELECT RANDOM() AS r;   -- PostgreSQL
SELECT RAND()   AS r;   -- MySQL, SQL Server

r
------------------
0.4172869123640

Random integer from 1 to 100

-- Scale the float, drop the fraction with FLOOR, then shift by 1
-- MySQL / SQL Server
SELECT FLOOR(RAND() * 100) + 1 AS dice;

-- PostgreSQL
SELECT FLOOR(RANDOM() * 100) + 1 AS dice;

dice
----
  73

Random integer in SQLite (integer generator)

-- SQLite RANDOM() returns a large signed integer,
-- so use ABS and modulo to land in a range 1..100
SELECT ABS(RANDOM() % 100) + 1 AS dice;

dice
----
  42

Pick one random row from a table

-- Works but scans and sorts the whole table (see mistakes)
-- PostgreSQL / SQLite
SELECT id, name
FROM customers
ORDER BY RANDOM()
LIMIT 1;

-- MySQL
SELECT id, name
FROM customers
ORDER BY RAND()
LIMIT 1;

-- SQL Server
SELECT TOP 1 id, name
FROM customers
ORDER BY NEWID();

id  | name
----+---------
 87 | Fatima

Reproducible sequence with a seed

-- MySQL / SQL Server: same seed gives the same value every run
SELECT RAND(42) AS r;

-- PostgreSQL: seed the session, then call RANDOM()
SELECT SETSEED(0.42);
SELECT RANDOM() AS r;

r
------------------
0.0011872280416

Fast random row on a big table (keyset instead of sort)

-- Avoid ORDER BY RAND() on large tables. Pick a random id
-- in the key range and grab the next row at or above it.
-- MySQL
SELECT id, name
FROM customers
WHERE id >= FLOOR(RAND() * (SELECT MAX(id) FROM customers))
ORDER BY id
LIMIT 1;

id  | name
----+---------
513 | Omar

Common mistakes

-- Shuffles by sorting the ENTIRE table, then throws
-- almost all of it away. Very slow on big tables.
SELECT *
FROM orders
ORDER BY RAND()
LIMIT 10;

-- Sample cheaply: filter to a small candidate set first,
-- or use TABLESAMPLE / a keyset pick on the primary key.
SELECT *
FROM orders
WHERE id >= FLOOR(RAND() * (SELECT MAX(id) FROM orders))
ORDER BY id
LIMIT 10;

-- Assumes SQLite RANDOM() is a float in [0, 1)
-- like PostgreSQL. It is NOT: it is a huge integer,
-- so this almost always evaluates to a huge number.
SELECT FLOOR(RANDOM() * 100) + 1 AS dice;  -- SQLite

-- SQLite RANDOM() returns a signed 64-bit integer.
-- Use ABS and modulo to build a range.
SELECT ABS(RANDOM() % 100) + 1 AS dice;    -- SQLite

-- Expecting the same 'random' row back on every run,
-- for example in a test, without any seed.
SELECT * FROM customers ORDER BY RANDOM() LIMIT 1;

-- Seed first so the sequence is reproducible.
-- MySQL / SQL Server
SELECT * FROM customers ORDER BY RAND(42) LIMIT 1;

-- PostgreSQL
SELECT SETSEED(0.42);
SELECT * FROM customers ORDER BY RANDOM() LIMIT 1;

Performance

The generator call itself is cheap, but the query pattern around it usually is not. ORDER BY RAND() forces the engine to compute a random key for every candidate row and then perform a full sort, so cost grows with the size of the table even when you only want a single row. This is the single most common performance trap involving random values.

For sampling large tables, prefer a physical sampling clause where your engine offers one. PostgreSQL and SQL Server support TABLESAMPLE, which reads a fraction of pages instead of every row. Where sampling is not exact enough, a keyset approach works everywhere: pick a random value inside the primary key range with FLOOR(RAND() * MAX(id)) and fetch the first row at or above it, which uses the primary key index and touches only one row.

Beware that a per-row random expression is volatile, so the optimiser cannot cache it and cannot use it to prune with an index. Materialising a small candidate set first, then randomising only that set, keeps the expensive part small. See the indexing guide for how index range scans avoid full table sorts.

Interview questions

-- 1..6 dice roll (MySQL / SQL Server)
SELECT FLOOR(RAND() * 6) + 1 AS roll;