[Oracle] 서브쿼리 (SubQuery ) - SELECT, FROM, WHERE

 

SELECT

단일 row 단일 column

select employee_id, first_name
    (select first_name
     from employees
     where employee_id = 100)
from employees;

 

FROM

다중 row 다중 column

select employee_id, first_name
from 
(select employee_id, first_name, salary
from employees
where department_id = 60)
where salary > 10000;

 

WHERE

다중 row 다중 column

select first_name, salary
from employees
where salary > (select avg(salary) from employees);

 

+ROWNUM

조회순번 =순위매기기

SELECT RNUM, employee_id, first_name, salary    
FROM (SELECT ROWNUM AS RNUM, employee_id, first_name, salary  -- 2. 순번 결정
   	  FROM    
        (SELECT employee_id, first_name, salary  -- 1. 급여 많은순으로 정렬
        FROM employees
        ORDER BY salary DESC)
    )
WHERE RNUM >= 11 AND RNUM <= 20;    -- 3. 원하는 순번 범위지정