The LIMIT Clause

Definition: The LIMIT clause is used to specify the maximum number of records the result-set will return. It acts as a "cutoff" point for your query results.

Practical Application: Practical SQL tutorials include LIMIT in basic lessons because it is essential for performance. When working with databases containing millions of rows, you often only need a small sample or the "top" few results to verify your query is working correctly.

Syntax

The LIMIT keyword is placed at the very end of the SQL statement (after the ORDER BY clause, if one is present).

SELECT column_name
FROM table_name
LIMIT number;

Example: Restricted Results

If you have a large students table but only want to see the first two records for a quick check, you would use the following:

SELECT *
FROM students
LIMIT 2;

Common Use Cases

• Pagination: Websites use LIMIT to show only 10 or 20 results per page rather than loading the entire database at once.
• Top N Records: When combined with ORDER BY, you can find the "Top 5 Highest Scores" or "3 Most Recent Orders."
• Testing: Developers use LIMIT 1 or LIMIT 5 when testing complex queries to ensure the syntax is correct without waiting for a massive data transfer.

Key Notes

• Database Variation: While LIMIT is standard in MySQL, PostgreSQL, and SQLite, some other databases use different keywords for the same task (e.g., SQL Server uses TOP and Oracle uses FETCH FIRST).
• Position: In a full query, the order of clauses must be: SELECT → FROM → WHERE → ORDER BY → LIMIT.
• Offset: You can also skip a certain number of rows before starting the limit by using an "offset" (e.g., LIMIT 5 OFFSET 10 would show 5 rows starting from the 11th record).