SQL Limit Result

In this lesson, you will learn how to retrieve fixed number of records from the table using SQL.

Why you should limit the result?

It is very important to limit query results for several reasons:

1- Better Performance

Limiting query results can significantly improve the performance of your database system. If a query returns a large number of rows, it can consume a lot of system resources and slow down the database server. By limiting the results, you reduce the amount of data that needs to be processed and transmitted, which can lead to faster query execution.

2- Resource Efficiency

Large result sets consume memory and network bandwidth. By limiting the number of rows returned, you free up these resources for other tasks and improve the overall efficiency of your application and database.

3- Better User Experience

Users generally don't want to see or interact with extremely large result sets. Limiting query results ensures that users are presented with manageable and meaningful data, which enhances the user experience.

4- More Security

Limiting results can help prevent data leakage and protect sensitive information. If a query accidentally retrieves too many rows, it may expose data to unauthorized users.

5- Scalability

When your application scales and more users or concurrent connections are accessing the database, limiting query results becomes even more important to maintain system performance. Without result limits, the database can become overwhelmed with requests.

Limit Results In SQL

The way you can follow to set the maximum number of rows to be returned varies from one database to another:

• In MySQL and SQLite, we use the LIMIT clause.
• In SQL Server and Access, we use the TOP clause.
• In Oracle, we use the ROWNUM clause or the FETCH FIRST n ROWS ONLY syntax.

MySQL LIMIT Syntax

The LIMIT clause can be used at the end of the SELECT statement as follows.

SELECT column1, column2, ...
FROM table_name
LIMIT [offset,] row_count;

• offset is an optional parameter that can be added to set the number of rows to be skipped first.
• row_count is the maximum number of rows that can be returned.

Preparing A Database

Since you will learn how to limit query results, you must have a database table with some records to practice retrieving some of them. Execute the following query to create the database.

-- If there is already a database named "demo" delete it
DROP DATABASE IF EXISTS demo;

-- Create a new database called "demo"
CREATE DATABASE demo;

-- Select the "demo" database
USE demo;

-- Create a new table called "users" inside the selected database
CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    country VARCHAR(100)
);

-- Insert 10 records in the "users" table
-- We add null in the id column because its value will be generated automatically by the database server
INSERT INTO users VALUES (null, "Emily", "United Kingdom");
INSERT INTO users VALUES (null, "Omar", "Jordan");
INSERT INTO users VALUES (null, "Olivia", "France");
INSERT INTO users VALUES (null, "William", "Norway");
INSERT INTO users VALUES (null, "Sophia", "Hawai");
INSERT INTO users VALUES (null, "Daniel", "Germany");
INSERT INTO users VALUES (null, "Ava", "Japan");
INSERT INTO users VALUES (null, "James", "Australia");
INSERT INTO users VALUES (null, "Mia", "France");
INSERT INTO users VALUES (null, "Ethan", "Russia");
    

Limit The Number Of Rows Examples

The following query returns the first 5 records from the users table.

SELECT * FROM users
LIMIT 5;
    

The following query skips the first 2 records and then returns the 5 next records from the users table.

SELECT * FROM users
LIMIT 2, 5;
    

The following query returns the last records from the users table.
Note: It arranges users in descending order and returns the one at the top.

SELECT * FROM users
ORDER BY id DESC
LIMIT 1;
    

Limit Results Exercise

1- Write a query that creates a new database called "training".
Note: you should recreate it if it already exists.

2- Write a query that creates a table called "customers" and check whether it exists or not. the table should contain the following fields:

• id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
• first_name VARCHAR(50)
• last_name VARCHAR(50)
• phone VARCHAR(20)

3- Write a query that inserts 5 rows in the "customers" table.

4- Write a query that returns the last 3 records from the "customers" table.

DROP DATABASE IF EXISTS training;

CREATE DATABASE training;

USE training;

CREATE TABLE IF NOT EXISTS customers (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(20)
);

INSERT INTO customers (first_name, last_name, phone) VALUES
('Jack', 'Reed', '10203040'),
('Emma', 'Clark', '55512388'),
('Luke', 'Lee', '32452012'),
('Jim', 'Wilson', '96112377'),
('Lily', 'Brooks', '36018090');

SELECT * FROM customers
ORDER BY id DESC
LIMIT 3;