방언별 SQL 페이징: LIMIT, OFFSET FETCH, TOP, ROWNUM

결과를 페이지 단위로 나눠 읽는 문법은 데이터베이스마다 다릅니다. 같은 페이지를 가져오는 엔진별 문법을 살펴보고, 큰 테이블에서 OFFSET 대신 쓸 수 있는 키셋 페이징도 알아봅니다.

아래 예제는 모두 같은 10행을 반환합니다. id 순으로 21번째부터 30번째 상품입니다.

SQLite, MySQL, MariaDB, PostgreSQL

이 네 데이터베이스는 LIMIT <가져올 행 수> OFFSET <건너뛸 행 수> 문법을 함께 사용합니다. 한 곳에서 작성한 쿼리를 다른 곳에서도 그대로 실행할 수 있습니다.

-- Rows 21 to 30, ordered by id.
SELECT id, name, price
FROM products
ORDER BY id
LIMIT 10 OFFSET 20;

Planche에서 실행

결과 — 10행 중 처음 5행

id	name	price
21	Onyx Sofa	1637.9
22	Zen Soundbar	1991.58
23	Onyx Catan Plus	525.91
24	Onyx Sleeping Bag X	2019.45
25	Pixel Lean Startup	188.86

브라우저의 새 탭에서 Planche가 열리고, 샘플 데이터베이스가 로드된 채로 이 쿼리가 들어갑니다. 이 페이지의 다른 예제도 같은 탭을 다시 씁니다. F9를 누르기 전까지는 아무것도 실행되지 않습니다.

MySQL / MariaDB

SELECT id, name, price
FROM products
ORDER BY id
LIMIT 10 OFFSET 20;

-- The older comma form means the same thing, with the arguments reversed:
-- offset first, then count. Easy to misread, so prefer the form above.
SELECT id, name, price
FROM products
ORDER BY id
LIMIT 20, 10;

MySQL / MariaDB — 비교용입니다. 브라우저 샘플 데이터베이스는 SQLite라서 이 문장은 여기서 실행할 수 없습니다.

MySQL과 MariaDB는 LIMIT 20, 10 표기도 허용합니다. 이 표기에서는 앞의 20이 건너뛸 행 수이고, 뒤의 10이 가져올 행 수입니다. LIMIT 10 OFFSET 20과 같은 결과지만 숫자를 읽는 순서가 반대라 헷갈리기 쉽습니다. 새 쿼리에서는 의미가 드러나는 LIMIT … OFFSET … 표기를 쓰는 편이 낫습니다.

PostgreSQL

SELECT id, name, price
FROM products
ORDER BY id
LIMIT 10 OFFSET 20;

-- PostgreSQL also accepts the SQL standard spelling.
SELECT id, name, price
FROM products
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

PostgreSQL — 비교용입니다. 브라우저 샘플 데이터베이스는 SQLite라서 이 문장은 여기서 실행할 수 없습니다.

SQL Server

-- OFFSET / FETCH requires an ORDER BY. Without one it is a syntax error.
SELECT id, name, price
FROM products
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- TOP caps the result but cannot skip, so it only gives you the first page.
SELECT TOP 10 id, name, price
FROM products
ORDER BY id;

SQL Server — 비교용입니다. 브라우저 샘플 데이터베이스는 SQLite라서 이 문장은 여기서 실행할 수 없습니다.

SQL Server는 두 점에서 다릅니다. OFFSET … FETCH는 독립된 절이 아니라 ORDER BY 뒤에 붙습니다. 따라서 ORDER BY가 없으면 문법 오류가 납니다. 더 오래된 TOP은 행을 건너뛸 수 없어 첫 페이지에만 사용할 수 있습니다.

Planche는 SQL Server에서 용도에 따라 두 문법을 사용합니다. 에디터의 행 제한에는 추가 절이 필요 없는 TOP n을 사용합니다. 테이블 그리드에서 페이지를 넘길 때는 OFFSET n ROWS FETCH NEXT m ROWS ONLY와 ORDER BY를 함께 사용합니다.

Oracle

-- Oracle 12c and later.
SELECT id, name, price
FROM products
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Before 12c, ROWNUM had to be applied to an already-sorted subquery.
SELECT * FROM (
  SELECT t.*, ROWNUM AS rn FROM (
    SELECT id, name, price FROM products ORDER BY id
  ) t
  WHERE ROWNUM <= 30
)
WHERE rn > 20;

Oracle — 비교용입니다. 브라우저 샘플 데이터베이스는 SQLite라서 이 문장은 여기서 실행할 수 없습니다.

Oracle 12c(2013년)부터 표준 행 제한 절을 사용할 수 있습니다. FETCH FIRST n ROWS ONLY만으로는 다음 페이지로 이동할 수 없습니다. 이전 버전에서는 ROWNUM을 사용했는데, 이 값은 정렬 전에 매겨집니다. 그래서 WHERE ROWNUM <= 10 ORDER BY price는 상위 10개가 아니라 임의의 10개를 정렬합니다. 예제처럼 안쪽에서 정렬하고, 가운데에서 번호를 매긴 뒤, 바깥에서 필터링해야 합니다.

OFFSET의 실제 비용

엔진은 만 번째 행부터 바로 읽을 수 없습니다. 순서대로 행을 만들며 앞의 행을 건너뜁니다. 따라서 OFFSET 10000으로 10행을 가져오려면 10,010행을 읽습니다. 페이지가 뒤로 갈수록 느려지는 이유입니다.

행에 고유하고 순서가 있는 키가 있다면, 다음 페이지를 그 키로 요청하세요.

-- The same page without OFFSET: continue after the last id of the
-- previous page. The database jumps straight there through the index.
SELECT id, name, price
FROM products
WHERE id > 20
ORDER BY id
LIMIT 10;