What are LIMIT and OFFSET?

-- Return only the first n rows
SELECT * FROM table LIMIT n;

-- Skip offset rows, then return limit rows
SELECT * FROM table LIMIT n OFFSET offset;

LIMIT Example

-- Get top 3 highest salaries
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

LIMIT with OFFSET

-- Skip first 3, return next 3
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3 OFFSET 3;

OFFSET Visualization

Pagination Pattern

-- Page 1 (rows 1-10)
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 0;

-- Page 2 (rows 11-20)
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10;

-- Page 3 (rows 21-30)
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;

-- Page N formula: OFFSET = (N-1) * page_size
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 40;  -- Page 5

Keyset Pagination

-- OFFSET pagination (slow for large offsets)
SELECT * FROM orders
ORDER BY id
LIMIT 10 OFFSET 100000;  -- Must scan 100,000 rows!

-- Keyset pagination (consistent performance)
SELECT * FROM orders
WHERE id > 100000  -- Use last seen ID
ORDER BY id
LIMIT 10;          -- Always fast!

Database Differences

-- MySQL / PostgreSQL / SQLite
SELECT * FROM products LIMIT 10 OFFSET 20;

-- SQL Server
SELECT TOP 10 * FROM products;
SELECT * FROM products
ORDER BY id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

-- Oracle 12c+
SELECT * FROM products
FETCH FIRST 10 ROWS ONLY;

SELECT * FROM products
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

LIMIT with Subqueries

-- Top 5 products by total sales
SELECT product_id, SUM(quantity) as total_sold
FROM order_items
GROUP BY product_id
ORDER BY total_sold DESC
LIMIT 5;

LIMIT with CTE

-- Get top 10 customers by order total
WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(total) as total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT
    c.name,
    ct.total_spent
FROM customer_totals ct
JOIN customers c ON ct.customer_id = c.id
ORDER BY ct.total_spent DESC
LIMIT 10;

LIMIT with Other Clauses

-- LIMIT with WHERE
SELECT * FROM products
WHERE price > 50
ORDER BY price DESC
LIMIT 5;

-- LIMIT with GROUP BY
SELECT city, COUNT(*) as customer_count
FROM customers
GROUP BY city
ORDER BY customer_count DESC
LIMIT 10;

-- LIMIT with HAVING
SELECT city, COUNT(*) as customer_count
FROM customers
GROUP BY city
HAVING COUNT(*) > 5
ORDER BY customer_count DESC
LIMIT 5;

Performance Considerations

-- 1. Add index on ORDER BY column
CREATE INDEX idx_orders_date ON orders(order_date);

-- 2. Use covering index
CREATE INDEX idx_orders_covering
ON orders(order_date, id, customer_id, total);

-- 3. Keyset pagination for large offsets
-- Instead of:
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 100000;

-- Use:
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

Common Mistakes

-- BAD: LIMIT without ORDER BY
SELECT * FROM products LIMIT 10;

-- GOOD: Add ORDER BY for predictable results
SELECT * FROM products ORDER BY id LIMIT 10;

-- BAD: Large OFFSET for deep pages
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 100000;

-- GOOD: Use keyset pagination
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

-- BAD: Using LIMIT in UPDATE/DELETE (database specific)
UPDATE products SET price = price * 1.1 LIMIT 10;  -- MySQL only

-- GOOD: Use subquery for portability
UPDATE products SET price = price * 1.1
WHERE id IN (SELECT id FROM products ORDER BY id LIMIT 10);

Practice Exercises

Exercise 1: Find the 5 most expensive products from a products table.

SELECT * FROM products
ORDER BY price DESC
LIMIT 5;

Exercise 2: Get page 3 of customers (10 per page), ordered by last name.

SELECT * FROM customers
ORDER BY last_name ASC
LIMIT 10 OFFSET 20;

Exercise 3: Find the 10 most recent orders.

SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 10;

Exercise 4: Get the second page of products (5 per page) in the 'Electronics' category.

SELECT * FROM products
WHERE category = 'Electronics'
ORDER BY name ASC
LIMIT 5 OFFSET 5;