[DB] 5. SubQuery

1. 서브쿼리 (Where 절)

1. DALLAS 사는 직원 출력하기

-- 서브쿼리 종류 3가지

-- 1. 서브쿼리 (where)
-- 데이터가 중복되도록 만들지 않는다. 변경에 대처하기 힘들다.
-- 연관 관계를 가지게 테이블을 두 개로 만든다.
-- (FK : 다른 테이블을 참조하는 키)
-- (PK : 내 테이블에서 행을 유일하게 결정하는 키)

-- DALLAS 사는 직원 출력해주세요.
select * 
from emp
where deptno = 20;

select deptno
from dept
where loc = 'DALLAS';

select * 
from emp
where deptno = (select deptno
from dept
where loc = 'DALLAS');

2. research 부서의 직원 출력하기

-- research 부서의 직원 출력해주세요.
-- emp와 dept가 deptno로 연관관계를 맺고 있다 (-> FK)
select *
from emp
where deptno = (select deptno
from dept
where dname = 'RESEARCH');

2. 인라인 뷰 (From절)

select ename, sal*12 '연봉'
from emp
where 연봉 = 9600;

-- 새로 만들어진 테이블(뷰)의 이름(별칭) 만들어줘야 됨.
select e.*
from ( 
	select ename, sal*12 '연봉'
    from emp
) e
where e.연봉 = 9600;

-- having 안써도 됨
select *
from
(
select avg(sal) 'avg_sal'
from emp
group by deptno
) e
where e.avg_sal < 2000;

3. 스칼라 서브쿼리 (Select 절)

1. 부서별로 직원 수 count해서 출력

-- 3. 스칼라 서브쿼리 (select 절에 있는 subquery)
select d.deptno, d.dname, d.loc,  
(
select count(*)
from emp e
where e.deptno = d.deptno
) '직원수'
from dept d;

• 연산

2. 스칼라 서브쿼리 과정 이해

1. deptno = 10인 직원의 수
1. deptno는 10, 20, 30, 40까지 있으므로 ‘10’의 자리가 변수가 되어야 한다.

select count(*)
from emp
where deptno = 10 

2. select절에는 상수 등 스칼라 값이어야 한다.
1. 프로젝션을 통해 테이블에서 cursor가 한 행씩 방문한다.
2. 한 번 방문한 행에 대한 deptno, dname, 1 이 다시 한 행으로 출력되는 것이므로 ‘1’과 같이 벡터가 아닌 스칼라 값이어야 한다.

select deptno, dname, 1 '직원수'
from dept;

3. 각 부서 별 직원의 수 count
1. 프로젝션으로 cursor가 가리키고 있는 행의 deptno가 10, dname이 accounting, loc이 new york 이다.
2. 이때의 deptno의 값이 emp 테이블에서 deptno 값에 따라 직원의 수를 셀 때 변수가 될 ‘10’이 위치한 자리에 들어가서, emp 테이블의 deptno값과 비교되어야 한다.
3. 따라서 어떠한 테이블의 deptno인지 구분하기 위해 별칭을 사용해야 한다.

전체 코드

-- 서브쿼리 종류 3가지

-- 1. 서브쿼리 (where)
-- 데이터가 중복되도록 만들지 않는다. 변경에 대처하기 힘들다.
-- 연관 관계를 가지게 테이블을 두 개로 만든다.
-- (FK : 다른 테이블을 참조하는 키)
-- (PK : 내 테이블에서 행을 유일하게 결정하는 키)

-- DALLAS 사는 직원 출력해주세요.
select * 
from emp
where deptno = 20;

select deptno
from dept
where loc = 'DALLAS';

select * 
from emp
where deptno = (select deptno
from dept
where loc = 'DALLAS');

-- research 부서의 직원 출력해주세요.
-- emp와 dept가 deptno로 연관관계를 맺고 있다 (-> FK)
select *
from emp
where deptno = (select deptno
from dept
where dname = 'RESEARCH');

-- 2. 인라인 뷰 (from 절에 들어가는 subquery)
select ename, sal*12 '연봉'
from emp
where 연봉 = 9600;

-- 새로 만들어진 테이블(뷰)의 이름(별칭) 만들어줘야 됨.
select e.*
from ( 
	select ename, sal*12 '연봉'
    from emp
) e
where e.연봉 = 9600;

-- having 안써도 됨
select *
from
(
select avg(sal) 'avg_sal'
from emp
group by deptno
) e
where e.avg_sal < 2000;

-- 3. 스칼라 서브쿼리 (select 절에 있는 subquery)
select d.deptno, d.dname, d.loc,  
(
select count(*)
from emp e
where e.deptno = d.deptno
) '직원수'
from dept d;