[SQL] 서브쿼리(Sub Query), 집합연산자(결합쿼리)

▣ 서브쿼리(Sub Query)

▶ 서브쿼리(Sub Query)

• 쿼리안에서 select 쿼리를 사용하는 것.
• 메인 쿼리 -> 서브쿼리
• 서브쿼리는 반드시 ()로 묶어줘야한다

▶ 서브쿼리가 사용되는 구 

• select절, from절, where절, having절   

▶ 서브쿼리의 종류

• 어느 구절에 사용되었는지에 따른 구분
• 스칼라 서브쿼리 : select 절에 사용. 반드시 서브쿼리 결과가 1행 1열(값 하나-스칼라) 0행이 조회되면 null을 반환
• 인라인 뷰 : from 절에 사용되어 테이블의 역할을 한다.
  

 

▶ 서브쿼리 조회결과 행수에 따른 구분

• 단일행 서브쿼리 : 서브쿼리의 조회결과 행이 한행인 것.
• 다중행 서브쿼리 : 서브쿼리의 조회결과 행이 여러행인 것.
  

▶ 동작 방식에 따른 구분

• 비상관(비연관) 서브쿼리 : 서브쿼리에 메인쿼리의 컬럼이 사용되지 않는다. 메인쿼리에 사용할 값을 서브쿼리가 제 공하는 역할을 한다.
• 상관(연관) 서브쿼리 - 서브쿼리에서 메인쿼리의 컬럼을 사용한다. 
•  -> 메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할때 주로 사용한다.

EX)

select *
from emp
where salary > (select salary
        from emp where emp_id=200);

 

▶ pair 연산자

• 서브쿼리 쓸때만 pair연산자 가능

EX)

select *
from emp
where (job_id, dept_id) = (select job_id, dept_id from emp where emp_id = 115);

select d.dept_name, to_char(round(avg(e.salary),2),'$999,999.99')
from  dept d ,emp e
where e.dept_id(+) = d.dept_id
group by d.dept_name
having avg(salary) > (select avg(salary) from emp);

 

▶  다중행 서브쿼리

• 서브쿼리의 조회 결과가 여러행인 경우
• where절 에서의 연산자

1.  in
2.  비교연산자 any : 조회된 값들 중 하나만 참이면 참 (where 컬럼 > any(서브쿼리) )
3.  비교연산자 all : 조회된 값들 모두와 참이면 참 (where 컬럼 > all(서브쿼리) )

EX)

select emp_id, emp_name, job_id, to_char(hire_date, 'yyyy'), to_char(salary,'$999,999')
from emp
where mgr_id = any(select emp_id from emp where emp_name = 'Alexander');

 

 

▶ 상관(연관) 쿼리

• 메인쿼리문의 조회값을 서브쿼리의 조건에서 사용하는 쿼리.
• 메인쿼리를 실행하고 그 결과를 바탕으로 서브쿼리의 조건절을 비교한다.
• 메인쿼리를 실행하면서 서브쿼리를 그때그때 실행시킴

EX)

select *
from emp e
where salary = (select max(salary) from emp where dept_id = e.dept_id)
order by dept_id;

 

▶ EXISTS, NOT EXISTS 연산자 (상관(연관)쿼리와 같이 사용된다)

• 서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건.
• 조건을 만족하는 행이 여러개라도 한행만 있으면 더이상 검색하지 않는다.

EX)

select d.dept_id, d.dept_name, d.loc
from dept d 
where exists(select 1 from emp where dept_id = d.dept_id);

 

 

▣ 집합 연산자(결합 쿼리)

▶ 집합 연산자 (결합 쿼리)

• 둘 이상의 select 결과를 가지고 하는 연산.
• 구문 : select문  집합연산자 select문 [집합연산자 select문 ...] [order by 정렬컬럼 정렬방식]

▶ 연산자

• UNION: 두 select 결과를 하나로 결합한다. 단 중복되는 행은 제거한다. (합집합) 반드시 column수가 같아야함
• UNION ALL : 두 select 결과를 하나로 결합한다. 중복되는 행을 포함한다. (합집합)
• INTERSECT: 두 select 결과의 동일한 결과행만 결합한다. (교집합)
• MINUS: 왼쪽 조회결과에서 오른쪽 조회결과에 없는 행만 결합한다. (차집합)
  

▶ 규칙

• 연산대상 select 문의 컬럼 수가 같아야 한다. 
• 연산대상 select 문의 컬럼의 타입이 같아야 한다.
• 연산 결과의 컬럼이름은 첫번째 왼쪽 select문의 것을 따른다.
• order by 절은 구문의 마지막에 넣을 수 있다.
• UNION ALL을 제외한 나머지 연산은 중복되는 행은 제거한다.

EX)

select * from emp where dept_id in (10,20)
union -- 합집합 중복된거 없이 나옴
select * from emp where dept_id in(10,30);

select * from emp where dept_id in (10,20)
intersect --교집합 : 두 조회결과에 공통으로 있는 것만 나온다.
select * from emp where dept_id in (20,30);