26일차(1). 다중행 서브쿼리(Multiple SubQuery)

오늘 아침의 SQL 몸풀기(Join을 이용)

 

new york에 근무하는 부서번호, 부서명 , 소재지, 사번, 사원명, 급여를 조회하고, 급여를 많이 받는 사원부터 출력하라

 

--new york에 근무하는 부서번호, 부서명 , 소재지, 사번, 사원명, 급여
--급여를 많이 받는 사원부터 출력

select e.empno, e.ename, d.loc, d.deptno, d.dname, e.sal
from emp e join dept d on e.deptno = d.deptno
where loc = upper('new york')
order by e.sal desc;

--natural join 이용

select e.empno, e.ename, d.loc, deptno, d.dname, e.sal
from emp e natural join dept d
where loc = upper('new york')
order by e.sal desc;

명확하게 LOC에 대문자로 'NEW YORK' 을 조건으로 제시해도 되지만, 추후 유기적인 데이터값이 들어올수있기때문에

 

UPPER나 LOWER를 앞으로는 사용하는 습관을 들일려고 한다.

 

 

여기서 where절에 서브쿼리를 수행하려고 했지만 단일행이 아니라서 수행이 불가능하다고 오류메세지가 떴다.

 

ORA-01427: single-row subquery returns more than one row

 

이러한 이유는 where절에서 특별한 함수 없이, 서브쿼리가 사용되면 단일행 서브쿼리로 인식되기때문에, 1개의 행만 조회가 가능해야 한다 여기서 전체 행의 조회값이 1개가 아닌 3개라서 추가로 함수를 이용해서 다중행서브쿼리로 처리 해야 서브쿼리가 가능할거 같다.

 

이것이 다중행 서브쿼리인데, 다중행 서브쿼리를 사용하기 위해서는 IN, ANY, ALL,EXIST같은  함수를 이용해줘야 다중행 서브쿼리를 사용할수 있다.

 

IN : or라고 생각하면 편할거같다. 예를들어 DEPTNO가 10이거나. 20이거나. 30인 데이터를 조회하는 SELECT 문을 서브쿼리로 두고 in을 붙여주면 DEPTNO 가 10인 데이터, 20인데이터, 30인 데이터 다 출력된다

 

select empno, ename, sal, deptno
from emp
where sal in(select sal
            from emp
            where deptno = 10)
order by sal;

 

여기서 서브쿼리가 DEPTNO가 10인 직원들의 SAL은 1300, 2450, 5000이다. 이를 in으로 메인쿼리에 조건절로주었으니

 

메인절에서도 1300,2400,5000인 직원들이 검색되는것이다.

 

Any 와 Some : 만족하는 결과가 하나라도 있으면 True

 

select empno, ename, sal, deptno
from emp
where sal >= any(select sal
            from emp
            where deptno = 10)
order by sal;

 

이 쿼리문을 보자. 위에서 서브쿼리의 결과값이 1300,2400,5000이였다. Any는 이중 조건이 하나라도 참이면 True이다.

 

따라서 메인쿼리의 조건문은 1300이상, 2400이상, 5000이상의 조건들을 다 출력해준다. 즉, 1300이상은 당연히 2400이상이며, 2400이상은 당연히 5000이상이다. 따라서 1300 이상의 모든 조건들을 출력해준다

 

 

 

 

EXIST, NOT EXIST : 서브쿼리의 결과가 존재하고, 존재하지않고에 따라서 메인쿼리의 수행여부를 결정한다

서브쿼리의 결과가 있다면 메인쿼리를 수행하고, 서브쿼리의 결과값이 없다면 수행하지 않는다.

 

select empno, ename, deptno
from emp
where Exists( select deptno      --서브쿼리의 결과가 있으면 그 결과를 다보여준다, where절 조건이 무시가되고 메인쿼리를 실행
                        from dept
                        where dname = upper('sales'));

 

DEPTNO의 SALES 부서는 존재한다. 따라서 성립하기때문에 메인쿼리를 수행한다. 

 

반대로 없는부서를 넣게되면

 

select empno, ename, deptno
from emp
where Exists( select deptno      --서브쿼리의 결과가 없으면 메인쿼리 결과도 나오지않는다, 서브쿼리의 결과가 1건이라도 존재해야
                        from dept  -- 메인쿼리 셀렉트가 가능하다
                        where dname = upper('sales123'));

 

일부러 존재하지 않는 부서를 서브쿼리의 조건문으로 대입해줬다.

 

결과값이 없다. EXIST의 조건이 발동하지 않아 메인쿼리도 값이 없는것으로 나온것이다.

 

ALL : ALL은 메인쿼리의 조건이 서브쿼리의 모든 조건을 부합하는, 즉 AND라고 이해하면 쉬울거같다.

 

select empno, ename, sal, deptno
from emp
where sal >= all(select sal
            from emp
            where deptno = 10)
order by sal;

 

여기에서 서브쿼리의 검색결과는 1300,2450,5000이다.

 

메인쿼리에서 SAL이 서브쿼리의 검색결과보다 크거나 같은, 즉 이상이여야한다. 1300보다 크거나 같고 2450보다 크거나 같고, 5000보다 크거나 같다. 즉 Any와 Some과 마찬가지로 접근하면 1300보다 크거나 같은 값은 2450보다 크거나 같고

이는 5000보다 크거나 같은 수, 즉 5000보다 크거나 같은 직원이 출력된다

 

 

직원들중에 5000보다 크거나 같은 사람은 KING 한명이다.

 

마지막으로 수업시간에 좀 난해했던 SQL 문제를 다시한번 풀어봤다.

 

--ALLEN이 근무하는 부서의 인원수이상 사원이 근무하는 부서명, 사원수 구하기

 

먼저 ALLEN이 근무하는 부서는 어떤 부서인지 알아보자.

 

select ename, deptno
from emp
where ename = upper('allen');

ALLEN은 30번부서에서 근무한다

 

자 그럼 이 30번 부서가 어떤 부서인지 알아보자

 

select dname, deptno
from dept
where deptno = (select deptno
                    from emp
                    where ename = upper('allen'));

단일행 서브쿼리를 이용하여 ALLEN이 근무하는 부서의 이름과, 부서번호를 출력해줬다.

 

ALLEN은 30번부서 즉 SALES부서에 근무하고 있다.

그럼 이 부서에 대한 사원 수를 구해보자.

 

select count(deptno)
from emp
where deptno = (select deptno
                    from emp
                    where ename = upper('allen'));

근무하는 사원에 대한 정보는 EMP 테이블에 있기때문에 중간에 메인쿼리의 FROM절을 EMP 테이블로 변환해주었다.

 

 

6임을 알수있다. 그럼 이제 다 구했다.

 

이 6이라는 숫자를 다시 메인쿼리로 비교하여 부서명과 사원수를 출력해줄것인데.

 

부서명은 DEPT 테이블에 있고 사원정보는 EMP 테이블에 있기때문에 Join을 이용해주겠다. 그리고 집계함수를 비교해줘야하니 Group by도 이용해주겠다.

 

select d.dname, count(dname)
from dept d join emp e on d.deptno = e.deptno
group by d.dname
having count(d.dname) >= (select count(deptno)
                        from emp
                        where deptno = (select deptno
                                            from emp
                                            where ename = upper('allen')));

 

 

SALES에는 6명이 근무하고, ALLEN이 근무하는 부서이다. 이상의 조건을 줬는데 SALES가 나오는걸 보니

 

6명 이상 근무하는 부서는 없는거같다. 혹시나 잘못나온거 아닐까 싶어서 비교 연산자를 반대로 줘봤다.

 

 

 

비교연산자를 미만으로 주니 3명씩 있는 부서가 출력된다. 정상적으로 쿼리문이 작동한것을 알수 있었다.