오라클 ROWNUM

* MYSQL에서는 LIMIT이지만, 오라클에서는 ROWNUM

SELECT ROWNUM, E.*
FROM EMP E 
ORDER BY E.SAL DESC; -- CTRL + ENTER : 쿼리실행

SELECT ROWNUM, T.*
FROM (SELECT *
FROM EMP
ORDER BY SAL DESC) T;

SELECT * 
FROM (SELECT ROWNUM AS RNUM, T.*
FROM (SELECT *
FROM EMP
ORDER BY SAL DESC) T)
WHERE RNUM >=5 AND RNUM <=7;

SELECT * 
FROM (SELECT ROWNUM AS RNUM, E.*
FROM (SELECT * 
FROM EMP
WHERE JOB NOT IN ('SALESMAN', 'PRESIDENT') 
ORDER BY SAL DESC) E)
WHERE RNUM BETWEEN 1 AND 5;

----

*SELECT 의 FROM 절에서 내부적으로 ROWNUM을 가지게 됨.

SELECT ROWNUM, E.* 

FROM (SELECT * FROM EMP ORDER BY ENAME ASC) E;

*FROM절에 ROWNUM 속성이 없으므로 WHERE절에서 ROWNUM으로 조건검색을 할 수 없다.

SELECT ROWNUM, E.* 
FROM (SELECT * FROM EMP ORDER BY ENAME ASC) E
WHERE ROWNUM BETWEEN 3 AND 6;

*이제는 FROM 절에 RNUM이 생겼으므로 WHERE절에서 조건으로 RNUM 사용 가능. ROWNUM은 예약어이므로 AS 로 리네임.

SELECT * 
FROM (
SELECT ROWNUM AS RNUM, E.* 
FROM (SELECT * FROM EMP ORDER BY ENAME ASC) E
)
WHERE RNUM BETWEEN 3 AND 6;