왜 필요한가: “상위 5명만 보여줘”라는 아주 흔한 요구

실무에서 가장 자주 나오는 요청 중 하나가 “매출 상위 5개 상품만”, “급여 1등부터 3등까지만” 같은 상위 N개(Top N) 추출이다. 15편에서 배운 ORDER BY는 전체 결과를 정렬만 할 뿐, “몇 개까지만 보여줄지”는 별도로 지정해야 한다. Oracle은 이 역할을 오랫동안 ROWNUM이라는 특수한 컬럼에 맡겨 왔는데, 이 ROWNUM의 동작 방식을 정확히 모르면 시험에서도 실무에서도 똑같은 함정에 빠진다.

쉽게 말하면: Top N은 “정렬해서 앞에서부터 몇 개 자르기”인데, ROWNUM은 “자르는 도중에 번호표를 나눠주는 방식”이라 정렬보다 먼저 번호가 매겨지면 원하는 순서와 어긋난다.

ROWNUM이란 무엇인가

ROWNUM은 Oracle이 쿼리 결과의 각 행에 자동으로 부여하는 의사(pseudo) 컬럼이다. 의사 컬럼이란 실제 테이블에 저장된 값이 아니라, 데이터베이스가 조회 시점에 계산해서 마치 컬럼처럼 보여주는 값을 뜻한다. ROWNUM은 테이블을 만들 때 지정하는 것이 아니라, SELECT 문이 행을 하나씩 뽑아낼 때마다 1, 2, 3, … 순서로 자동 부여된다.

여기서 가장 중요한 사실 하나를 짚어야 한다. ROWNUM은 결과 집합이 “완성된 뒤”에 매겨지는 번호가 아니라, 행을 하나씩 뽑아 결과에 담는 “그 순간”에 매겨지는 번호다. 즉 정렬(ORDER BY)이 끝난 다음에 붙는 꼬리표가 아니라, 원본 테이블을 훑으며 조건을 통과한 행이 결과 버퍼에 담기는 시점에 실시간으로 부여된다.

ROWNUM의 함정: 왜 WHERE ROWNUM = 2는 항상 0건인가

여기서부터가 시험 단골 함정이다. 아래 사원 테이블 5행을 예로 들어보자.

SELECT * FROM 사원 WHERE ROWNUM = 2;를 실행하면 어떻게 될까. 언뜻 “두 번째 행이 나오겠지”라고 생각하기 쉽지만, 결과는 0건이다. 이 결과를 이해하려면 데이터베이스가 이 쿼리를 처리하는 순서를 단계별로 따라가야 한다.

같은 원리로 WHERE ROWNUM > 1도 항상 0건이다. 첫 번째로 검사되는 행은 무조건 ROWNUM 1을 받는데, 조건이 1 > 1이라 거짓이 되어 탈락한다. 그러면 다음 행 역시 “이전 행이 통과하지 못했으므로” 다시 1을 받아 똑같이 탈락한다. 결국 어떤 행도 2 이상의 ROWNUM을 받을 수 없다. 이 규칙은 시험에서 “ROWNUM은 조건을 통과한 행에만 순차 부여되며, 통과하지 못하면 번호가 증가하지 않는다”는 문장으로 자주 등장한다.

반대로 WHERE ROWNUM <= 3이나 WHERE ROWNUM < 4는 정상적으로 동작한다. 첫 행이 1 <= 3을 통과하고, 둘째 행이 2 <= 3을 통과하는 식으로 조건이 순차적으로 계속 참이 되기 때문에 번호가 끊기지 않고 1, 2, 3까지 순서대로 부여된다. 즉 ROWNUM은 1부터 끊기지 않고 이어지는 상한 조건(<=, <)에서만 의도한 대로 동작하고, 특정 값과의 등호 비교나 하한 조건에서는 항상 실패한다.

자주 틀리는 점: “ROWNUM은 각 행이 원래 가지고 있던 순번”이라고 오해하면 이 함정에 빠진다. ROWNUM은 저장된 값이 아니라 조건 검사와 동시에 실시간으로 매겨지는 번호이며, 탈락한 행 다음에는 번호가 다시 1로 리셋된다는 점을 반드시 기억해야 한다.

Top N을 구하려면 왜 인라인뷰로 감싸야 하는가

이번에는 “급여가 가장 높은 상위 3명”을 뽑는다고 해보자. 직관적으로 이렇게 쓰고 싶어진다.

SELECT * FROM 사원 WHERE ROWNUM <= 3 ORDER BY 급여 DESC;

이 쿼리는 문법적으로는 실행되지만 원하는 결과를 주지 않는다. 이유는 SQL의 논리적 실행 순서에 있다. 13편에서 다뤘듯 WHERE는 ORDER BY보다 먼저 처리된다. 즉 위 쿼리는 “원본 테이블에서 아무 순서로나 앞의 3행을 먼저 뽑은 다음, 그 3행만 급여 기준으로 정렬”하는 것이다. 정렬되지 않은 원본 순서에서 앞 3개를 뽑았으니, 그 3명이 실제로 급여 1~3위라는 보장이 전혀 없다.

이 문제를 해결하는 방법이 인라인뷰(inline view)다. 인라인뷰란 FROM 절 안에 SELECT 문 자체를 괄호로 감싸 마치 하나의 테이블처럼 사용하는 서브쿼리를 말한다(17편의 서브쿼리 개념과 같은 뿌리이며, FROM 절에 놓인 서브쿼리를 특별히 인라인뷰라 부른다). 정렬을 먼저 끝낸 결과를 인라인뷰로 만들어 놓고, 그 바깥에서 ROWNUM 조건을 걸면 순서가 뒤바뀌지 않는다.

SELECT *
FROM (
  SELECT * FROM 사원 ORDER BY 급여 DESC
)
WHERE ROWNUM <= 3;

이 쿼리의 처리 순서는 다음과 같다.

정렬이 끝난 결과를 인라인뷰로 한 번 감싸고, 그 바깥에서 ROWNUM 조건을 걸어야 한다는 것이 Top N 쿼리의 핵심 원칙이다. ORDER BY와 ROWNUM을 같은 레벨의 SELECT에 나란히 써서는 안 된다.

FETCH FIRST: 더 간단하고 안전한 대안

Oracle 12c부터는 표준 SQL의 행 제한 절(row limiting clause)인 FETCH FIRST를 지원한다.

SELECT * FROM 사원
ORDER BY 급여 DESC
FETCH FIRST 3 ROWS ONLY;

FETCH FIRST는 ORDER BY 바로 뒤에 붙어서 “정렬이 끝난 다음에 앞에서부터 N개만 가져와라”는 의미가 문장 그대로 지켜지므로, 인라인뷰로 감싸는 번거로움이 없고 ROWNUM의 함정에도 애초에 걸리지 않는다. OFFSET n ROWS FETCH FIRST m ROWS ONLY 형태로 쓰면 앞의 n개를 건너뛰고 그다음 m개를 가져오는 페이징(paging)도 가능하다. 예를 들어 OFFSET 3 ROWS FETCH FIRST 5 ROWS ONLY는 4등부터 8등까지를 가져온다. 다만 SQLD 시험은 여전히 ROWNUM 기반 문제 비중이 높으므로, ROWNUM의 동작 원리를 정확히 아는 것이 우선이고 FETCH FIRST는 더 안전한 실무 대안으로 알아두면 된다. MySQL과 표준 SQL의 행 제한 문법 차이는 25편에서 대조표로 정리한다.

계층형 질의: 조직도처럼 자기 자신을 참조하는 데이터

이번에는 전혀 다른 문제를 보자. 사원 테이블에 “이 사원의 상사가 누구인지”를 나타내는 상사사번 컬럼이 있다고 하자. 이런 구조를 자기 참조 관계(self-referencing relationship)라 하는데, 같은 테이블의 한 행이 같은 테이블의 다른 행을 가리키는 구조다(08편에서 다룬 외래키 개념이 같은 테이블 안에서 걸리는 경우로 이해하면 된다). 이런 데이터에서 “사장부터 시작해서 몇 단계 아래 부하 직원인지” 같은 상하 관계를 조회하려면 일반 JOIN만으로는 몇 단계까지 내려가야 할지 미리 알 수 없어 한계가 있다. 이때 쓰는 것이 계층형 질의(hierarchical query)다.

다음과 같은 조직도 데이터를 예로 든다.

대표(100번) 아래에 김부장(200번)이 있고, 김부장 아래에 이과장(300번)과 박대리(400번)가 있으며, 이과장 아래에 최사원(500번)과 정사원(600번)이 있는 3단계 조직도다.

CONNECT BY와 START WITH

Oracle은 이런 구조를 START WITH와 CONNECT BY PRIOR 절로 조회한다.

SELECT LEVEL, 사번, 이름, 상사사번
FROM 사원
START WITH 상사사번 IS NULL
CONNECT BY PRIOR 사번 = 상사사번
ORDER BY LEVEL, 사번;

각 절의 역할을 하나씩 뜯어보자.

• START WITH 상사사번 IS NULL: 계층의 출발점(루트, root)을 정하는 조건이다. 상사가 없는 사람, 즉 최상위인 대표(100번)에서 시작하라는 뜻이다.
• CONNECT BY PRIOR 사번 = 상사사번: 부모 행과 자식 행을 어떻게 연결할지 정의한다. PRIOR가 붙은 쪽이 “이전 단계(부모)의 값”을 가리킨다.
• LEVEL: 역시 의사 컬럼으로, 루트를 1로 시작해 한 단계 내려갈 때마다 1씩 증가하는 계층 깊이를 나타낸다.

이 쿼리를 실행하면 다음과 같은 결과가 나온다.

LEVEL 값을 이용해 들여쓰기를 표현하면 조직도 모양이 그대로 눈에 보인다. LPAD(' ', (LEVEL-1)*2) || 이름처럼 LEVEL에 비례해 공백을 채워 넣는 방식이 시험에도 자주 나오는 응용이다.

PRIOR의 위치가 방향을 결정한다

CONNECT BY PRIOR 사번 = 상사사번처럼 PRIOR가 왼쪽(사번)에 붙으면, “이전 단계 행의 사번이 = 현재 행의 상사사번”이라는 뜻이 된다. 즉 부모의 사번을 자식의 상사사번과 맞추는 것이므로 상위에서 하위로 내려가는 순방향(정방향, top-down) 조회가 된다. 위에서 본 조직도가 바로 이 경우다.

반대로 PRIOR를 오른쪽(상사사번)에 붙여 CONNECT BY 사번 = PRIOR 상사사번이라고 쓰면, “현재 행의 사번이 = 이전 단계 행의 상사사번”이라는 뜻이 되어 방향이 뒤집힌다. 이 경우 START WITH도 특정 말단 사원(예: 사번 = 500)으로 지정하면, 그 사원에서 출발해 상사, 상사의 상사 순으로 거슬러 올라가는 역방향(bottom-up) 조회가 된다. 예를 들어 최사원(500번)에서 시작하면 이과장(300) → 김부장(200) → 대표(100) 순으로 조상을 타고 올라가는 결과가 나온다.

자주 틀리는 점: “PRIOR는 무조건 순방향을 의미한다”고 외우면 틀린다. PRIOR는 방향을 정하는 게 아니라 어느 쪽이 “이전 단계 값”인지를 지정할 뿐이며, 등호의 좌우 어느 쪽에 붙느냐에 따라 순방향도 역방향도 될 수 있다. 기출에서는 CONNECT BY PRIOR 자식컬럼 = 부모컬럼 형태와 CONNECT BY 자식컬럼 = PRIOR 부모컬럼 형태를 뒤섞어 제시하고 방향을 맞히게 하는 문제가 반복적으로 나온다.

또한 WHERE 절을 계층형 질의에 함께 쓰면, WHERE 조건은 계층이 전개된 이후에 결과 행을 걸러낼 뿐, 계층을 전개하는 범위 자체를 제한하지 않는다는 점도 주의해야 한다. 특정 하위 트리(subtree)를 통째로 잘라내려면 WHERE가 아니라 CONNECT BY 절 안에 조건을 추가해야 한다.

직접 해보기

직접 해보기 1 — ROWNUM은 정말 빈 결과를 주는가

본문에서 WHERE ROWNUM = 2와 WHERE ROWNUM > 1이 항상 0건이라고 설명했습니다. 정말 그런지, 그리고 시작값 1을 포함하는 조건과 어떻게 다른지 직접 비교해 보세요.

SELECT * FROM 사원 WHERE ROWNUM = 2;
SELECT * FROM 사원 WHERE ROWNUM > 1;
SELECT * FROM 사원 WHERE ROWNUM <= 2;

무엇을 보아야 하나: 앞의 두 쿼리는 정말 0건이 나오는지, 세 번째 쿼리는 몇 건이 나오는지 확인하세요. 세 쿼리의 유일한 차이는 비교 연산자뿐입니다.

왜 이걸 해보나: “ROWNUM은 저장된 순번”이라고 착각하면 ROWNUM = 2가 두 번째 행을 돌려줄 거라 기대하게 됩니다. 직접 0건을 눈으로 봐야 이 함정이 몸에 남습니다.

직접 해보기 2 — 정렬 후 자르기 vs 자른 후 정렬

급여 상위 3명을 뽑는 두 쿼리를 나란히 실행해 결과가 다른지 비교해 보세요.

SELECT * FROM 사원 WHERE ROWNUM <= 3 ORDER BY 급여 DESC;
 
SELECT * FROM (SELECT * FROM 사원 ORDER BY 급여 DESC) WHERE ROWNUM <= 3;

무엇을 보아야 하나: 첫 번째 쿼리가 뽑은 3명이 실제 급여 1·2·3위와 같은지, 두 번째(인라인뷰) 쿼리가 뽑은 3명과 비교했을 때 차이가 있는지 확인하세요.

왜 이걸 해보나: WHERE가 ORDER BY보다 먼저 처리된다는 규칙을 안다고 생각해도, 두 결과가 실제로 갈리는 것을 직접 보기 전까지는 시험장에서 흔들리기 쉽습니다.

직접 해보기 3 — CONNECT BY로 조직도 조회하기

본문의 조직도 예제를 그대로 실행해 LEVEL이 어떻게 매겨지는지 확인해 보세요.

SELECT LEVEL, 사번, 이름, 상사사번
FROM 사원
START WITH 상사사번 IS NULL
CONNECT BY PRIOR 사번 = 상사사번
ORDER BY LEVEL, 사번;

무엇을 보아야 하나: 몇 행이 나오는지, LEVEL 값이 본문 조직도의 단계(대표 1단계 → 김부장 2단계 → …)와 정확히 일치하는지 확인하세요.

왜 이걸 해보나: START WITH와 CONNECT BY PRIOR가 실제로 어떤 행부터 어떻게 뻗어나가는지는 결과를 직접 눈으로 훑어봐야 감이 잡힙니다.

핵심 정리

• ROWNUM은 행이 결과 집합에 포함되기로 확정되는 순간 1부터 순차 부여되는 의사 컬럼이며, 조건을 통과하지 못한 행 다음에는 번호가 다시 1로 리셋된다.
• 이 때문에 WHERE ROWNUM = 2, WHERE ROWNUM > 1처럼 시작값 1을 건너뛰는 조건은 항상 0건이 되고, WHERE ROWNUM <= n처럼 1부터 이어지는 상한 조건만 정상 동작한다.
• Top N을 정확히 구하려면 정렬을 먼저 끝낸 결과를 인라인뷰로 감싼 뒤, 바깥 쿼리에서 ROWNUM 조건을 걸어야 한다. ORDER BY와 ROWNUM 조건을 같은 SELECT에 나란히 쓰면 정렬 전 순서에서 잘려나간다.
• FETCH FIRST n ROWS ONLY는 인라인뷰 없이 정렬 직후 상위 n개를 안전하게 가져오는 표준 SQL 행 제한 절이다.
• 계층형 질의는 START WITH로 루트를 지정하고 CONNECT BY PRIOR로 부모-자식 연결 조건을 정의하며, LEVEL로 계층 깊이를 확인한다.
• PRIOR가 등호의 어느 쪽에 붙느냐에 따라 순방향(상위→하위) 조회와 역방향(하위→상위) 조회가 갈린다.

마무리 복습

참고 자료

• Oracle Database SQL Language Reference - Hierarchical Queries 
• Oracle Database SQL Language Reference - Pseudocolumns (ROWNUM)