Oracle에서 조회 결과에 번호를 붙이거나 특정 개수의 데이터만 가져오려고 할 때 ROWNUM과 ROW_NUMBER()를 자주 사용합니다.
두 기능 모두 결과에 순번을 부여할 수 있기 때문에 처음 사용할 때는 비슷해 보입니다.
하지만 실제로는 동작하는 방식과 사용하는 목적에 차이가 있습니다.
특히 ORDER BY와 함께 사용할 때 이 차이를 제대로 이해하지 못하면 예상과 다른 결과가 나올 수 있습니다.
이번 글에서는 Oracle의 ROWNUM과 ROW_NUMBER()가 어떻게 다른지, 각각 어떤 상황에서 사용하는 것이 좋은지 예제를 통해 알아보겠습니다.
ROWNUM이란?
ROWNUM은 Oracle이 조회된 행에 순서대로 부여하는 가상 컬럼입니다.
별도의 함수를 호출하지 않고 바로 사용할 수 있습니다.
예를 들어 사원 목록에 번호를 붙여 조회하려면 다음과 같이 작성할 수 있습니다.
SELECT
ROWNUM AS RN,
EMPNO,
ENAME,
SAL
FROM EMP;결과는 다음과 같이 나올 수 있습니다.
| RN | EMPNO | ENAME | SAL |
|---|---|---|---|
| 1 | 1001 | 홍길동 | 3000 |
| 2 | 1002 | 김철수 | 4500 |
| 3 | 1003 | 이영희 | 2500 |
| 4 | 1004 | 박민수 | 5000 |
조회된 데이터에 1부터 순서대로 번호가 붙습니다.
하지만 여기서 중요한 점이 있습니다.
ROWNUM은 단순히 화면에 보이는 최종 결과를 기준으로 번호를 붙이는 기능이라고 생각하면 안 됩니다.
이 때문에 ORDER BY와 함께 사용할 때 주의해야 합니다.
ROWNUM으로 조회 개수 제한하기
ROWNUM은 과거 Oracle에서 조회할 데이터의 개수를 제한할 때 많이 사용했습니다.
예를 들어 사원 데이터 중 5개만 조회하려면 다음과 같이 작성할 수 있습니다.
SELECT
EMPNO,
ENAME,
SAL
FROM EMP
WHERE ROWNUM <= 5;이렇게 하면 조건을 만족하는 데이터 중 최대 5건만 조회됩니다.
단순하게
“전체 데이터 중 몇 건만 가져오고 싶다.”
라는 경우에는 간단하게 사용할 수 있습니다.
ROWNUM과 ORDER BY를 같이 사용할 때 주의할 점
ROWNUM을 사용할 때 가장 많이 헷갈리는 부분입니다.
예를 들어 급여가 가장 높은 사원 3명을 조회하고 싶다고 가정해보겠습니다.
다음과 같이 작성하면 될 것처럼 보입니다.
SELECT
ROWNUM AS RN,
EMPNO,
ENAME,
SAL
FROM EMP
WHERE ROWNUM <= 3
ORDER BY SAL DESC;하지만 이 쿼리가 전체 사원 중 급여가 높은 3명을 가져오는 것을 보장하지는 않습니다.
왜냐하면 원하는 상위 데이터를 정렬해서 고르는 것과 ROWNUM을 제한하는 순서를 생각해야 하기 때문입니다.
따라서 먼저 급여순으로 정렬한 뒤 바깥쪽에서 ROWNUM을 적용하는 방식으로 작성할 수 있습니다.
SELECT
ROWNUM AS RN,
EMPNO,
ENAME,
SAL
FROM (
SELECT
EMPNO,
ENAME,
SAL
FROM EMP
ORDER BY SAL DESC
)
WHERE ROWNUM <= 3;결과
| RN | EMPNO | ENAME | SAL |
|---|---|---|---|
| 1 | 1004 | 박민수 | 5000 |
| 2 | 1002 | 김철수 | 4500 |
| 3 | 1001 | 홍길동 | 3000 |
이렇게 하면 먼저 급여를 내림차순으로 정렬하고 그 결과에서 3건을 가져오게 됩니다.
ROW_NUMBER()란?
ROW_NUMBER()는 분석 함수 중 하나로, 지정한 정렬 기준에 따라 각 행에 순번을 부여합니다.
기본 문법은 다음과 같습니다.
ROW_NUMBER() OVER (ORDER BY 정렬컬럼)예를 들어 급여가 높은 순서대로 번호를 부여하려면 다음과 같이 사용할 수 있습니다.
SELECT
EMPNO,
ENAME,
SAL,
ROW_NUMBER() OVER (ORDER BY SAL DESC) AS RN
FROM EMP;결과
| EMPNO | ENAME | SAL | RN |
|---|---|---|---|
| 1004 | 박민수 | 5000 | 1 |
| 1002 | 김철수 | 4500 | 2 |
| 1001 | 홍길동 | 3000 | 3 |
| 1003 | 이영희 | 2500 | 4 |
ROW_NUMBER()는 OVER()안에 정렬 기준을 직접 지정할 수 있다는 것이 특징입니다.
즉,
ROW_NUMBER() OVER (ORDER BY SAL DESC)는
SAL이 높은 순서대로 순번을 부여하겠다
는 의미입니다.
ROW_NUMBER()로 상위 데이터 조회하기
급여가 높은 사원 3명을 ROW_NUMBER()를 이용해 조회할 수도 있습니다.
SELECT
RN,
EMPNO,
ENAME,
SAL
FROM (
SELECT
EMPNO,
ENAME,
SAL,
ROW_NUMBER() OVER (ORDER BY SAL DESC) AS RN
FROM EMP
)
WHERE RN <= 3;결과는 다음과 같습니다.
| RN | EMPNO | ENAME | SAL |
|---|---|---|---|
| 1 | 1004 | 박민수 | 5000 |
| 2 | 1002 | 김철수 | 4500 |
| 3 | 1001 | 홍길동 | 3000 |
ROWNUM을 사용할 때와 달리 순번을 부여하는 기준이 SQL에 명확하게 표현되어 있다는 장점이 있습니다.
ROWNUM과 ROW_NUMBER() 차이
두 기능의 가장 큰 차이는 순번을 어떤 기준으로 부여하느냐입니다.
| 구분 | ROWNUM | ROW_NUMBER() |
|---|---|---|
| 종류 | 가상 컬럼 | 분석 함수 |
| 번호 부여 | 처리되는 행에 번호 부여 | 지정한 정렬 기준으로 번호 부여 |
| 정렬 기준 지정 | 직접 지정 불가 | OVER(ORDER BY …) 사용 |
| 그룹별 번호 | 어려움 | 가능 |
| 단순 행 개수 제한 | 편리함 | 가능하지만 상대적으로 복잡 |
| 순위/순번 처리 | 제한적 | 적합 |
단순히 몇 건의 데이터만 가져오는 목적이라면 ROWNUM도 간단하게 사용할 수 있습니다.
반면 특정 정렬 기준이나 그룹을 기준으로 정확한 순번을 만들어야 한다면 ROW_NUMBER()가 훨씬 편리합니다.
ROW_NUMBER()의 장점 – 그룹별 순번
ROW_NUMBER()가 유용한 이유 중 하나는 PARTITION BY를 사용할 수 있다는 점입니다.
예를 들어 부서별로 급여가 높은 순서대로 번호를 매겨보겠습니다.
SELECT
EMPNO,
ENAME,
DEPTNO,
SAL,
ROW_NUMBER() OVER (
PARTITION BY DEPTNO
ORDER BY SAL DESC
) AS RN
FROM EMP;여기서
PARTITION BY DEPTNO는 부서별로 데이터를 나눈다는 의미입니다.
그리고 각 부서 안에서
ORDER BY SAL DESC를 기준으로 다시 번호를 매깁니다.
예를 들어 결과가 다음과 같이 나올 수 있습니다.
| EMPNO | ENAME | DEPTNO | SAL | RN |
|---|---|---|---|---|
| 1002 | 김철수 | 10 | 4500 | 1 |
| 1001 | 홍길동 | 10 | 3000 | 2 |
| 1004 | 박민수 | 20 | 5000 | 1 |
| 1003 | 이영희 | 20 | 2500 | 2 |
부서가 바뀌면 RN이 다시 1부터 시작합니다.
이 기능은 실무에서도 꽤 유용합니다.
예를 들어
- 카테고리별 최신 게시글 찾기
- 회원별 최근 주문 찾기
- 상품별 최근 변경 이력 조회
- 그룹별 최고값을 가진 데이터 찾기
같은 상황에서 사용할 수 있습니다.
그룹별 가장 높은 데이터 하나만 조회하기
ROW_NUMBER()를 활용하면 각 그룹에서 원하는 데이터 하나만 가져오는 것도 가능합니다.
예를 들어 각 부서에서 급여가 가장 높은 사원 한 명씩 조회해보겠습니다.
SELECT
EMPNO,
ENAME,
DEPTNO,
SAL
FROM (
SELECT
EMPNO,
ENAME,
DEPTNO,
SAL,
ROW_NUMBER() OVER (
PARTITION BY DEPTNO
ORDER BY SAL DESC
) AS RN
FROM EMP
)
WHERE RN = 1;결과
| EMPNO | ENAME | DEPTNO | SAL |
|---|---|---|---|
| 1002 | 김철수 | 10 | 4500 |
| 1004 | 박민수 | 20 | 5000 |
개인적으로 ROW_NUMBER()를 사용할 때 단순히 번호를 표시하는 것보다 이런 식으로 그룹별 최신 데이터나 상위 데이터 한 건을 찾는 용도로 사용하는 경우가 더 많습니다.
RANK(), DENSE_RANK()와는 무엇이 다를까?
ROW_NUMBER()와 함께 자주 등장하는 함수가 RANK()와 DENSE_RANK()입니다.
차이는 동일한 값이 있을 때 나타납니다.
급여가 다음과 같다고 가정해보겠습니다.
5000
4000
4000
3000각 함수를 적용하면 다음과 같습니다.
| SAL | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 5000 | 1 | 1 | 1 |
| 4000 | 2 | 2 | 2 |
| 4000 | 3 | 2 | 2 |
| 3000 | 4 | 4 | 3 |
ROW_NUMBER()는 값이 같더라도 무조건 서로 다른 번호를 부여합니다.
RANK()는 같은 값에 같은 순위를 부여하고 다음 순위를 건너뜁니다.
DENSE_RANK()는 같은 값에 같은 순위를 부여하지만 다음 순위를 건너뛰지 않습니다.
따라서 순위가 아니라 각 행에 고유한 순번이 필요하다면 ROW_NUMBER()를 사용하는 것이 적합합니다.
언제 ROWNUM을 사용할까?
단순하게 조회되는 데이터의 개수를 제한할 때 사용할 수 있습니다.
WHERE ROWNUM <= 10특히 기존 Oracle SQL을 유지보수하다 보면 ROWNUM을 사용한 쿼리를 자주 볼 수 있습니다.
다만 최신 Oracle에서는 단순 행 제한에 FETCH FIRST문법을 사용할 수도 있습니다.
예를 들어 다음과 같습니다.
SELECT
EMPNO,
ENAME,
SAL
FROM EMP
ORDER BY SAL DESC
FETCH FIRST 10 ROWS ONLY;단순 TOP-N 조회라면 이런 방식도 알아두면 좋습니다.
언제 ROW_NUMBER()를 사용할까?
다음처럼 데이터의 순서 자체가 중요한 경우에 적합합니다.
- 특정 기준으로 번호를 부여할 때
- 그룹별 순위를 만들 때
- 그룹별 최신 데이터 한 건을 찾을 때
- 중복 데이터 중 한 건을 선택할 때
- 페이징 처리를 할 때
특히 PARTITION BY와 함께 사용할 수 있다는 점 때문에 복잡한 조회에서는 ROWNUM보다 활용도가 높습니다.
ROWNUM과 ROW_NUMBER(), 무엇을 사용해야 할까?
둘 중 하나가 무조건 더 좋다고 보기는 어렵습니다.
단순히 조회 결과를 몇 건으로 제한하려는 목적이라면 ROWNUM이나 최신 Oracle의 FETCH FIRST가 간단합니다.
반대로
“급여가 높은 순서대로 번호를 매기고 싶다.”
또는
“부서별로 가장 급여가 높은 사원 한 명을 찾고 싶다.”
처럼 명확한 기준에 따라 순번을 만들어야 한다면 ROW_NUMBER()가 적합합니다.
결국 중요한 것은 번호를 붙이는 목적입니다.
요약
Oracle의 ROWNUM과 ROW_NUMBER()는 모두 조회 결과에서 순번과 관련된 작업에 사용할 수 있지만 동작 방식은 다릅니다.
ROWNUM은 Oracle이 처리하는 행에 부여하는 가상 컬럼으로 단순한 조회 개수 제한 등에 사용할 수 있습니다.
반면 ROW_NUMBER()는 분석 함수이며 ORDER BY를 통해 원하는 기준으로 순번을 부여할 수 있고 PARTITION BY를 이용해 그룹별 순번도 만들 수 있습니다.
따라서 단순 행 제한에는 ROWNUM을 사용할 수 있고, 정렬 기준이나 그룹별 순번이 필요한 경우에는 ROW_NUMBER()를 사용하는 것이 편리합니다.
특히 ROWNUM과 ORDER BY를 함께 사용할 때는 처리 순서 때문에 예상과 다른 결과가 나올 수 있으므로 주의해야 합니다.
자주 묻는 질문(FAQ)
Q. ROWNUM과 ROW_NUMBER()는 같은 기능인가요?
아닙니다. 둘 다 번호와 관련된 기능이지만 ROWNUM은 Oracle의 가상 컬럼이고, ROW_NUMBER()는 정렬 기준에 따라 번호를 부여하는 분석 함수입니다.
Q. ROWNUM으로 상위 10개 데이터를 조회할 수 있나요?
가능합니다. 다만 특정 컬럼을 정렬한 후 상위 10개를 가져오려면 먼저 정렬한 결과에 ROWNUM을 적용해야 합니다.
Q. ROW_NUMBER()에서 같은 값이 있으면 같은 번호가 나오나요?
아닙니다. 같은 값이 있어도 각각 다른 번호가 부여됩니다. 같은 값에 같은 순위를 부여하려면 RANK()나 DENSE_RANK()를 사용할 수 있습니다.
Q. ROWNUM 대신 ROW_NUMBER()만 사용해도 되나요?
목적에 따라 다릅니다. 복잡한 순번 처리에는 ROW_NUMBER()가 유용하지만 단순한 행 개수 제한이라면 ROWNUM이나 FETCH FIRST가 더 간단할 수 있습니다.
Q. 부서별로 가장 급여가 높은 사원을 찾으려면 어떤 것을 사용해야 하나요?
ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC)와 같이 작성한 뒤 RN이 1인 데이터만 조회하는 방법을 사용할 수 있습니다.
함께 보면 좋은 Oracle 글
