{"id":32770,"date":"2023-10-16T11:54:15","date_gmt":"2023-10-16T01:54:15","guid":{"rendered":"https:\/\/database.guide\/?p=32770"},"modified":"2023-10-16T11:54:16","modified_gmt":"2023-10-16T01:54:16","slug":"understanding-the-limit-clause-in-sql","status":"publish","type":"post","link":"https:\/\/database.guide\/understanding-the-limit-clause-in-sql\/","title":{"rendered":"Understanding the LIMIT Clause in SQL"},"content":{"rendered":"\n

Some of the major relational database management systems (RDBMS<\/a>s) have a LIMIT<\/code> clause that enables us to reduce the number of rows returned by a query. <\/p>\n\n\n\n

The way it works is that we provide the number of rows we want to be returned by the query. We can also provide an offset to specify which row to start the count from.<\/p>\n\n\n\n\n\n\n\n

Example<\/h2>\n\n\n\n

Suppose we have a table with the following data:<\/p>\n\n\n\n

SELECT\n    ProductId,\n    ProductName\nFROM Products\nORDER BY ProductId;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------+---------------------------------+\n| ProductId | ProductName                     |\n+-----------+---------------------------------+\n|         1 | Left handed screwdriver         |\n|         2 | Right handed screwdriver        |\n|         3 | Long Weight (blue)              |\n|         4 | Long Weight (green)             |\n|         5 | Sledge Hammer                   |\n|         6 | Chainsaw                        |\n|         7 | Straw Dog Box                   |\n|         8 | Bottomless Coffee Mugs (4 Pack) |\n|         9 | Hammock                         |\n|        10 | Tea Pot                         |\n+-----------+---------------------------------+\n10 rows in set (0.11 sec)<\/pre>\n\n\n\n
This query returned ten rows.<\/p>\n\n\n\n
We can use the LIMIT<\/code> clause to reduce the number of rows returned:<\/p>\n\n\n\n
SELECT\n    ProductId,\n    ProductName\nFROM Products\nORDER BY ProductId\nLIMIT 3;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------+--------------------------+\n| ProductId | ProductName              |\n+-----------+--------------------------+\n|         1 | Left handed screwdriver  |\n|         2 | Right handed screwdriver |\n|         3 | Long Weight (blue)       |\n+-----------+--------------------------+\n3 rows in set (0.00 sec)<\/pre>\n\n\n\n
Using an Offset<\/h2>\n\n\n\n
Most (if not all) RDBMSs that provide a LIMIT<\/code> clause allow us to skip rows at the beginning of the output. We do this by specifying an offset.<\/p>\n\n\n\n
This is probably explained best by an example:<\/p>\n\n\n\n
SELECT\n    ProductId,\n    ProductName\nFROM Products\nORDER BY ProductId\nLIMIT 3 OFFSET 2;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------+---------------------+\n| ProductId | ProductName         |\n+-----------+---------------------+\n|         3 | Long Weight (blue)  |\n|         4 | Long Weight (green) |\n|         5 | Sledge Hammer       |\n+-----------+---------------------+\n3 rows in set (0.00 sec)<\/pre>\n\n\n\n
In this case I specified an offset of 2<\/code>. This meant that the LIMIT<\/code> conditions were applied after skipping two rows. <\/p>\n\n\n\n
So in this example we still got three rows. We just skipped two rows before counting the three rows. So we didn’t get products 1 through 3 like in the previous example. We got products 3 through 5 instead.<\/p>\n\n\n\n
Some RDBMSs (such as MySQL, MariaDB, and SQLite) accept a shortened form for including offset. In such cases we can rewrite the above query as follows:<\/p>\n\n\n\n
SELECT\n    ProductId,\n    ProductName\nFROM Products\nORDER BY ProductId\nLIMIT 2, 3;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------+---------------------+\n| ProductId | ProductName         |\n+-----------+---------------------+\n|         3 | Long Weight (blue)  |\n|         4 | Long Weight (green) |\n|         5 | Sledge Hammer       |\n+-----------+---------------------+\n3 rows in set (0.06 sec)<\/pre>\n\n\n\n
So in this case the offset is the first value, and the row count limit is the second.<\/p>\n\n\n\n
Alternatives to the LIMIT<\/code> Clause<\/h2>\n\n\n\n
The LIMIT<\/code> clause is not supported in all RDBMSs. However, some RDBMSs provide an alternative method for limiting the rows returned by a query, and for setting an offset.<\/p>\n\n\n\n
In SQL Server, we can include TOP(n<\/em>)<\/code> in our SELECT<\/code> list to return the top n<\/em> rows<\/a>. SQL Server also supports a FETCH<\/code> and OFFSET<\/code> clause that can be used to limit the rows returned with an offset. We can use these two clauses to implement pagination functionality<\/a>.<\/p>\n\n\n\n
Oracle Database also implements a FETCH<\/code> row limiting clause<\/a> (which includes an OFFSET<\/code> clause).<\/p>\n","protected":false},"excerpt":{"rendered":"
Some of the major relational database management systems (RDBMSs) have a LIMIT clause that enables us to reduce the number of rows returned by a query. The way it works is that we provide the number of rows we want to be returned by the query. We can also provide an offset to specify which … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[15,20],"class_list":["post-32770","post","type-post","status-publish","format-standard","hentry","category-sql","tag-create-query","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/32770","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=32770"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/32770\/revisions"}],"predecessor-version":[{"id":32856,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/32770\/revisions\/32856"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=32770"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=32770"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=32770"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}