SQL - SELECT RANDOM

In SQL, the RANDOM() function is used to fetch random rows from a table. It is an extremely useful function in various applications, such as selecting random users, retrieving random questions from a pool, or even for random sampling in data analysis.

In this article, we will explore how to use RANDOM() (or its database-specific variants) across different SQL databases like MySQL, PostgreSQL, and SQLite, and how it can help you retrieve data in random order.

What is RANDOM() in SQL?

The RANDOM() function is used to generate random values and can be applied to return random rows or records from a table in SQL. It has many real-life applications. Some common use cases include:

1. There are a lot of employees in an organization. Suppose, if the event manager wants to mail any ten random employees then he/she can use the RANDOM( ) in SQL to get the Email Id of the ten random employees.
   
2. It can also be used to display random questions during an online exam or MCQ from a pool of questions.

Example of SQL Queries for Random Selection

Sample Table: Customer Information

Let’s consider the following table of Customer Information to demonstrate how to use RANDOM() in SQL.

Example 1: Using RAND() in MySQL

SELECT col_1,col_2, ... FROM Table_Name
ORDER BY RAND()

col_1 : Column 1
col_2 : Column 2

The above query will return the entire table for the specific columns mentioned and the rows will be random and changing position every time we run the query. To get a single row randomly, we can use the LIMIT Clause and set to only one row. ORDER BY clause in the query is used to order the row(s) randomly.

SELECT col_1,col_2, ... FROM Table_Name
ORDER BY RAND()
LIMIT 1

col_1 : Column 1
col_2 : Column 2

The output will vary each time you run the query, and it will return a random row from the table. For example:

Example 2: Using RANDOM() in PostgreSQL and SQLite

It is exactly the same as MYSQL. Just replace RAND( ) with RANDOM( ). Like MySQL, the output will vary with each query execution. For example, the query might return:

SELECT col_1,col_2, ... FROM Table_Name
ORDER BY RAND()
LIMIT 1

col_1 : Column 1
col_2 : Column 2

Output :

We can observe that the above queries return the rows randomly from all the set of rows in the table. The RANDOM( ) clause is beneficial when there are humongous records in the database.

Performance Considerations

While RANDOM() is useful, it should be noted that random ordering can be very resource intensive, especially when working with large data sets. The database must sort the entire table by random value, which can be slow if the table has millions of rows.

For optimal performance, consider the following strategies:

• Limit the number of rows: Always use LIMIT to restrict the number of random rows returned, especially for large tables.
  
• Use indexed columns: If you're selecting a random row based on certain conditions (e.g., a random customer from a specific city), try to add indexes to relevant columns to speed up the query.

Conclusion

In this article, we’ve covered how to use the RANDOM() function (or its equivalent, RAND(), depending on the SQL dialect) to retrieve random rows from a table in SQL. Whether you're using MySQL, PostgreSQL, or SQLite, the process of selecting random rows is quite similar. By using ORDER BY RANDOM() or ORDER BY RAND(), you can easily select a random sample of rows from your database, which is useful in a wide range of applications, from sending random emails to displaying random quiz questions.