MySQL Select Random Records

Sometimes we want to fetch random records from the database table. For example, our table has stored several quotes, and there is a need to display a random quote on GUI. In such a case, we will write an SQL query to fetch random records from the table. In this section, we are going to see how to select a random record from a table.

Real-World Examples:

Online exams, where we will display a random question.
Displaying random featured items on the home page of an e-commerce website.
Display random pictures from a gallery and used this as the featured pictures.

MySQL does not provide any built-in statement for returning the random rows from a database table. We can accomplish this with the help of a RAND() function.

Syntax

The following is a syntax to select random records from a database table:

SELECT * FROM table_name
ORDER BY RAND()
LIMIT N;

Let us understands the parameters of the statement in detail:

First, we have specified the table name to which we are going to select random records.
Second, we have specified the RAND function that returns random values for each row in the table.
Third, we have specified an ORDER BY This clause sorts all table rows by the random number generated by the RAND() function.
Finally, we have specified the LIMIT clause picks the N random records from a database table.

Example

Let us understand how we can generate random records from the database table through an example. First, we will create a table named 'students' using the below statement:

mysql> CREATE TABLE students(  
    id int NOT NULL AUTO_INCREMENT,  
    name varchar(45) NOT NULL,  
    class int NOT NULL,  
    email_id varchar(65) NOT NULL,  
    PRIMARY KEY (id)  
);

Next, we will fill records into this table using the INSERT statement as follows:

INSERT INTO students (name, class, email_id)   
VALUES ('Stephen', 6, 'stephen@javatpoint.com'), 
('Bob', 7, 'bob@javatpoint.com'), 
('Steven', 8, 'steven@javatpoint.com'), 
('Donald', 6, 'donald@javatpoint.com'), 
('Jenifer', 9, 'jenifer@javatpoint.com'),
('Peter', 9, 'peter@javatpoint.com'),
('Alexandar', 7, 'alexandar@javatpoint.com');

Next, we will display all records from the table using the query is as follows:

Now, we will execute the below query to select random records from the table. Suppose we want to select five random records from the table; we will query the data as follows:

mysql> SELECT id, name, email_id FROM students
ORDER BY RAND() LIMIT 5;

It returns the following output:

If we run the above query again, we will get the output as follows:

It is to notice that whenever we will perform RAND() function, it always returns a different result because it is random. Therefore, this technique works effectively only with a small table. In the case of a big table, it will be slow. It is because MySQL first sorts the entire table and then return the random ones.

The query speed also depends on the number of rows available in the table. Thus if our table has more rows, it takes more time to generate the random records for each row.

Next TopicMySQL Extract

← prev next →