There are several scenarios where you want to fetch random data and show it on the page or use it somehow depending on the output you want. In this article, we will learn how can we fetch random data from the MySQL table. We will see why we need random records and then we will go through multiple methods to fetch some random records from the table.
Why do We Need Random Records?
Suppose you want to show “Quote of the day” or a random post on the top of the page or any image as a featured image from the database. In this case, you can fetch one or more random records from the MySQL table.
There are multiple methods for fetching random records. We will see all possible methods where some methods are easy to implement whereas some methods provide excellent performance. So, without wasting time, let’s get started.
Examples to Fetch Random Records from Table
In this tutorial, we will use a table of the following schema. The table consists of a hundred records of the authors which are inserted in sequential order of id.
In the above image, only a few records are shown. But there are a hundred records present in the table. We will be using all records for the below examples.
Example 1: Fetch Random Records Using ORDER BY RAND()
This is one of the simplest methods to pick up random records and display them. You don’t need to write any complex subqueries to fetch random data from the table.
MySQL provides us with the RAND() function which is used to get any random value. This function doesn’t help directly to pick random records. However, we can use it with the ORDER BY clause to fetch the random records.
Have a look at the following example to understand how we can use the RAND() function to get random records.
SELECT * FROM authors
ORDER BY RAND()
LIMIT 1;Code language: SQL (Structured Query Language) (sql)Here, the RAND() function generates and assigns random values to every row in the table. Then, the ORDER BY clause sorts the records of the table by those randomly generated values.
Note that, the randomly generated values are not sorted. Hence, the particular record may get a lower random value than the previous record.
Finally, the LIMIT clause limits the number of output rows. The above query will show the total number of rows that you specify with the LIMIT clause.
Have a look at the output below. We have executed the query twice and gotten two random records.
If you don’t use the LIMIT clause, the query will return all the records randomly. Check below.
SELECT * FROM authors
ORDER BY RAND();Code language: SQL (Structured Query Language) (sql)
As you can see, all records are randomly displayed.
This is the simplest technique to get random records and it works well for a small number of records. However, if your table consists of a relatively larger number of records, this method is not suitable as it takes more time than the other methods listed below.
Example 2: Fetch Random Records Using INNER JOIN
In this method, we will use the indexed column ID and the INNER JOIN to fetch random records. This technique is faster than the previous one because it uses the indexed ID column for sorting. Let’s see how it works.
SELECT * FROM authors AS a1
JOIN (
SELECT id FROM authors
ORDER BY RAND() LIMIT 1
) AS a2
ON a1.id=a2.id;Code language: SQL (Structured Query Language) (sql)Here, we are using the INNER JOIN clause to join the table itself and fetch the records randomly. Note that, in the previous example, we were using the RAND() function without targeting any column. Here, we are using the id column which is indexed and helps sort the records faster.
Note that there must be a primary integer attribute such as id for faster execution. Moreover, this method also works for non-contiguous id values. The LIMIT clause is used to limit the number of records to be shown. Below is the output of the above query which was executed twice.
As you can see, we have received two different records which were fetched randomly.
Conclusion
In this tutorial, we have learned how can we fetch random records from the table by two methods. There can be more methods but we have examined the simplest and the fastest method. I hope you have understood and learned to display random records from the table. You can use it in your project for displaying random posts or random images. Stay tuned with mysqlcode.com for more such interesting tutorials!