Oracle ROWNUM, ROW_NUMBER() 사용하기 (MySQL LIMIT와 차이점)

Oracle ROWNUM, ROW_NUMBER() 사용하기

Oracle ROWNUM, ROW_NUMBER()

MySQL에서는 SELECT 문에서 'LIMIT'를 통해 조회할 데이터 결과 값의 로우를 제한할 수 있습니다.

반면 Oracle에서는 'LIMIT'가 없기 때문에 결과 값의 로우를 제한하기 위해 'ROWNUM' 또는 'ROW_NUMBER()'를 사용하는데요.

 

해당 포스팅은 Oracle에서 'ROWNUM' 및 'ROW_NUMBER()'를 사용하는 방법과 MySQL(및 PostgreSQL)의 'LIMIT'와의 차이점을 살펴보겠습니다.

 

---

MySQL LIMIT, Oracle ROWNUM 사용 방법 비교

id (BIGINT or NUMBER)	name (VARCHAR)	state (VARCHAR)
1	김학생	NORMAL
2	이학생	NORMAL
3	최학생	WITHDRAWAL
4	박학생	NORMAL
5	강학생	NORMAL

(student table example data)

 

먼저 예시에 사용될 간단한 데이터입니다. 아래 MySQL 및 Oracle에서는 위와 동일한 데이터를 기준으로 테스트를 진행합니다.

 

 

-- MySQL
SELECT *
FROM   student
ORDER  BY name
LIMIT  2;

/*
RESULT
id / name / state
5  / 강학생 / NORMAL
1  / 김학생 / NORMAL
*/

 

MySQL에서 student 테이블을 name(이름)으로 정렬 후 'LIMIT'를 통해 결과에서 2개의 row를 제한하는 쿼리 및 결과입니다.

 

 

-- Oracle
SELECT *
FROM   student
WHERE  ROWNUM <= 2
ORDER  BY name; 

/*
RESULT
id / name / state
1  / 김학생 / NORMAL
2  / 이학생 / NORMAL
*/

 

이어서 Oracle에서 student 테이블을 name(이름)으로 정렬 후 'ROWNUM'을 통해 결과에서 2개의 row를 제한하는 쿼리 및 결과입니다.

 

***
오라클의 경우 다음과 같이 쿼리를 요청하면 원하는 데이터와 다른 결과가 나오는 것을 볼 수 있습니다.

이유는 'ROWNUM'이 'ORDER BY' 이전에 적용되기 때문인데요.

따라서 ROWNUM과 ORDER BY를 함께 사용할 경우 아래와 같이 테이블을 먼저 정렬해서 가져온 결과를 SELECT 문으로 한번 더 감싸 'ROWNUM'을 적용시키는 방법을 사용할 수 있습니다.

 

 

-- Oracle
SELECT *
FROM   (SELECT *
        FROM   student
        ORDER  BY name)
WHERE  ROWNUM <= 2;

/*
RESULT
id / name / state
5  / 강학생 / NORMAL
1  / 김학생 / NORMAL
*/

 

+++

추가로 ROWNUM의 경우 '<' 또는 '<=' 두 가지 연산자만 사용이 가능합니다.

('=1'의 경우에만 '='을 사용할 수 있습니다.)

 

 

---

Oracle ROWNUM, ROW_NUMBER()

-- Oracle ROW_NUMBER() PARTITION BY, ORDER BY 둘 다 적용
ROW_NUMBER() OVER(PARTITION BY [그룹핑할 컬럼] ORDER BY [정렬할 컬럼])

-- Oracle ROW_NUMBER() ORDER BY만 적용
ROW_NUMBER() OVER(ORDER BY [정렬할 컬럼])

 

오라클의 'ROW_NUMBER()'의 경우 각 PARTITION 내에서 ORDER BY절에 의해 정렬된 순서를 기준으로 고유한 값을 반환하는 함수입니다.

(ORDER BY에 대한 조건은 필수이며, PARTITION BY는 선택입니다.)

 

숫자는 1부터 시작하며 ORDER_BY_CASE에 지정된 순서대로 row들이 정렬됩니다.

'ROW_NUMBER()' 함수의 경우 'ORDER BY'와 결과 값의 로우에 대한 범위가 함께 제한될 때 사용되는데요.

사용에 대한 예시는 아래 내용을 통해 살펴보겠습니다.

 

-- MySQL
SELECT *
FROM   student
ORDER  BY name
LIMIT  0, 3;

/*
RESULT
id / name / state
5  / 강학생 / NORMAL
1  / 김학생 / NORMAL
4  / 박학생 / NORMAL
*/

 

MySQL에서는 다음과 같이 LIMIT에 범위를 설정하여 결과 값의 로우를 제한할 수 있습니다.

숫자의 시작점은 0부터이며, 위 예시의 SQL 같은 경우 첫 번째 로우부터 뒤에 값인 3만큼의 결과 값 로우를 제한하게 됩니다.

 

 

-- Oracle
SELECT *
FROM   (SELECT ROWNUM AS row_num,
               student.*
        FROM   student
        ORDER  BY name)
WHERE  row_num BETWEEN 1 AND 3; 

/*
RESULT
id / name / state
1  / 김학생 / NORMAL
2  / 이학생 / NORMAL
3  / 최학생 / NORMAL
*/

 

Oracle의 경우 'ORDER BY'를 적용하지 않는 경우 ROWNUM을 통해 결과 값의 로우에 대한 범위를 설정하여 제한할 수 있지만, 'ORDER BY'를 적용하는 경우 ROWNUM을 통해서는 원하는 결과를 얻을 수 없는데요.

 

이러한 경우 아래와 같이 'ROW_NUMBER()'를 사용하여 'ORDER BY'와 결과 값의 로우에 대한 범위를 함께 적용할 수 있습니다.

 

-- Oracle
SELECT *
FROM   (SELECT ROW_NUMBER() OVER(ORDER BY name) row_num,
               student.*
        FROM   student
        ORDER  BY name)
WHERE  row_num BETWEEN 1 AND 3; 

/*
RESULT
row_num / id / name / state
     1  / 5  / 강학생 / NORMAL
     2  / 1  / 김학생 / NORMAL
     3  / 4  / 박학생 / NORMAL
*/

 

 

 

 

< 참고 자료 >

https://gent.tistory.com/254
https://tychejin.tistory.com/201