SQL:2008 標準引入 OFFSET ... FETCH，但各資料庫廠商實作時機與語法差異極大。開發跨平台應用或進行異構遷移時，掌握各家「取前 N 筆」寫法差異，能避免大量語法錯誤與效能陷阱。
| 資料庫 | 語法範例 | 關鍵字/子句 | 備註 |
|---|---|---|---|
| Oracle | SELECT * FROM table1 WHERE ROWNUM <= N |
ROWNUM 偽欄位 |
12c+ 支援 OFFSET ... FETCH；舊版需子查詢包裝配合 ROW_NUMBER() 做精確分頁 |
| Informix | SELECT FIRST N * FROM table1 |
FIRST N |
簡潔直觀，支援 SKIP M FIRST N 做分頁 |
| DB2 | SELECT * FROM table1 FETCH FIRST N ROWS ONLY |
FETCH FIRST N ROWS ONLY |
標準 SQL 風格；也可用 ROW_NUMBER() OVER() 窗口函數 |
| SQL Server | SELECT TOP N * FROM table1 |
TOP N |
2012+ 支援 OFFSET M ROWS FETCH NEXT N ROWS ONLY；TOP 可搭配 WITH TIES |
| Sybase ASE | SET ROWCOUNT N; SELECT * FROM table1 |
SET ROWCOUNT (Session 層級) |
影響後續所有查詢，用畢須 SET ROWCOUNT 0 重置；新版建議用 TOP |
| MySQL / MariaDB | SELECT * FROM table1 LIMIT N |
LIMIT N / LIMIT M, N |
最普及寫法；LIMIT offset, count 直觀易用 |
| FoxPro / Visual FoxPro | SELECT * FROM table ORDER BY col TOP N |
TOP N (需 ORDER BY) |
桌面資料庫特有語法，TOP 必須配合排序 |
| 資料庫 | 標準分頁寫法 (Page 2, Size 10) |
|---|---|
| Oracle 12c+ | SELECT * FROM t ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY |
| Oracle 11g- | SELECT * FROM (SELECT a.*, ROWNUM rn FROM (SELECT * FROM t ORDER BY id) a WHERE ROWNUM <= 20) WHERE rn > 10 |
| SQL Server 2012+ | SELECT * FROM t ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY |
| SQL Server 2008- | SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 20 * FROM t ORDER BY id) ORDER BY id DESC) ORDER BY id |
| DB2 | SELECT * FROM t ORDER BY id OFFSET 10 ROWS FETCH FIRST 10 ROWS ONLY |
| MySQL / PostgreSQL | SELECT * FROM t ORDER BY id LIMIT 10 OFFSET 10 |
| Informix | SELECT SKIP 10 FIRST 10 * FROM t ORDER BY id |
OFFSET 1000000 會掃描並捨棄百萬列，極度低效。建議改用「游標分頁」：WHERE id > last_seen_id ORDER BY id LIMIT 10。雖然 ANSI SQL 標準化腳步未止，但生產環境仍長期共存多種方言。收藏此速查表，下次跨庫開發、異構遷移、或 Code Review 時，即可快速驗證分頁語法正確性，避免低級錯誤上線。
