OFFSET Clause

Much like the LIMIT clause specifies the number of records to be returned, the OFFSET clause specifies the number of records to skip when returning the output. The output will have $n$ skipped records and display the remaining records.

Let's demonstrate this concept more practically. Let's begin by writing a query that returns 10 rows of data from the customers table. We will use the LIMIT clause as we did in the previous section.

SELECT *
FROM customers_new
LIMIT 10

OUTPUT| customer_id | first_name | last_name | email | city | country | registration_date | |-------------|------------|------------|------------------------------|-------------|-------------|-------------------| | 2001 | Alice | Johnson | [email protected] | New York | USA | 2023-01-15 | | 2002 | Bob | Williams | [email protected] | London | UK | 2023-02-20 | | 2003 | Carol | Brown | [email protected] | Toronto | Canada | 2023-03-10 | | 2004 | David | Davis | [email protected] | Sydney | Australia | 2023-04-05 | | 2005 | Emma | Miller | [email protected] | Paris | France | 2023-05-12 | | 2006 | Frank | Wilson | [email protected] | Berlin | Germany | 2023-06-18 | | 2007 | Grace | Moore | [email protected] | Tokyo | Japan | 2023-07-22 | | 2008 | Henry | Taylor | [email protected] | Madrid | Spain | 2023-08-30 | | 2009 | Ivy | Anderson | [email protected] | Rome | Italy | 2023-09-14 | | 2010 | Jack | Thomas | [email protected] | Amsterdam | Netherlands | 2023-10-08 |

Now, let's add an OFFSET clause specifying that we would like to skip the first 5 rows.

select *
from customers
limit 5
offset 5

OUTPUT| customer_id | first_name | last_name | email | city | country | registration_date | |-------------|------------|------------|------------------------------|-------------|-------------|-------------------| | 2006 | Frank | Wilson | [email protected] | Berlin | Germany | 2023-06-18 | | 2007 | Grace | Moore | [email protected] | Tokyo | Japan | 2023-07-22 | | 2008 | Henry | Taylor | [email protected] | Madrid | Spain | 2023-08-30 | | 2009 | Ivy | Anderson | [email protected] | Rome | Italy | 2023-09-14 | | 2010 | Jack | Thomas | [email protected] | Amsterdam | Netherlands | 2023-10-08 |

Notice that the output is still 5 rows of data but it is skipped the first 5 rows of the output from the previous examples and only returned the next 5 rows from the full output.