SQL 서브쿼리(스칼라 서브쿼리, 인라인뷰, 중첩서브쿼리)

서브쿼리

하나의 sql문 안에 포함되어있는 또 다른 sql문을 말한다. 서브쿼리의 경우 괄호로 묶어서 사용하며 서브쿼리의 사용 위치에 따라 사용법과 부르는 이름이 달라진다. 

 

 

서브쿼리 종류

• 스칼라 서브쿼리
• 인라인뷰
• 중첩 서브쿼리

 

서브쿼리경우 group by를 제외한 거의 모든 쿼리문에서 사용이 가능한데 대표적으로는 스칼라, 인라인뷰, 중첩서브쿼리가 있으며, 스칼라 서브쿼리는 select 절에 사용하고, 인라인뷰는 from절에 사용하며, 중첩서브쿼리는 where절에서 사용한다. 

 

 

스칼라 서브쿼리

select 절에서 사용하는 서브쿼리이다. 

--스칼라 서브쿼리--

select * from emp;

select * from dept;

select e.empno, e.ename, e.job, e.deptno,
(select d.dname from dept d where e.deptno=d.deptno) as dname
from emp e;

--join으로 같은 결과 출력하기--
select e.empno, e.ename, e.job, e.deptno, d.dname
from emp e , dept d
where e.deptno=d.deptno;


select e.empno, e.ename, e.job, e.deptno,
(select d.dname d.loc from dept d where e.deptno=d.deptno)as dname
from emp e; --에러!--

select e.empno, e.ename, e.job, e.deptno,
(select d.dname from dept d where deptno=0) as dname
from emp e; --오류나지 않음--

select e.empno, e.ename, e.job, e.deptno, (select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;

스칼라 서브쿼리의 경우 항상 단일행으로 값이 나와야하며, 그때문에 하나의 출력값으로 사용할 수 있다. 위의 스칼라 서브쿼리문에서 where절이 사용되었는데 emp테이블에 있는 부서번호와 dept테이블의 부서번호와 정확이 일치하는 값들을 행별로 추출하기 때문에 dept테이블에 있는 부서이름을 마치 하나의 출력값으로 사용가능 하다는 것이다. 

 

스칼라 서브쿼리는 inner join으로도 똑같은 값을 낼 수 있는데

select e.empno, e.ename, e.job, e.deptno, d.dname from emp e , dept d where e.deptno=d.deptno;

이처럼 스칼라 서브쿼리는 join의 대체역할도 가능하다.

 

select e.empno, e.ename, e.job, e.deptno, (select d.dname from dept d where deptno=0) as dname from emp e;

만약 스칼라 서브쿼리에서 사용되는 where절의 조건이 아무것도 출력되지 않도록 하여, 스칼라서브쿼리로 아무값도 출력되지 않는 상태에서 전체쿼리를 실행해도 스칼라서브쿼리의 출력값이 null이 될뿐 에러는 나지 않고 정상실행된다.

 

 

인라인 뷰

인라인 뷰는 from절에서 사용되는 서브쿼리이며 또 다른 테이블의 역할을 한다.

--인라인 뷰--
--1대 N인라인뷰--
select e.ename, e.job, e.deptno, d.dname
from emp e, 
(select deptno, dname from dept) d
where e.deptno= d.deptno;

select deptno, dname from dept;

--1대1 인라인뷰--
select e.dept_sal, d.deptno, d.dname
from dept d, (select deptno,max(sal) as dept_sal from emp group by deptno) e
where e.deptno=d.deptno;

select deptno,max(sal) as dept_sal from emp group by deptno;

--join으로 작성--
select d.deptno, max(e.sal), d.dname
from emp e, dept d
where d.deptno= e.deptno
group by d.deptno, d.dname;


--가상의 테이블사용하기--
select e.empno, e.ename, e.job, c.company
from emp e, (select '무한상사' as company from dual) c;

인라인 뷰에는 1대1 연결이 있고 1대N연결이 있다. 

 

1대1연결 인라인 뷰

select e.dept_sal, d.deptno, d.dname from dept d, (select deptno,max(sal) as dept_sal from emp group by deptno) e where e.deptno=d.deptno;

해당 쿼리의 인라인뷰만 실행시키면 오른쪽과 같은 결과가 나온다. 여기서 조건을 부서번호가 같은 값만 보여달라고 했기때문에 1대1인 이퀄조건(=)이 성립된다. 

 

1대N 인라인 뷰

select e.ename, e.job, e.deptno, d.dname from emp e, (select deptno, dname from dept) d where e.deptno= d.deptno;

1대N인라인 뷰의 경우 오른쪽에 출력된 값에 1대1로 매칭되어 4가지의 값만 나오는게 아니라 인라인뷰에 출력된 값에 대응되는 값 여러개를 함께 출력하는 방식이다. 1대N역시 join으로 대체하여 출력이 가능하다.

 

dual과 인라인뷰

select e.empno, e.ename, e.job, c.company from emp e, (select '무한상사' as company from dual) c;

인라인뷰의 경우 테이블의 역할을 하기 때문에 dual테이블과도 함께 사용이 가능하다. dual테이블에 값을 적고 출력해보면 오른쪽과 같은 결과가 나오는데 이 dual테이블의 값을 메인쿼리와 매핑하면 왼쪽과 같이 모든 값에 매핑되어 값이 출력된다.

 

 

중첩쿼리(where절)

--중첩서브쿼리--

--단일행 비연관서브쿼리--
select ename, sal, job
from emp
where sal > (select avg(sal) from emp);


--다중행 비연관 서브쿼리--
select ename, sal, job
from emp
where sal in (select sal from emp where sal >= 2000);

select sal from emp where sal >= 2000;


--단일행 연관서브쿼리--
select e.ename, e.empno, e.job, e.deptno
from emp e
where e.ename=(select ename from emp where ename = 'JONES');

select ename from emp where ename = 'JONES';

--다중행 연관서브쿼리--
select e.ename, e.empno, e.job, e.deptno
from emp e
where exists (select 1 from dept d where e.deptno= d.deptno);

where절에 사용하는 중첩서브쿼리에는 비연관서브쿼리와 연관서브쿼리가 존재하며, 종류는 단일행과 다중행으로 나눌 수 있다.

단일행은 서브쿼리의 출력결과가 단일행으로 나오며, 이 경우 단일행 연산자를 사용하여 서브쿼리를 메인쿼리와 연결시켜줄 수 있다. 다중행의 경우 서브쿼리의 출력결과가 다중행으로 출력되기때문에 단일행 연산자가 아닌 다중행 연산자를 사용하여야 한다.

 

다중행 비연관 서브쿼리

select ename, sal, job from emp where sal in (select sal from emp where sal >= 2000);

다중행 비연관서브쿼리는 메인쿼리의 값을 참조하지 않는 비연관서브쿼리이다. 서브쿼리의 결과만 출력하면 오른쪽과 같은 결과가 출력되는데 다중행으로 출력된다. 출력된 값을 가지고 where절 조건에서 이와 같은 값에 해당하는 값들을 출력하여 왼쪽과같은 값을 출력하는 것이다. 본 쿼리문은 연습용이기때문에 같은 emp테이블을 사용하고 있어, 연관서브쿼리처럼 보일지 모르나, 메인쿼리의 emp와 서브쿼리에 사용된 emp는 전혀 다른 테이블이다.

 

단일행 비연관 서브쿼리

select e.ename, e.empno, e.job, e.deptno from emp e where e.ename=(select ename from emp where ename = 'JONES');

단일행 연관서브쿼리는 메인쿼리의 값을 참조하지 않는 비연관서브쿼리이다. 서브쿼리의 결과만 출력하면 오른쪽과 같은 결과가 출력되는데 단1건만 출력되는 단일행 쿼리문이다. 이 결과문을 가지고 이퀄조건을 사용하여 출력된 서브쿼리의 결과와 똑같은 결과만을 출력하는 쿼리가 단일행 비연관 서브쿼리이다.

 

다중행 연관서브쿼리

select e.ename, e.empno, e.job, e.deptno from emp e where exists (select 1 from dept d where e.deptno= d.deptno);

다중행 연관서브쿼리는  서브쿼리의 where절 조건에서 메인쿼리의 값을 참조하여 값을 추출하는 방식이다. 이는 메인쿼리와 서브쿼리가 연관되어있기때문에 연관서브쿼리라고 하며, 쿼리가실행되는 순서상 메인쿼리문의 from절이 수행되고 where절이 수행되기때문에 서브쿼리의 쿼리문에서 메인쿼리의 테이블을 인식하여 참조할 수 있는것이다.

 

 

---

 

학습일기

확실히 서브쿼리부분이 가장 어려운것 같다..쿼리문쓰면서 이렇게쓰면 출력되겠지..? 하며 썼는데 오류도 여러번 났었고...ㅎㅎ

그만큼 열심히 연습해야겠다는 뜻이겠지만...그래도 처음 배웠을때보다는 확실히 쉽게 서브쿼리가 이해되고 쿼리문이 작성되는것 같다..! 서브쿼리가 들어가면 쿼리문이 순식간에 복잡해보이는데 차근차근 하나씩 분석하고 쓰다보면 그래도 그 로직이 뚜렷하게 보일때가 있다. 그 순간들을 여러번 마주하다보면 서브쿼리 쓰는것도 언젠간 늘겠지...!!ㅠ