|The jOOQ User Manual. Multiple Pages : SQL building : SQL Statements : The SELECT statement : The LIMIT .. OFFSET clause||previous : next|
While being extremely useful for every application that does paging, or just to limit result sets to reasonable sizes, this clause is not yet part of any SQL standard (up until SQL:2008). Hence, there exist a variety of possible implementations in various SQL dialects, concerning this limit clause. jOOQ chose to implement the LIMIT .. OFFSET clause as understood and supported by MySQL, H2, HSQLDB, Postgres, and SQLite. Here is an example of how to apply limits with jOOQ:
This will limit the result to 1 books starting with the 2nd book (starting at offset 0!). limit() is supported in all dialects, offset() in all but Sybase ASE, which has no reasonable means to simulate it. This is how jOOQ simulates the above query in various SQL dialects:
-- MySQL, H2, HSQLDB, Postgres, and SQLite SELECT * FROM BOOK LIMIT 1 OFFSET 2 -- CUBRID supports a MySQL variant of the LIMIT .. OFFSET clause SELECT * FROM BOOK LIMIT 2, 1 -- Derby SELECT * FROM BOOK OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY -- Ingres SELECT * FROM BOOK OFFSET 2 FETCH FIRST 1 ROWS ONLY -- Firebird SELECT * FROM BOOK ROWS 2 TO 3 -- Sybase SQL Anywhere SELECT TOP 1 ROWS START AT 3 * FROM BOOK -- DB2 (without OFFSET) SELECT * FROM BOOK FETCH FIRST 1 ROWS ONLY -- Sybase ASE, SQL Server (without OFFSET) SELECT TOP 1 * FROM BOOK -- DB2 (with OFFSET), SQL Server (with OFFSET), Oracle (actual query may vary) SELECT * FROM ( SELECT LIMIT_98843777.*, ROW_NUMBER() OVER (ORDER BY ID ASC) AS ROWNUM_98843777 FROM ( SELECT TOP 100 PERCENT * FROM BOOK ORDER BY ID ASC ) AS LIMIT_98843777 ) AS OUTER_LIMIT_98843777 WHERE ROWNUM_98843777 > 1 AND ROWNUM_98843777 <= 3
As you can see, jOOQ will take care of the incredibly painful ROW_NUMBER() OVER() (or ROWNUM for Oracle) filtering in subselects for you, you'll just have to write limit(1).offset(2) in any dialect.
As can be seen in the above example, writing correct SQL can be quite tricky, depending on the SQL dialect. For instance, with SQL Server, you cannot have an ORDER BY clause in a subquery, unless you also have a TOP clause. This is illustrated by the fact that jOOQ renders a TOP 100 PERCENT clause for you. The same applies to the fact that ROW_NUMBER() OVER() needs an ORDER BY windowing clause, even if you don't provide one to the jOOQ query. By default, jOOQ adds ordering by the first column of your projection.