Limit and Offset

In this topic, we will cover the Limit and Offset statements. Both are useful tools for retrieving only a specific portion of your data. When WHERE is used to specify what data to retrieve, LIMIT will help you indicate how much of it you'd like to get. The OFFSET statement will help you navigate in the resulting snippet, providing the row from which you'd like to start viewing the data.

The most common usage of these functions is, probably, in ad-hoc analytics. So, when you'd like to view a random snippet of data in a table or to select its top rows ordered by some value — these will come in handy. However, it is also widely used in ETL processes and data streaming to control the size of the streams and their order.

Limit

Imagine we have a table where we store data on our marketing campaigns. Now, our marketing manager asked us to look at the top-10 most successful campaigns. Here is a perfectly fine query for this task:

SELECT 
     campaign_name,
     started_at,
     visits,
     transactions,
     traffic_cost,
     profit,
     profit/traffic_cost as ROI
FROM marketing_campaigns
ORDER BY profit/traffic_cost DESC;

This query will provide you with all the necessary info. Probably some of you wrote a lot of similar queries, especially those who use database administration tools like Dbeaver or MySQL Workbench. However, there are some problems with it. You see, most database administration tools will include the LIMIT statement despite you not writing it. In these tools, the SELECT query is an instrument mostly used for viewing a snippet of your data. The output size for your query will be limited to some default value of the tool.

On the other hand, when you connect directly to the database, there is no implicit limitation on the output. When we're talking millions of rows this could cause significant performance issues. By adding the LIMIT statement we signal our database to stop gathering info at the time when the required amount of rows is reached.

So, the following query will provide you with the required info without any data you don't need:

SELECT 
     campaign_name,
     started_at,
     visits,
     transactions,
     traffic_cost,
     profit,
     profit/traffic_cost as ROI
FROM marketing_campaigns
ORDER BY profit/traffic_cost DESC
LIMIT 10;

You can see that the LIMIT statement is placed at the end of the query. In this case, the placement is easy to understand — we're limiting the output of the query. So, other commands are both placed and run before LIMIT for us to have the output to limit.

It is important to note that in most DBMSs, you won't get the same result every time you use queries with LIMIT if you don't provide any ORDER BY clause. The LIMIT clause by itself won't help with any sorting for your data. In these cases, LIMIT can only be used to retrieve random snippets of your data with predefined size.

OFFSET

The OFFSET statement tells your DBMS to skip a certain amount of rows before beginning to return data.

Let's go back to our example of successful campaigns. Suppose now you need to get the top 5 most successful campaigns that are not in the top 10. We can do that using OFFSET 10.

SELECT 
     campaign_name,
     started_at,
     visits,
     transactions,
     traffic_cost,
     profit,
     profit/traffic_cost as ROI
FROM marketing_campaigns
ORDER BY profit/traffic_cost DESC
LIMIT 5
OFFSET 10;

When using both LIMIT and OFFSET, the OFFSET rows are skipped before starting to count limited rows that are returned.

So, we can rewrite our previous query this way:

SELECT 
     campaign_name,
     started_at,
     visits,
     transactions,
     traffic_cost,
     profit,
     profit/traffic_cost as ROI
FROM marketing_campaigns
ORDER BY profit/traffic_cost DESC
LIMIT 10, 5;

Conclusion

LIMIT and OFFSET are key statements in most SQL syntaxes.

Now you know how to use them for:

• Limiting query's output
• Retrieving random and specific snippets of data
• Pagination
• Learning about your top performers

Also, it'd be good to remember the following points:

• LIMIT by itself won't help with sorting your data — you should include the ORDER BY clause for sorting and getting the top or the lowest rows.
• LIMIT is performed after GROUP BY and you should not use it for optimization in grouping queries. It would still limit their output though.
• You can use the LIMIT {offset value}, {limit value} clause and get the same result as when using both statements separately.
• Your SQL syntax might have another name for a statement with the same effect as LIMIT's but is still there.