[ORACLE] scalar subquery / inline view

 

※ 서브쿼리 위치별 이름

 - 서브쿼리는 오는 위치에 따라서 그 이름이 다름

 

select : scalar subquery 

from : inline view

where : subquery

 

 

 

[1] scalar subquery

select (subquery)

 - select절에 오는 서브쿼리로 한 번에 결과를 1행씩 반환함

        ( subquery 결과가 select 절에서 하나의 컬럼 역할을 하는거임) 

 - select문에서 사용하려면 단일 서브쿼리 중에서 단일행이면서 단일컬럼인 경우만 가능

        ( = 임의의 숫자나 문자로 인식할 수 있는 서브쿼리 )

 

 

 

▷ scalar subquery 는 outer join 과 동일

 - 다른테이블에 있는 컬럼 (컬럼의 수가 적은경우) 을 추가하고싶을 때

    join 대신 사용하면 좋다 ! 가독성 good 성능 good 그런데 추가하고싶은 컬럼수가 많으면 그냥 join 하셈

 

 

ex ) employees 테이블과 departments 테이블을 조회하여 사원정보, 부서명을 출력하시오

-- outer join (left)

select e.*, d.DEPARTMENT_NAME 부서명
from employees e left join departments d
on e.DEPARTMENT_ID=d.DEPARTMENT_ID
order by e.department_id desc;

--scalar subquery

select e.*, (select department_name from departments d --select 절에서 조인
                where e.department_id = d.department_id) 부서명
from employees e
order by e.department_id desc ;

 = > 사원정보를 모두 출력하고, 부서번호가 없는 경우 scalar subquery 로 조회한 부서명은 null 값이 됨

 

 

 

ex ) 각 부서에 해당하는 사원수 구하기

--departments, employees 이용

 select DEPARTMENT_ID, DEPARTMENT_NAME,LOCATION_ID,
    (select count(*) from employees e 
        where e.DEPARTMENT_ID=d.DEPARTMENT_ID) "사원수"
from departments d;

 

 

 

 

▷ self join scalar subquery

 ex ) employees에서 사원정보와 직속상관의 이름, 급여 등급 구하기

employees 테이블 : 

neena 의 직속상관의 사원번호 : 100 => 따라서 neena 의 직속상관은 steven 이다

.. 세상에 이렇게 가독성떨어질수가 !! 

 

△ manager_id 와 employee_id 가 매칭되는 first_name 로 이루어진 컬럼을 만들꺼다. self join 을 이용해서 ! 

select a.*, (select first_name from employees b
                where a.manager_id = b.employee_id) 직속상관이름
from employees a;

 = > 여기서 주의할점은 직속상관이 main 이고 사원이 sub 라는것.

       이런식으로 () 안의 subquery 가 하나의 컬럼으로서 출력되는것이다.

 

 

근데 직속상관이 없는 보스 Steven 의 직속상관명은 null 이다. 보완해보자

nvl 과 case 를 사용할 수 있지만 , case 가 가독성이 높으니 case 를 사용할꺼임

select a.*, 
    case when manager_id is null then '사장'
    else
         (select b.FIRST_NAME
         from employees b where b.EMPLOYEE_ID=a.manager_id)
    end 직속상관이름
from employees a;

 = > 오른쪽 끝에 직속상관이름 컬럼이 붙었고, 상관이없는 Steven 의 직속상관이름은 사장 이라고 출력된다

 

 

▷ scalar subquery 를 이용해 백분율구하기

ex ) 학과별 교수의 인원수와 백분율 구하기- professor 테이블 이용

select deptno, count(*) "인원수", 
    round((count(*)/ ( select count(*) from professor))*100, 1) "백분율"
from professor
group by deptno
order by deptno;

  = > subquery : professor 총인원수 

( subquery 나누기 학과별교수인원수 *100 )을 소수 1자리수에서 반올림하여

"백분율" 이라는 컬럼명으로 데이터 가져와라 ▽

 

---

 

[2] ★inline view★

   from (subquery)

       - from절에 오는 서브쿼리

 

 

 

inline view 들어가기전에 !

 

▶ 유령컬럼

    ( = 의사컬럼(pseudoColumn), 모조컬럼 )

 

- 테이블에 있는 일반적인 컬럼처럼 행동하기는 하지만, 실제로 테이블에 저장되어 있지 않은 컬럼

 

    ● ROWNUM : 쿼리의 결과로 나오는 각각의 ROW들에 대한 순서값을 가리키는 의사컬럼
                - 주로 특정 개수나 그 이하의 row를 선택할 때 사용됨 (예 : 상위 n개 조회)
                - 고유순서
    
    ● ROWID : 테이블에 저장된 각각의 row들이 저장된 주소값을 가진 의사컬럼
                - 모든 테이블의 모든 row들은 오직 자신만의 유일한 rowid값을 갖고 있다 
                - 고유 행별주소

 

 

▽ 조건없이 rownum 기준 상위 5개 데이터 뽑아라

select rownum, empno, ename, sal, rowid as "ROW_ID"
from emp
where rownum < =5;

  = > rownum 기준 상위 5개 데이터가 출력된다.

 

 

 

▽ 조건지정 : ename 오름차순 상위 5개 데이터 출력

select rownum, empno, ename, sal, rowid as "ROW_ID"
from emp
where rownum < =5
order by ename;

 

 = > rownum 순서가 뒤죽박죽

 

 

이럴때 !!!!

★★★inline view 를 사용해줌★★★  - from 절에 쿼리를 넣어서 

 

 

▶ inline view 기본틀

select rownum
from
(

)
where

 

▽ 이렇게 from 뒤에서 쓰면 rownum 이 섞이지않음 이유는 모름

select rownum, empno, ename, sal
from
(
    select rownum, empno, ename, sal
    from emp
    order by ename
)A
where rownum <=5;

 

 

 

ex ) exam_01 테이블에서 점수가 높은 순으로 6명 조회하기

select *
from
(
    select studno, total
    from exam_01
    order by total desc
)
where rownum<=6;

 

---

 

 

▶salary로 내림차순 정렬해서 2~4순위 사이인 데이터 조회하기

 

  rownum 특이사항 :

  - rownum 은 1이 포함되지 않으면 데이터를 읽어오지 못한다 (ex. 2~4 번째 데이터를 가져와라 => 불가) 

  - 따라서 1이 포함되도록 조건을 지정해야 한다.

     ㄴ 어떻게 ? 별칭을 지정해서 ! (rownum 이라는 이름만 사용안하면되는거임) 

 

▽ 별칭지정해서 실행하기

select rownum as ROWCNT, A.*
from 
(
    select *
    from employees
    order by salary desc
)A
where ROWCNT >= 2 and ROWCNT<= 4;

error : "ROWCNT" 부적합한 식별자

왜 ?!!!! 또 왜 ? !!!!!! => 실행순서오류

 

 

★ 오라클 실행순서 : 

FROM ON JOIN WHERE GROUPBY CUBE/ROLLUP HAVING SELECT ORDERBY 

 

select 에서 rownum을 ROWCNT 로 선언하기 전에 where 절이 시행되었으니

프로그램은 ROWCNT 가 뭔지모르는거다.

 

▽ 쿼리를 inline view 를 통해 from 절에 넣어서 가장먼저 시행되게해보자

select A.*
from
(
    select rownum as ROWCNT, A.*
    from 
    (
        select *
        from employees
        order by salary desc
    )A
)A
where ROWCNT >= 2 and ROWCNT<= 4;

 원하는대로 salary로 내림차순 정렬해서 2~4순위 사이인 데이터를 가져옴 

 

 

 

▷ inline view 를 통한 join

★자주사용함

 

예제 ) employees에서 사원정보를 조회하고, job_id별 salary평균도 출력

 - 테이블 + 인라인뷰

 - 데이터집합 (<=얘를 하나의 테이블로 보는거임) 과 employees테이블을 join 

select
from employees e join() A
on -- 틀잡기

select e.*, A.평균급여
from employees e join(
        select job_id ,avg(nvl(salary,0)) 평균급여
        from employees
        group by job_id 
) A
on e.job_id = A.job_id;

subquery 에서 직군별 평균급여 가 나옴.

위 데이터와 employees 테이블을 job_id 로 연결해서 조인해서 

jobid 별 평균급여를 각 직원정보에 연결해줌

 

 

테이블 + 인라인뷰를

인라인뷰 + 테이블로 바꿔보기

select e.job_id, employee_id, first_name, hire_date, salary, A.평균급여
from (
    select job_id, avg(nvl(salary,0)) 평균급여
    from employees
    group by job_id
)A join employees e 
on e.job_id = A.job_id;

 

당연히 인라인뷰 + 인라인뷰 형태도 가능하다

 

 

 

 

▷ inline view 를 통한 로그인 처리

select case (select count(*) from member 
                where id='simson' --member 테이블에 simson id 가 있으면(count = 1) 
                and passwd='a1234')
            when 1 then '로그인 성공' -- 아이디 : simson, 비번 : a1234 = 일치
            else
                case (select count(*) from member where id='simson') --아이디 : simson
                    when 1 then '비밀번호 불일치' -- 비밀번호불일치
                    else '해당 아이디가 존재하지 않음' -- 둘다아님
                end -- 이런식으로 3가지 경우의 수를 모두 넣는것임
            end 로그인
from dual;

 

▷ inline view 를 이용해 학년별, 성별 인원수와 백분율 구하기

select grade, 성별, count(*) "인원수",
    round(count(*)/(select count(*) from student) *100, 2) "백분율" 
from
(
    select studno, name, grade, jumin,
        case when substr(jumin,7,1) in ('1','3') then '남' else '여' end 성별
    from student
)A
group by grade, 성별
order by grade, 성별;

 

 

▽ 쌤이 좋은 예제라고 말씀하셨다.. 

    백지상태에서 다시 코드짜보기 도망가지말기

각 부서에 속하는 사원정보를 조회하고, 부서별 평균급여도 출력하시오
select * from departments;
select * from employees;

--[1] 각 부서에 속하는 사원정보를 조회하는 데이터 집합 구하기
select d.DEPARTMENT_ID, d.DEPARTMENT_NAME, e.EMPLOYEE_ID,
    e.FIRST_NAME, e.HIRE_DATE,
    salary+salary*nvl(commission_pct,0) 급여
from departments d right join employees e
on d.DEPARTMENT_ID=e.DEPARTMENT_ID;

--[2] 부서별 평균급여를 조회하는 데이터 집합 (salary+salary*commission_pct)
select department_id, 
    round(avg(nvl(salary+salary*commission_pct, salary))) 평균급여
from employees 
group by department_id;

--[3] 두 개의 데이터집합을 이용해서 outer join한다 --실무에서 많이씀
select A.*, B.평균급여
from (
    select d.DEPARTMENT_ID, d.DEPARTMENT_NAME, e.EMPLOYEE_ID,
        e.FIRST_NAME, e.HIRE_DATE,
        salary+salary*nvl(commission_pct,0) 급여
    from departments d right join employees e
    on d.DEPARTMENT_ID=e.DEPARTMENT_ID
) A left join (
    select department_id, 
        round(avg(nvl(salary+salary*commission_pct, salary))) 평균급여
    from employees 
    group by department_id
) B
on A.department_id=B.department_id
order by A.department_id;

 

---

 

[3] subquery

 where (subquery) 
     - where절에 오는 서브쿼리
     - where 절에 오는건 그냥 subquery

 

= > go to day 30 ▽

[ORACLE] - [풀스택] 30일차 개발일지_SUBQUERY/ 단일행 , 다중행, 상호연관 서브쿼리 / exists 연산자