LIMIT Clause

Databases can hold thousands, millions, or billions of records. Most of the time, you don't need all of them. Maybe you just want the first few, the top performers, or a quick preview while testing.

Real examples:

• Your shopping app shows 10 products per page, not 1 million all at once.
• Twitter-like apps load 20 posts, then 20 more. That's pagination.
• Your dashboard shows the top 5 earners, not everyone's paycheck.
• You're testing a query and need a quick sample, not the entire dataset.

Pulling everything when you need just a bit is wasteful. It kills performance, burns memory, and frustrates users.

To solve this problem, SQL provides the LIMIT Clause.

The LIMIT clause caps how many rows come back from your query. It's everywhere: web apps, dashboards, reports, pagination.

Why is LIMIT Important?

Picture a Products table with:

500,000 Products

If you run this without LIMIT:

You get all 500k rows back.

That's bad:

• Slow execution
• Increased memory usage
• Higher network traffic
• Poor user experience

Now use LIMIT:

Just 10 rows.

Much better:

• Faster queries
• Better performance
• Reduced resource consumption
• Improved application responsiveness

Basic LIMIT Syntax

The general syntax is:

Example:

Only three rows are returned.

Understanding the Syntax

Here's what each part does:

SELECT — Gets your data.

FROM — Which table.

LIMIT — How many rows max.

3 — The number: stop after 3.

The database will hand you 3 rows and stop.

Creating a Sample Table

Let's set up a test table:

Add some students:

Your table now looks like this:

Retrieving the First Few Rows

This query:

Gives you:

Just the first two rows. Simple.

LIMIT with ORDER BY

LIMIT really shines when you pair it with ORDER BY. This is where the magic happens.

Watch:

Process:

Result:

The three oldest students are returned.

Finding Top Paid Employees

Say you need the top earners:

You get:

The five employees with the highest salaries are displayed.

This is a common business reporting requirement.

LIMIT with WHERE

You can mix LIMIT with WHERE to be even more surgical.

Like this:

Process:

Only matching records are considered.

LIMIT with Multiple Conditions

Getting fancy:

Returns:

The top three highest-paid IT employees are displayed.

Using LIMIT 1

Sometimes you just need one.

Like checking if something exists:

Hands back:

Only the first row is returned.

Handy for:

• Testing queries
• Checking table structure
• Retrieving a single record

LIMIT with OFFSET

OFFSET skips ahead. Combined with LIMIT, it's the backbone of pagination.

Here's the pattern:

Process:

This is widely used in pagination systems.

Pagination Example

Think of a store with product pages:

Page 1:

Page 2:

Page 3:

That's how you scroll through pages without loading everything.

LIMIT in Different Database Systems

Different databases have different keywords. Here's the rundown:

MySQL

PostgreSQL

SQLite

SQL Server

Uses TOP (we'll cover this next):

Oracle

Uses FETCH (more on this later):

Real-World Example

Let's say you're building an online store homepage.

You've got a Products table with:

1,000,000 Products

Your homepage needs:

Your query:

You show the freshest 20.

Performance stays snappy instead of crawling.

Common Errors

Forgetting ORDER BY

If you do this:

You'll get 5 rows, but which 5? Nobody knows. Could be random.

Add ORDER BY when it matters:

so results are predictable.

Negative LIMIT Values

This won't work:

Databases will throw an error. Negative limits don't make sense.

Assuming LIMIT Sorts Data

Mistake:

LIMIT just caps the count.

Sorting requires:

Using Large LIMIT Values

This is pointless:

If you're capping at a million rows, you've beat the whole point.

Best Practices

Combine LIMIT with ORDER BY

Do this:

Use Pagination for Large Datasets

For big datasets:

It feels faster to the user.

Retrieve Only Required Columns

Better:

Don't pull stuff you won't use. Saves bandwidth.

Use Small Limits During Testing

When you're testing:

Grab 5 rows fast so you can see if your logic works.

Common Interview Questions

What is the purpose of LIMIT?

It caps how many rows come back. Useful for performance and pagination.

Can LIMIT be used with ORDER BY?

Absolutely.

It's super common — sorting first, then grabbing the top N.

What is OFFSET?

It skips ahead. Skip 10, return the next 5 — that's how pagination works.

Does LIMIT sort data?

Nope.

You need ORDER BY for sorting. LIMIT just stops after N rows.

Summary

LIMIT is essential. It restricts output, speeds up queries, and powers pagination everywhere online.

In this lesson, you learned:

• What LIMIT is
• Why LIMIT is important
• Basic syntax
• LIMIT with ORDER BY
• LIMIT with WHERE
• LIMIT with OFFSET
• Pagination
• Database-specific alternatives
• Common mistakes
• Best practices

You'll see LIMIT in almost every real application because showing everything at once is a bad user experience. Break it into pages.

Next Step

Continue to the next lesson:

TOP Clause →