SQL workshop: LIMIT statement

The LIMIT statement in SQL is used to constrain the number of rows returned by a query. This is particularly useful for pagination in applications where you may want to display only a specific number of records at a time. The LIMIT clause can also be combined with the OFFSET clause to skip a certain number of rows before starting to return the rows.

Basic Syntax

SELECT columns
FROM table
WHERE conditions
LIMIT number_of_rows;
With OFFSET:
SELECT columns
FROM table
WHERE conditions
LIMIT number_of_rows OFFSET starting_row;

Examples:

  1. Select the first 10 rows from the employees table:

     SELECT * FROM employees
     LIMIT 10;
    
  2. Skip the first 5 rows, then select the next 10 rows from the employees table:

     SELECT * FROM employees
     LIMIT 10 OFFSET 5;
    

Workshop Exercise: Using LIMIT in MySQL

Environment Setup

  1. Open your MySQL terminal or MySQL interface.

    💡
    You can also use DBeaver GUI client to access the database. In case of a connection error use the following guide: Troubleshoot Access Denied Error: Connect DBeaver to a local MySQL database
  2. Create a database:

     CREATE DATABASE workshopDB;
    
  3. Use the database:

     USE workshopDB;
    
  4. Create an employees table:

     CREATE TABLE employees (
         id INT PRIMARY KEY AUTO_INCREMENT,
         first_name VARCHAR(50),
         last_name VARCHAR(50),
         salary INT
     );
    
  5. Insert some data:

     INSERT INTO employees (first_name, last_name, salary)
     VALUES ('John', 'Doe', 55000),
            ('Jane', 'Smith', 60000),
            ('Bob', 'Johnson', 57000),
            ('Emily', 'Brown', 62000),
            ('Anna', 'Williams', 58000),
            ('Jack', 'White', 63000);
    

Exercises

  1. Basic LIMIT: Retrieve the first 3 records from the employees table.

     SELECT * FROM employees LIMIT 3;
    

    What are the names and salaries of these employees?

    Solution:

  2. LIMIT with OFFSET: Skip the first 2 records and then retrieve the next 3 records from the employees table.

     SELECT * FROM employees LIMIT 3 OFFSET 2;
    

    What are the names and salaries of these employees?

    Solution:

  3. No OFFSET: Use LIMIT without an OFFSET. Retrieve the first 2 records from the employees table.

     SELECT * FROM employees LIMIT 2;
    

    What are the names and salaries of these employees?

    Solution:

  4. Challenge: Using LIMIT and OFFSET, can you paginate through the table? Fetch the first 2 employees, then the next 2, and so on.

    First Page:

     SELECT * FROM employees LIMIT 2 OFFSET 0;
    

    Second Page:

     SELECT * FROM employees LIMIT 2 OFFSET 2;
    

    And so on...

Solution:
First pagination

Second pagination

Best Practices of using LIMIT statement

Below are some best practices and warnings you should be aware of:

  1. Combine with Sorting: When you're using LIMIT, make sure to always include an ORDER BY clause unless the order of records doesn't matter. Without an explicit order, the database returns rows in an undefined order.

     SELECT * FROM employees
     ORDER BY salary DESC
     LIMIT 10;
    
  2. Use Pagination Carefully: When using LIMIT with OFFSET for pagination, remember that as the offset grows, the query can slow down, especially on large tables. This is because the database must find and skip the OFFSET number of rows before returning the remaining rows.

  3. Consider Using Indexed Columns in Sorting: When you're sorting data using ORDER BY, using indexed columns can make your query faster, especially when used with LIMIT.

  4. Avoid SELECT *: When using LIMIT, you're often displaying data. Choose only the columns you need to improve performance and reduce network load.

  5. Calculate OFFSET Dynamically: Instead of hardcoding the OFFSET value, you can calculate it dynamically based on the page number and the number of records per page. For example, OFFSET = (page_number - 1) * records_per_page.

  6. Batch Operations: If you have to delete or update many rows in a large table, using LIMIT can help to batch the operation and avoid locking the table for a long time.

     DELETE FROM large_table WHERE some_condition LIMIT 1000;
    

Warnings

  1. Non-deterministic Results: If the ORDER BY columns have non-unique values, using LIMIT may return non-deterministic results. In other words, you might not get the same set of rows for the same LIMIT and OFFSET values.

  2. Database Variations: The implementation and syntax of LIMIT can vary between different database systems. For example, SQL Server uses TOP instead of LIMIT. (How To Limit The Number Of Rows Returned In SQL Server)

  3. Inconsistent Performance: The performance may not be consistent for varying limits and offsets. As the OFFSET increases, some databases might slow down significantly.

  4. Resource Usage: Using a very large LIMIT value may consume a lot of memory and slow down the system. Always balance the LIMIT against available resources.

  5. Write Operations: Be extra careful while using LIMIT with write operations like UPDATE or DELETE, especially without a WHERE clause, as you might end up updating or deleting a subset of records that you didn't intend to.

  6. Concurrency Issues: When using LIMIT for pagination, be aware that if data is being added or removed, the pages may shift, resulting in duplicate or missed records across pages.

By understanding these considerations, you can use the LIMIT statement more effectively and avoid some common pitfalls.

References

  1. SQL TOP, LIMIT, FETCH FIRST or ROWNUM Clause