[ORACLE] 특정 행 추출하기 (FETCH, ROWNUM, OFFSET, PERCENT, LIMIT, TOP)

업무를 하며 쿼리를 작성하다 보면 페이징 처리를 하거나 제한해서 데이터를 추출해야 하는 상황에 주로 ROWNUM을 활용했었는데, 이번에 FETCH 구문을 활용하는 방법을 알게 되었다.

보통 ROWNUM을 활용하여 데이터를 추출하려면 정렬한 쿼리를 서브쿼리로 감싸야 하는데, FETCH 구문을 활용하면 좀 더 간결하게 쿼리를 작성할 수 있어 종종 활용하게 될 것 같고, 사실 ROWNUM과 FETCH 구문 외에도 TOP, LIMIT 등 데이터를 추출하는 방법이 많은데, 다 숙지해뒀다가 다양한 상황 및 조건에 맞춰 유연하게 쿼리를 작성할 수 있는 사람이 되어아겠다.

 

* FETCH 구문의 경우 ORACLE 12c 버전부터 사용 가능

 

FETCH 구문

SELECT [컬럼명]
  FROM [테이블명]
 ORDER BY [컬럼명]
OFFSET [행 시작 위치] { ROW | ROWS }
 FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ] { ROW | ROWS } { ONLY | WITH TIES }

• OFFSET - 데이터를 추출할 시작 위치를 지정할 수 있다 ( 정렬의 앞 부분 SKIP )
• FETCH : 데이터 추출하려는 행을 지정할 수 있는 구문
• FIRST/NEXT : 기능 상의 큰 차이점은 없다.
• ROWCOUNT : 추출하려는 행의 수를 입력
• PERCENT : 원하는 %만큼 행을 추출
• ROW/ROWS : 의미를 명확하게 하고자 구분하여 사용. 기능 상의 큰 차이점은 없다.
• ONLY : 앞서 지정한 구문의 행의 수만큼만 추출
• WITH TIES : 앞서 지정한 구문의 행의 수만큼만 추출하되, 동일한 정렬기준에 해당하는 값은 전부 가져온다.

 

FETCH 구문 예시

-- 상품 가격 내림차순으로 추출하기
SELECT PRODUCT_NAME, PRICE
  FROM PRODUCT
 ORDER BY PRICE DESC

-- 제일 비싼 2개의 상품을 제외하고, 가격 내림차순으로 추출하기
SELECT PRODUCT_NAME, PRICE
  FROM PRODUCT
 ORDER BY PRICE DESC
OFFSET 2 ROWS

-- 제일 비싼 2개의 상품을 제외하고, 가격 내림차순으로 5개의 상품만 추출하기
SELECT PRODUCT_NAME, PRICE
  FROM PRODUCT
 ORDER BY PRICE DESC
OFFSET 2 ROWS
 FETCH FIRST 5 ROWS ONLY

-- 제일 비싼 2개의 상품을 제외하고, 가격순 상위 50% 상품 추출하기
SELECT PRODUCT_NAME, PRICE
  FROM PRODUCT
 ORDER BY PRICE DESC
OFFSET 2 ROWS
 FETCH FIRST 50 PERCENT ROWS ONLY

 

 

 

 

TOP, LIMIT, ROWNUM

-- TOP
SELECT TOP {rowcount} [컬럼명]
  FROM [테이블명]
 ORDER BY [컬럼명]


-- LIMIT
SELECT [컬럼명]
  FROM [테이블명]
 ORDER BY [컬럼명]
 LIMIT {rowcount}


-- ROWNUM
SELECT [컬럼명]
  FROM [테이블명]
 WHERE ROWNUM { <= | = | >= } { rowcount }

 

 

TOP, LIMIT, ROWNUM 예시

-- TOP 예시
-- 상품의 비싼 가격순으로 5개 추출하기
SELECT TOP 5 PRODUCT_NAME, PRICE
  FROM PRODUCT
 ORDER BY PRICE DESC

-- LIMIT 예시
-- 상품의 비싼 가격순으로 10개 추출하기
SELECT PRODUCT_NAME, PRICE
  FROM PRODUCT
 ORDER BY PRICE DESC
 LIMIT 10

-- ROWNUM 예시
-- 10일 이내 등록된 상품 중에서 5개의 상품 추출하기
SELECT PRODUCT_NAME, PRICE
  FROM PRODUCT
 WHERE REGIST_DATA > SYSDATE - 10
   AND ROWNUM <= 5

-- ROWNUM 예시
-- 10일 이내 등록된 상품 중에서 가장 비싼 5개의 상품 추출하기
SELECT A.*
  FROM (SELECT PRODUCT_NAME, PRICE
          FROM PRODUCT
         WHERE REGIST_DATA > SYSDATE - 10
         ORDER BY PRICE DESC) A
 WHERE A.ROWNUM <= 5

 

 

 

[ 참고 ]

 - SQL에서 TOP, LIMIT, FETCH FIRST, ROWNUM 절 활용하기 (https://statuscode.tistory.com/138)