SQL | LIMIT Clause

The LIMIT clause in SQL is used to constrain the number of rows returned by a query. This can be particularly useful in scenarios where you want to retrieve a subset of records, such as pagination. The behavior and syntax might slightly differ based on the database system you're using.

1. Basic Usage:

MySQL, PostgreSQL, SQLite, and MariaDB:

To fetch the first 10 rows:

SELECT * FROM table_name LIMIT 10;

2. Offset:

You can also specify an offset to indicate where to start the retrieval. This is particularly useful for pagination.

MySQL, PostgreSQL, SQLite, and MariaDB:

To fetch 10 rows, starting from the 11th row:

SELECT * FROM table_name LIMIT 10 OFFSET 10;

Or, using an alternative syntax (especially in MySQL):

SELECT * FROM table_name LIMIT 10, 10;

Here, the first number after LIMIT is the offset, and the second number is the count of rows to retrieve.

3. Database Specifics:

SQL Server:

SQL Server doesn't have a LIMIT keyword. Instead, you can use TOP or the OFFSET ... FETCH clauses:

-- Using TOP
SELECT TOP 10 * FROM table_name;

-- Using OFFSET and FETCH
SELECT * FROM table_name 
OFFSET 10 ROWS 
FETCH NEXT 10 ROWS ONLY;

Oracle:

Oracle doesn't have a LIMIT keyword either. For pagination, you would use the ROWNUM or, in Oracle 12c and later, the FETCH and OFFSET clauses:

-- Using ROWNUM
SELECT * FROM (SELECT * FROM table_name WHERE ROWNUM <= 20) WHERE ROWNUM >= 11;

-- Using OFFSET and FETCH (Oracle 12c and later)
SELECT * FROM table_name 
OFFSET 10 ROWS 
FETCH NEXT 10 ROWS ONLY;

Note:

When using the LIMIT clause, especially for pagination, it's a good practice to use an ORDER BY clause to ensure consistent ordering, as databases don't guarantee an inherent order of rows unless you specify it.

SQL LIMIT clause examples:
- Description: The LIMIT clause is used in SQL to restrict the number of rows returned by a query. It is commonly used for pagination or limiting the result set for performance reasons.
- Example Code:
```
SELECT * FROM employees
LIMIT 10;
```
How to use LIMIT in SQL:
- Description: The LIMIT clause is used at the end of a SELECT statement to limit the number of rows returned.
- Example Code:
```
SELECT * FROM orders
LIMIT 5;
```
Retrieving a specific number of rows with LIMIT:
- Description: You can use LIMIT to retrieve a specific number of rows from the beginning of the result set.
- Example Code:
```
SELECT * FROM products
LIMIT 20;
```
Using OFFSET with LIMIT in SQL:
- Description: OFFSET is often used in combination with LIMIT to skip a certain number of rows before returning the specified number of rows.
- Example Code:
```
SELECT * FROM customers
OFFSET 10
LIMIT 5;
```
LIMIT vs. FETCH FIRST in SQL:
- Description: FETCH FIRST is an alternative syntax used in some databases (e.g., IBM Db2) to achieve the same result as LIMIT.
- Example Code:
```
SELECT * FROM orders
FETCH FIRST 5 ROWS ONLY;
```
LIMIT with ORDER BY in SQL:
- Description: You can use ORDER BY in conjunction with LIMIT to retrieve a specified number of rows based on a particular order.
- Example Code:
```
SELECT * FROM products
ORDER BY unit_price DESC
LIMIT 10;
```
Dynamic values for LIMIT in SQL:
- Description: You can use variables or parameters to dynamically set the limit value in a query.
- Example Code:
```
DECLARE @limit INT = 5;
SELECT * FROM customers
LIMIT @limit;
```
LIMIT in subqueries in SQL:
- Description: LIMIT can be used within subqueries to limit the number of rows returned by the subquery.
- Example Code:
```
SELECT * FROM orders
WHERE customer_id IN (SELECT customer_id FROM customers LIMIT 5);
```
Combining LIMIT with WHERE clause in SQL:
- Description: You can use LIMIT along with the WHERE clause to filter and limit the number of rows simultaneously.
- Example Code:
```
SELECT * FROM employees
WHERE department_id = 2
LIMIT 10;
```
Limiting results with GROUP BY in SQL:
- Description: LIMIT can be used in conjunction with GROUP BY to limit the number of rows for each group.
- Example Code:
```
SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
LIMIT 5;
```
SQL FETCH FIRST vs. TOP vs. LIMIT:
- Description: Different databases may use different syntax for limiting rows, such as FETCH FIRST (IBM Db2), TOP (Microsoft SQL Server), or LIMIT (used by several databases).
- Example Code:
```
-- Microsoft SQL Server (TOP)
SELECT TOP 5 * FROM products;

-- IBM Db2 (FETCH FIRST)
SELECT * FROM orders
FETCH FIRST 5 ROWS ONLY;
```
Alternatives to LIMIT for row limiting in SQL:
- Description: Depending on the database system, alternatives to LIMIT may include FETCH FIRST, TOP, or other specific syntax.
- Example Code:
```
-- Alternative to LIMIT in PostgreSQL
SELECT * FROM customers
OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;
```
Handling NULL values with LIMIT:
- Description: When using LIMIT, it doesn't directly handle NULL values. You can use the WHERE clause to filter out rows with NULL values if needed.
- Example Code:
```
SELECT * FROM products
WHERE product_name IS NOT NULL
LIMIT 10;
```