**Course Title:** SQL Mastery: From Fundamentals to Advanced Techniques **Section Title:** Data Retrieval with SQL: SELECT Queries **Topic:** Limiting the result set with LIMIT and OFFSET ### Overview of LIMIT and OFFSET In the previous topics, we explored how to query data using the SELECT statement, filter results with WHERE, AND, OR, and NOT, and sort results with ORDER BY. However, sometimes you may want to limit the number of rows returned by a query or skip a certain number of rows before starting to return rows. This is where the LIMIT and OFFSET clauses come in. ### The LIMIT Clause The LIMIT clause is used to limit the number of rows returned by a query. It takes one argument, which is the maximum number of rows to return. The LIMIT clause is useful when you want to retrieve a small number of rows from a large table or when you want to implement pagination in your application. **Example 1:** Retrieve the first 10 rows from the `employees` table. ```sql SELECT * FROM employees LIMIT 10; ``` This query will return the first 10 rows from the `employees` table. Note that the order of the rows is not guaranteed unless you use the ORDER BY clause. ### The OFFSET Clause The OFFSET clause is used to skip a certain number of rows before starting to return rows. It takes one argument, which is the number of rows to skip. The OFFSET clause is often used in conjunction with the LIMIT clause. **Example 2:** Retrieve rows 11-20 from the `employees` table. ```sql SELECT * FROM employees OFFSET 10 LIMIT 10; ``` This query will skip the first 10 rows from the `employees` table and then return the next 10 rows. ### Combining LIMIT and OFFSET You can combine the LIMIT and OFFSET clauses to implement pagination in your application. **Example 3:** Retrieve rows 11-20 from the `employees` table, ordered by `employee_id`. ```sql SELECT * FROM employees ORDER BY employee_id OFFSET 10 LIMIT 10; ``` This query will order the `employees` table by `employee_id`, skip the first 10 rows, and then return the next 10 rows. ### Key Concepts to Remember * The LIMIT clause limits the number of rows returned by a query. * The OFFSET clause skips a certain number of rows before starting to return rows. * Combine LIMIT and OFFSET to implement pagination in your application. * Use the ORDER BY clause to guarantee the order of the rows. ### Practical Takeaways * Use LIMIT to retrieve a small number of rows from a large table. * Use OFFSET to skip a certain number of rows before starting to return rows. * Combine LIMIT and OFFSET to implement pagination in your application. ### Try it Out Open your favorite SQL client or IDE, and try out the examples above. Experiment with different values for LIMIT and OFFSET to see how they work. ### Additional Resources * [W3Schools LIMITClause tutorial](https://www.w3schools.com/sql/sql_limit.asp) * [MySQL LIMIT Clause documentation](https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html) * [PostgreSQL LIMIT Clause documentation](https://www.postgresql.org/docs/current/queries-limit.html) If you have any questions or need help with the examples above, feel free to leave a comment below. In the next topic, we will explore **Using aggregate functions: COUNT, SUM, AVG, MIN, MAX**, from SQL Functions and Operators.