Limiting Results with LIMIT in SQL | SQL LIMIT Clause Tutorial

Introduction

When working with large databases, sometimes you don't want to retrieve thousands of records at once. Instead, you may want to limit the number of results returned.

SQL provides the LIMIT clause to control how many rows are retrieved, making your queries faster, more efficient, and easier to read.

In this section, you'll learn how to use the LIMIT clause effectively in SQL queries.

What is the LIMIT Clause?

The LIMIT clause in SQL is used to specify the maximum number of rows that a query should return.

Basic Syntax:

SELECT column1, column2, ...
FROM table_name
LIMIT number;

number: The maximum number of rows to return.

Example: Basic LIMIT

Retrieve the first 5 employees from the employees table:

SELECT first_name, last_name
FROM employees
LIMIT 5;

Result: Only the first 5 rows from the employees table will be displayed.

Why Use LIMIT?

• To test queries without loading too much data.
• To improve query performance when only a sample of data is needed.
• To implement pagination in applications (showing a few records per page).
• To retrieve top results (e.g., top 10 customers by spending).

LIMIT with ORDER BY

LIMIT is often used with ORDER BY to fetch the top or bottom records based on a specific column.

Example: Top 3 highest salaries

SELECT first_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Explanation:

• ORDER BY salary DESC sorts employees by salary from highest to lowest.
• LIMIT 3 fetches only the top 3 highest-paid employees.

LIMIT with OFFSET (Pagination)

Some databases (like PostgreSQL, MySQL) allow you to use OFFSET with LIMIT to skip a specific number of rows.

Syntax:

SELECT column1, column2
FROM table_name
LIMIT number OFFSET number_to_skip;

Example: Skip the first 5 employees and show the next 5

SELECT first_name, last_name
FROM employees
LIMIT 5 OFFSET 5;

• OFFSET 5 skips the first 5 records.
• LIMIT 5 then retrieves the next 5 records.

This technique is extremely useful for pagination (showing page 2, page 3, etc., in web applications).

Using LIMIT with Different SQL Databases

Example in SQL Server (alternative):

SELECT TOP 5 first_name, last_name
FROM employees;

Best Practices When Using LIMIT

• Always combine ORDER BY with LIMIT if you want predictable results.
• Use LIMIT when sampling data for reports or testing.
• For large data sets, optimize your queries to avoid unnecessary data load even when using LIMIT.
• Be cautious when using LIMIT without ORDER BY — results can be unpredictable because SQL tables don't guarantee row order unless sorted.

Conclusion

The LIMIT clause is a simple but powerful SQL feature that controls the number of results returned. By combining LIMIT, OFFSET, and ORDER BY, you can efficiently retrieve, paginate, and display data exactly as you need it.