[ORACLE] ROWNUM에 대해

Leetcode DB 185 문제를 풀다가

select salary

from (

    select salary, rownum

    from (

        select distinct salary

        from Employee

        order by salary desc

        )

)

where rownum = 3

위 코드의 결과는

 

아무것도 안나오고,

==========================================

select salary

from (

    select salary, rownum

    from (

        select distinct salary

        from Employee

        order by salary desc

        )

)

where rownum <= 3

이 쿼리는 나오고,

=======================================

select salary

from (

    select salary, rownum as rn

    from (

        select distinct salary

        from Employee

        order by salary desc

        )

)

where rn = 3

이렇게 쿼리를 짜면 결과가 나오는 것을 확인할 수 있다.

 

단순히 보면 별칭이 있고 없고 차이밖에 없는 것 같은데 결과가 왜 다를까?

이것에 대한 이유를 몰랐기에 정리해보려고 한다.

=======================================

 

결론부터 말하자면, rownum의 동작 원리에 대해 몰랐기 때문이다.

 

조금만 생각해보면 당연한 결과였다.

select salary

from (

    select salary, rownum

    from (

        select distinct salary

        from Employee

        order by salary desc

        )

)

where rownum = 3

여기서 where 절의 rownum은 from 서브쿼리에서 조회한 rownum이 아니라 바깥 레벨을 가리킨다. 즉 from 절에서 조회한 행들을 다시 최종 바깥레벨에서 rownum으로 값을 매기고 있는데, rownum은 조회된 결과를 반환할 때마다 한 행 씩 순번을 매겨주는 역할을 한다. 그래서 첫 행을 반환하고 rownum을 1을 반환하는데 where 조건문에서 rownum = 3을 필터링하고 있기에 실패하고, rownum을 할당 해야 다음 순번으로 넘어가는데, rownum = 1을 할당하지 못했기에 계속 rownum은 1을 반환하고 where 절인 rownum = 3은 도달하지 못한다. 그래서 결과가 나오지 않는다.

 

select salary

from (

    select salary, rownum as rn

    from (

        select distinct salary

        from Employee

        order by salary desc

        )

)

where rn = 3

하지만 이 쿼리에서의 where 필터링 조건은 바깥 레벨이 아닌 from 서브쿼리 내부의 rownum을 가리키며 필터링한다.

즉, where 절에서 가리키는 대상이 다른 것이다. rownum = 3은 가장 바깥 레벨의 rownum을 가리키고, rn = 3은 from 서브쿼리의 rownum(별칭 rn)을 가리킨다.

그러므로 from 서브쿼리에서의 rownum은 정상적으로 할당되었기에 where절에서도 rn = 3 인 결과를 가져올 수 있다.

 

나는 처음에는 별칭 유무에 따라 달라지는 것이라 생각했고, 그래서 별칭의 숨은 기능에 대해 찾아보았는데, 실제 원인은 rownum의 동작 방식을 제대로 몰랐던 나에게 있었다 ㅎ_ㅎ 

 

참고: pseudocolumn( 의사 컬럼 )이란 

실제 테이블 정의에는 없지만 마치 컬럼처럼 사용할 수 있는 특별한 식별자이다.

gpt 피셜

1. 테이블에 물리적으로 존재하지 않음
   • 데이터 사전에 컬럼으로 정의되어 있지 않지만, SELECT 등 조회 문맥에서 컬럼처럼 참조 가능
   • 예: ROWNUM, ROWID, SYSDATE, USER, LEVEL, 시퀀스의 CURRVAL, NEXTVAL 등
2. 자동 생성 및 관리
   • 예를 들어 ROWNUM은 쿼리 수행 시 반환된 순서대로 1부터 번호를 매겨주고,
   • ROWID는 각 행의 물리적 저장 위치(데이터베이스 블록·파일 번호 등)를 나타냄,
   • SYSDATE는 조회 시점의 데이터베이스 서버 날짜/시간을 반환
3. 읽기 전용
   • pseudocolumn 값을 직접 UPDATE하거나 INSERT할 수 없으며, 단지 조회용으로만 사용 가능
   • 예외적으로 시퀀스의 NEXTVAL/CURRVAL은 시퀀스 객체 상태를 갱신하거나 참조
4. 특정 문맥에서만 사용
   • 일부 pseudocolumn은 WHERE나 ORDER BY에 제한이 있음
     • 예: ROWNUM은 WHERE ROWNUM <= N 형태로만 정확히 동작
   • LEVEL은 계층형 쿼리(CONNECT BY)에서만 의미가 있음

 

라고 한다~ 참고해두자.