SQL RAND() Function - Scaler Topics

Overview

Let's say, you and your friend are taking part in an online aptitude exam, and both of you start at the same time, but the questions the two of you receive are not the same or might be in a different order. Have you ever wondered why this happens, or how this happens? The SQL RAND() function is the mastermind behind making your questions different or out of order. In the backend, a query is run to fetch random questions from the given set, and thus, your questions are randomized.

The SQL RAND() function returns a random decimal value in between 0 (inclusive) and 1 (exclusive). Multiplying these fractional values with some integers we can definitely get random integers as well! Let's learn more about the SQL RAND() in this article.

Note: All codes are in MySQL.

Introduction to SQL RAND() Function

The RAND() function is a mathematical function in SQL that returns a pseudo-random float value between 0 and 1 [Including 0 and excluding 1].

Note: The term pseudo-random is used because the output of the RAND() function is not $100$ % random, it is rather generated using a deterministic and repeated pattern.

Syntax

Parameters

The seed value can be any integer (int, tinyint, or smallint). This is an optional parameter. If no seed value is given by the user then the SQL Server Database Engine assigns a random seed value every time the function is used. The same result will be returned for a given seed value.

Getting a Random Decimal Number Within A Range Using RAND()

Here, we will see how we can get a random decimal number within a range from a to b using the RAND() function in SQL.

Formula

Example

Getting a random decimal number within a range from 4 to 10

Code:

Output for multiple executions:

Getting A Random Integer Within A Range Using RAND()

Here we will see how we can get a random integer number within a range from a to b using the RAND() function in SQL.

Formula

Example

Getting a random integer number within a range from 4 to 10

Code:

Output for multiple executions:

Applications

Applications of the RAND() function in SQL are along the lines of the example we discussed at the beginning of the article, i.e. to randomly fetch data from a given set of data.

A good situation where the RAND() can be used in real life is something like assigning questions to students during a practical examination, there are a set of questions in a table format, and a query is run to select a question at random from the table.

SQL Query for RAND() Function

The SQL RAND() function is used to select a random row from the table.

Example

Let us take the example of randomizing the questions in an online exam. Below is the query used for the purpose.

Query:

Explanation:

The above query will give one question randomly from the set of questions.

Conclusion

RAND() function in SQL generates a pseudo-random decimal number between 0 (inclusive) and 1 (exclusive).
The output of the RAND() function is called pseudo-random because it is not $100$ % random, rather there is a periodic repetition, which makes it look random.
We can use the RAND() function to generate random decimal numbers or integers in a range.
The SQL RAND() function is used to select a random row from a table.