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, FETCH FIRST Clause
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.