How universal is the LIMIT statement in SQL?
I'm in the process of generalizing a Django DB replication app and it uses the statement:
SELECT %s FROM %s LIMIT 1
to fetch 1 row and use the Python DBAPI to describe the fields, it works fine with ORACLE and MySQL but, how cross platform is the LIMIT statement?
http://en.wikipedia.org/wiki/Select_(SQL)#Limiting_result_rows lists all of the major variants of the select command.
I believe the best way to do this is to use the SET ROWCOUNT command before your SELECT statement.
So, for you:
SET ROWCOUNT 1 SELECT %s FROM %s
LIMIT has become quite popular with a variety of Open Source databases, but unfortunately, the fact is that OFFSET pagination has been about the least standardised SQL feature of them all, having been standardised as late as in SQL:2008.
Until then, the jOOQ user manual page on the LIMIT clause shows how the various equivalent statements can be formed in each SQL dialect:
-- 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, SQL Server 2012, Oracle 12c, SQL:2008 standard -- Some need a mandatory ORDER BY clause prior to OFFSET SELECT * FROM BOOK [ ORDER BY ... ] 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 2008 (without OFFSET) SELECT TOP 1 * FROM BOOK
Now, these were all pretty straight-forward, right? Here comes the nasty part, when you have to emulate them:
-- DB2 (with OFFSET), SQL Server 2008 (with OFFSET), SELECT * FROM ( SELECT BOOK.*, ROW_NUMBER() OVER (ORDER BY ID ASC) AS RN FROM BOOK ) AS X WHERE RN > 2 AND RN <= 3 -- DB2 (with OFFSET), SQL Server 2008 (with OFFSET) -- When the original query uses DISTINCT! SELECT * FROM ( SELECT DISTINCT BOOK.ID, BOOK.TITLE DENSE_RANK() OVER (ORDER BY ID ASC, TITLE ASC) AS RN FROM BOOK ) AS X WHERE RN > 2 AND RN <= 3 -- Oracle 11g and less SELECT * FROM ( SELECT b.*, ROWNUM RN FROM ( SELECT * FROM BOOK ORDER BY ID ASC ) b WHERE ROWNUM <= 3 ) WHERE RN > 2
Choose your poison ;-)
LIMIT is very far from universal - out of major RDBMS, it's pretty much restricted to MySQL and PostgreSQL. Here is a detailed analysis of how this is done in many other implementations, including MSSQL, Oracle and DB2, as well as in ANSI SQL.
It's not at all universal. Actually I am surprised it is working for you in Oracle; it didn't used to be present. Normally Oracle users go for ROWNUM.
Every database has its own syntax for limiting results by row number. There are also two methods that are ANSI standard SQL:
FETCH FIRST. Derived from DB/2 and only made standard in SQL:2008, so very little DBMS support. Can't use an offset.
The windowing function SELECT ..., ROW_NUMBER() OVER (ORDER BY some_ordering) AS rn WHERE rn BETWEEN n AND m ... ORDER BY some_ordering. This is from SQL:2003 and has some (patchy, sometimes slow) support in newer DBMSs. It can use an offset or any other comparison function on the row number, but has the drawback of being appallingly ugly.
Here's a good overview of the tediousness you will have to deal with if you want cross-DBMS pagination support.
It doesn't work on MSSQL (which uses SELECT TOP 10 * FROM Blah instead). That cuts out a significant portion of the DB market. I'm not sure about others.
Also, it's possible, though very unlikely, that your DB API will translate it for you.
LIMIT is not part of the ANSI SQL standard as of the 1992 standard; I don't have a copy of any later standard to hand. Vendors' compliance with the standard is pretty vague at the best of times. For what it's worth, "LIMIT" is listed as a reserved word (meaning it can't legally be used as an identifier even in cases where it's not a keyword in the implementation).
Since it was mentioned in one of the answers that LIMIT and OFFSET is more or less limited to MySQL and PostgreSQL, I thought of pointing out that SAP HANA also supports LIMIT and OFFSET clause. But OFFSET without LIMIT is not permitted in the SAP HANA database.