Back to Library

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

RAND([seed]) or RANDOM()
Return type
FLOAT/DOUBLE (0 ≤ x < 1)

RAND()/RANDOM() Function Example

SELECT RAND(); 
-- Output: 0.7294838393 (random value between 0 and 1)

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

SELECT * FROM users ORDER BY RAND() LIMIT 10; 
-- Output:
id    name           email                    signup_date
---   ------------   ---------------------    -----------
45    Emma Wilson    emma.w@email.com        2024-01-15
12    James Lee      jlee@email.com          2023-11-20
89    Sarah Chen     schen@email.com         2024-02-28
34    Miguel Ruiz    mruiz@email.com         2023-12-05
67    Lisa Brown     lbrown@email.com        2024-01-30
23    Alex Kim       akim@email.com          2023-10-12
91    David Park     dpark@email.com         2024-03-01
56    Anna Smith     asmith@email.com        2024-01-02

-- Generate random price within range 

SELECT min_price + (RAND() * (max_price - min_price)) FROM products; 
-- Output:
min_price    max_price    random_price
---------    ---------    ------------
10.00        50.00        27.84
10.00        50.00        42.19
10.00        50.00        15.63
10.00        50.00        33.92
10.00        50.00        48.75

-- Create random test data 

SELECT FLOOR(RAND() * 100) AS random_number FROM generate_series(1,10);
-- Output:
random_number
-------------
47
23
89
12
56
78
34
91

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:

SIGN()

Returns the sign of a number: -1 for negative numbers, 0 for zero, and 1 for positive numbers.

Returns the sign of a number: -1 for negative numbers, 0 for zero, and 1 for positive numbers.

Returns the sign of a number: -1 for negative numbers, 0 for zero, and 1 for positive numbers.

Returns the sign of a number: -1 for negative numbers, 0 for zero, and 1 for positive numbers.

ABS()

Returns the absolute (positive) value of a number by removing the negative sign if present.

Returns the absolute (positive) value of a number by removing the negative sign if present.

Returns the absolute (positive) value of a number by removing the negative sign if present.

Returns the absolute (positive) value of a number by removing the negative sign if present.

RAND()/RANDOM()

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

MOD()

Returns the remainder after dividing one number by another. Also available as % operator in many databases.

Returns the remainder after dividing one number by another. Also available as % operator in many databases.

Returns the remainder after dividing one number by another. Also available as % operator in many databases.

Returns the remainder after dividing one number by another. Also available as % operator in many databases.

SQRT()

Returns the square root of a non-negative number. The square root is the value that, when multiplied by itself, gives the number.

Returns the square root of a non-negative number. The square root is the value that, when multiplied by itself, gives the number.

Returns the square root of a non-negative number. The square root is the value that, when multiplied by itself, gives the number.

Returns the square root of a non-negative number. The square root is the value that, when multiplied by itself, gives the number.

POWER()

Returns the result of raising a base number to a specified power (exponent). Also written as POW() in some databases.

Returns the result of raising a base number to a specified power (exponent). Also written as POW() in some databases.

Returns the result of raising a base number to a specified power (exponent). Also written as POW() in some databases.

Returns the result of raising a base number to a specified power (exponent). Also written as POW() in some databases.

One place for all your queries,
directly on your SQL editor