SQL Sub Query Select 절 = Scalar 서브쿼리

스칼라 서브쿼리는 Where 절이 없다면 데이터 분석함수로 쓰는게 더 속도가 빠르다. 

 

예제  : 사원이름,월급, 사원테이블의 최대월급 출력

select ename, sal, (select max(sal) 
                     from emp ) as 최대월급
from emp;

문제 : 사원이름,월급, 사원테이블의 평균월급 출력

select ename, sal, (select avg(sal) 
                    from emp ) as 최대월급 ——>컬럼 가공하는것 안좋음
from emp; ——>튜닝전

 

emp 테이블을 두번 출력했기때문에 성능이 느리다.

---

스칼라 서브쿼리 대신에 데이터 분석함수를 이용하여 출력 해 보겠다. 

select ename, sal, avg(sal) over () 
from emp;

 

sum (sal) over ()

or

avg (sal) over () ——→ 작성하면 사원테이블 전체의 토탈, 평균월급이 출력됩니다.

분석함수를 이용한 sql 실행물은  emp 테이블을 한번 출력했으므로 위에 스칼라 서브쿼리를 이용한  sql 보다 성능이 좋다

---

문제 : 사원이름, 월급, 사원테이블 전체의 최대월급, 사원테이블 전체의 최소월급, 사원테이블 전체의 토탈월급 출력

튜닝전 sql 

select ename, sal, (select max(sal) from emp ) as 최대월급,
                   (select min(sal) from emp) as 최소월급,
                   (select sum(sal) from emp) as 토탈월급
 from emp;

 

scalar 서브쿼리 사용

성능이 느리다. emp 테이블을 4번이나 출력하게 됨

set autot on 을 셀렉절 위에 입력하고 도스창에 검색해보면 42...

 

튜닝후 sql

select ename, sal, max(sal) over () as 최대월급
                 , min(sal) over () as 최소월급
                 , sum(sal) over () as 토탈월급 
from emp;

 

데이터 분석함수를 이용하여 sql 작성

emp 테이블을 1번만 access 함

set autot on 을 셀렉절 위에 입력하고 도스창에 검색해보면 6

but where 절을 쓰게되면 select 절에 서브쿼리를 쓸 수 밖에없다.

문제 : 이름, 월급, 부서번호가 20번인 사원들 토탈월급 출력

select ename, sal, (select sum(sal) 
                    from emp 
                    where deptno =20) as "부서번호20의총월급" 
from emp;

 

where 절때문에 select 절의 서브쿼리 사용 하고 있다.

---

문제 374 사원이름 , 월급, 부서번호20번인 사원들의 토탈, 최대,최소 월급 출력

select ename, sal, (select sum(sal) 
                    from emp 
                    where deptno =20)토탈월급,
                     (select max(sal) 
                       from emp 
                       where deptno = 20)최대월급, 
                        (select min(sal) 
                          from emp 
                           where deptno =20)최소월급
from emp;

 

튜닝전 sql 악성 sql 식

emp 테이블이 너무 많이 출력된다. ㅜㅜ

---

튜닝 ing sql ~ (1) 

select ename, sal, (select sum(sal), max(sal), min(sal)
                    from emp 
                    where deptno =20) 
from emp;

 

 

→ 값의 수가 너무 많습니다. 오류 (select 절을 보면 값을 3개나 출력하고 있다)

이유? select 절의 서브쿼리(스칼라) 는 값을 하나만 출력 할 수 있다.

---

(2) 수정 sql 

select ename, sal, (select sum(sal)||max(sal)||min(sal) 
                    from emp 
                    where deptno =20)
from emp;

연결연산자를 사용하여 값을 1개로 만들어 주었다.

but 값이 다 이어져서 나오게 되므로 해결해야한다.

---

(3) 각 컬럼에 공백을 채워넣어  구분을 한다. (rpad) 

select ename, sal, (select substr(rpad(sum(sal),10,' '),1)|| 
                           substr(rpad(max(sal),10,' '),1)|| 
                           substr(rpad(min(sal),10,' '),1) 
                   from emp 
                   where deptno =20) as 집계 
from emp;

 

이거를 각각의 컬럼으로 뽑아 내야한다 (substr )이용, 해당 내용을 in line view

---

완성 SQL 

select ename, sal, substr(집계,1,10) 토탈월급, 
                   substr(집계,11,10) 최대월급, 
                   substr(집계,21,10) 최소월급
from ( select ename, sal, ( select rpad(sum(sal),10,' ') ||
                                   rpad(max(sal),10,' ') ||
                                   rpad(min(sal),10,' ') 
                           from emp
                           where deptno = 20 
                           ) 집계
       from emp );

 

연결연산자로 만들어 놓은 답을 집계로 묶고 하나씩 출력 해야하기때문에

from 절의 서브쿼리인 in line view 를 사용한다.

그 다음 substr 로 하나씩 추출 해 주면 된다.

---

문제 : (join 이용) SQL

select ename, sal, s, m1, m2 
from emp natural join (select sum(sal)as s , max(sal) as m1, min(sal) as m2 
                       from emp 
                       where deptno =20);

---

NATURAL JOIN 이용

emp 테이블이 14건이고 natural join 다음에 나오는 인라인뷰의 select 문장의 결과가는 1건이므로 그냥 조인조건없이 조인해서 14x1 = 14 건이 출력이 된것. 

---

문제 374 의 또다른 SQL (데이터분석함수 이용)

select ename, sal , sum(decode(deptno , 20, sal , null) over () 토탈월급, 
                    max(decode(deptno, 20, sal , null ) over () 최대월급,
                    min(decode(deptno, 20, sal , null) over () 최소월급
from emp;

데이터 분석함수 이용

ename, sal, 이 함께 나와야한다. 그래서 SUM( ) OVER () 중 OVER 를 쓰지 않으면 그룹함수 오류난다.

---

문제 375

튜닝전 sql

select sum(sal), max(sal), min(sal) 
from emp 
where deptno =20;

 

문제 이름과, 나이 그리고 총나이, 최대나이, 최소나이를 출력하시오 

select ename, age, sum(decode(lower(telecom),'kt',age, null)) over () 총나이 , 
                   max(decode(lower(telecom),'kt',age, null)) over () 최대나이,
                   min(decode(lower(telecom),'kt',age, null)) over () 최소나이 
from emp14;

select ename, age , sum_a, max_a, min_a 
from emp14 natural join (select sum(age) as sum_a, 
                                max(age) as max_a ,
                                min(age) as min_a 
                         from emp14);

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

복습문제

문제 380 직업을 출력하고 DECODE 사용하여 직업이 PRESIDENT 면 NULL 나머지 직업은 SAL 출력하시오

select job, decode(job, 'PRESIDENT', NULL, SAL)
from emp;

 

문제 381 위의 결과에서 월급이 높은 사원 부터 출력 (null 은 맨밑) (president 의 월급은 나오지 않도록 ) 

select job, decode(job, 'PRESIDENT', NULL, SAL) as sal 
from emp
order by 2 desc ;

 

앞의 'PRESIDENT '가 문자형이여서 null 값이 문자형으로 치환되어버렸다.

따라서 마지막 SAL 또한 문자형으로 암시적 형 변화가 되어서

order by 했을때 월급이 높은 사원이 아닌, 문자형으로 desc적용되어 출력 됐다.-> (9 가 문자로 치면 가장 높은 수이다. )

---

그래서 다음과 같이 SQL 을 개선해야한다.

select job, decode(job, 'PRESIDENT', to_number(null), SAL) as sal 
from emp 
order by 2 desc nulls last ;

 

order by 절에 sal 로 정렬하게 되면, sal 에는 null 값이 없기 때문에 null 이 계속 위로 올라오게 된다.

그래서 order by 2 로 하여야 null 포함된순으로 desc 되고 nulls last 도 적용이된다.

---

문제 : 이름과 나이 그리고 통신사가 kt 인 학생들의 총나이 최대나이 최소나이 를 출력하시오 

select ename, age , substr(집계, 1, 10) as sum_age,
                    substr(집계, 11, 10)as max_age,
                    substr(집계, 21, 10) as min_age 
from ( select ename, age, (select rpad(sum(age),10,' ')||
                                  rpad(max(age),10,' ')||
                                  rpad(min(age),10, ' ') 
                           from emp14 
                           where lower(telecom) = 'kt'
                           ) as 집계
       from emp14 
       ) ;