RAND()/RANDOM() in SQL
Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.
Syntax
Return type
RAND()/RANDOM() Function Example
What is RAND()/RANDOM() in SQL?
The RAND()
(or RANDOM()
) function in SQL generates a random floating-point number between 0 (inclusive) and 1 (exclusive). It is commonly used for sampling data, generating test values, shuffling records, or creating random IDs. Some database systems allow seeding the function with a specific value to generate reproducible random sequences. The function is supported across multiple databases:
RAND()
is used in MySQL and SQL Server.RANDOM()
is used in PostgreSQL and SQLite.
Since RAND()
generates different values with each execution, it is often used in combination with functions like FLOOR()
or CEIL()
to generate random integers within a range.
Parameters:
seed: (Optional) Integer value to generate reproducible random numbers
Example Use Cases:
-- Select random sample of records
-- Generate random price within range
-- Create random test data
Notes:
Behavior: Non-deterministic without seed, deterministic with seed
Performance Considerations: Can be slow with ORDER BY RAND()
Version Info: Implementation varies by DBMS
Deprecated/Recommended Alternatives: Some DBMS prefer RANDOM()
Error Handling:
Error: Returns NULL if seed is NULL
Recommendation: Use seed for reproducible results in testing
Supported Databases:
DBMS
Function / Syntax
Example
Behavior with NULL
MySQL
RAND([seed])
RAND()
Returns NULL if seed NULL
PostgreSQL
RANDOM()
RANDOM()
Returns NULL
SQL Server
RAND([seed])
RAND()
Returns NULL if seed NULL
SQLite
RANDOM()
RANDOM()
Returns NULL
BigQuery
RAND()
RAND()
Returns NULL
Snowflake
RANDOM()
RANDOM()
Returns NULL
Athena
RAND()
RAND()
Returns NULL
Related Functions:
FLOOR()
Rounds a number down to the nearest integer or to a specified decimal place. Always rounds toward negative infinity.
CEIL()/CEILING()
Rounds a number up to the nearest integer or to a specified decimal place. Always rounds away from zero for positive numbers and toward zero for negative numbers.
ROUND()
Rounds a number to a specified number of decimal places or to the nearest integer.