LIMIT Clause

When working with large tables, returning all rows is often unnecessary and inefficient. The LIMIT clause allows us to control how many rows a query returns.

This is especially useful for previews, dashboards, reports, and performance optimization.

What is LIMIT?

The LIMIT clause restricts the number of rows returned by a SELECT query.

Basic syntax:

SELECT column_name
FROM table_name
LIMIT number;
  

The database returns only the specified number of rows.

Why LIMIT is Important

• Improves query performance
• Makes results easier to read
• Helps preview large datasets
• Commonly used in pagination

Example Table

We will continue using the employees table:

+----+----------+------------+--------+
| id | name     | department | salary |
+----+----------+------------+--------+
| 1  | Alice    | HR         | 60000  |
| 2  | Bob      | IT         | 75000  |
| 3  | Charlie  | Finance    | 70000  |
| 4  | Diana    | IT         | 80000  |
| 5  | Evan     | HR         | 58000  |
| 6  | Fiona    | Finance    | 72000  |
+----+----------+------------+--------+
  

Limiting Rows

To retrieve only the first 3 rows from the table:

SELECT * FROM employees
LIMIT 3;
  

The database stops returning rows after reaching the limit.

LIMIT with ORDER BY

LIMIT is most powerful when combined with ORDER BY.

Example: Get the top 3 highest-paid employees.

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

Important rule:

• ORDER BY sorts the data
• LIMIT restricts the final result

LIMIT with OFFSET

OFFSET allows you to skip a certain number of rows before returning results. This is commonly used for pagination.

Syntax:

SELECT column_name
FROM table_name
LIMIT limit OFFSET offset;
  

Example: Skip the first 2 rows and return the next 3.

SELECT name, department
FROM employees
ORDER BY id
LIMIT 3 OFFSET 2;
  

LIMIT vs OFFSET Explained

Common Beginner Mistakes

• Using LIMIT without ORDER BY (results may be unpredictable)
• Confusing OFFSET with LIMIT
• Assuming LIMIT changes the table data

LIMIT only affects the output — it never modifies stored data.

Real-World Use Cases

• Displaying top products by sales
• Showing recent transactions
• Building paginated tables
• Previewing large datasets

What’s Next?

In the next lesson, we will learn how to remove duplicate values from results using the DISTINCT keyword.