SQL LIMIT  Clause

SQL LIMIT Clause limits the number of results returned in the results set. The LIMIT Clause is utilized with the accompanying database systems:

• MySQL
• PostgreSQL
• SQLite

SQL LIMIT Clause Example

Since the LIMIT Clause is not supported in SQL Server we need to create a table in MySQL/PostgreSQL/SQLite. We will use the LIMIT clause in MySQL

CREATE TABLE Employee (
   EmpId INTEGER PRIMARY KEY, 
   EmpName VARCHAR(225) NOT NULL,  
   Email VARCHAR(225) NOT NULL,   
   Address VARCHAR(225) NOT NULL,
   Age INT NOT NULL,
   Salary MONEY NOT NULL
);

INSERT INTO Employee (EmpId, EmpName, Email, Address, Age, Salary)
VALUES (1, 'Shubham', 'shubham@example.com', 'India', 23, 50000.00),
       (2, 'Aman', 'aman@example.com', 'Australia', 21, 45000.00),
       (3, 'Naveen', 'naveen@example.com', 'Sri Lanka', 24, 55000.00),
       (4, 'Aditya', 'aditya@example.com', 'Austria', 21, 42000.00),
       (5, 'Nishant Saluja', 'nishant@example.com', 'Spain', 22, 48000.00);
    
SELECT * FROM Employee ;

Output:

SELECT LIMIT Clause in SQL Example

In this example, we will use the SELECT LIMIT clause to display only 2 results.

Query:

SELECT * FROM Employee
WHERE Salary = 45000
LIMIT 2;

Output:

From the above query, the LIMIT operator limits the number of records to be returned. Here, it returns the first 2 rows from the table.

SQL LIMIT with WHERE Clause Example

The accompanying query selects the initial 4 records from the Employee table with a given condition.

Query:

SELECT * FROM Employee
WHERE Salary = 45000
LIMIT 2;

Output:

The above query will select all the employees according to the imposed condition (i.e. it selects the limited 2 records from the table where salary is 2000). Finally, the first 2 rows would be returned by the above query.

SQL LIMIT With OFFSET Clause Example

The OFFSET keyword is utilized to indicate beginning rows from where to select rows. For instance,

Query:

SELECT * FROM Employee
LIMIT 2 OFFSET 2;

Output:

Here, the above query selects 2 rows from the beginning of the third row (i.e., OFFSET 2 means, the initial 2 rows are excluded or avoided).

SQL TOP, LIMIT, and FETCH FIRST clauses are used to retrieve a specific number of records from a table. These clauses are especially useful in large datasets with thousands of records. Each of these SQL clauses performs a similar operation of limiting the results returned by a query, but they are supported by different database management systems:

• SQL TOP Clause is used in SQL Server and Sybase to limit the number of records returned.
• SQL LIMIT Clause is utilized in MySQL, PostgreSQL, and SQLite.
• SQL FETCH FIRST Clause is part of the SQL standard and is supported by Oracle, DB2, PostgreSQL, and SQL Server (as part of OFFSET-FETCH).

Depending on the database management system (DBMS) being used, you can utilize the respective clause to efficiently manage data retrieval. This article will provide examples and guidance on how to use the SQL TOP, LIMIT, and FETCH FIRST clauses in different SQL environments.