[데이터베이스] MySQL - 서브쿼리

🌳  서브쿼리(subQuery)

SQL문 내부에서사용하는 SELECT문

 

- 서브쿼리가 먼저 실행된 후 메인 쿼리가 실행된다.

- 한 문장안에 한 개의 메인 쿼리와 한 개 이상의 서브쿼리가 있는 구조이다.

 

작은 데이터를 필터링하기 위해 주로 사용된다. (조인 : 큰 테이블을 처리할 때 효과적이다.)

 

🌱 반환 값에 따른 서브쿼리

1. 단일 행 서브쿼리(Single-Row SubQuery) : 단일 행을 반환하는 서브쿼리
   - 특정 값을 비교하거나 계산할 때 유용
   >,>=,<,<=,=
   
   
2. 다중 행 서브쿼리(Multi-Row SubQuery) : 여러 행을 반환하는 서브쿼리
   - 여러 조건을 충족하는 데이터를 필터링할 때 사용
   IN : 서브쿼리 각 결과마다 = 연산자 사용하여 비교
   ALL(최댓값/OR) , ANY(최솟값/AND) : 서브쿼리 각 결과에 대해  비교 연산자 사용하여 비교 후 만족하는 정보 반환
   EXITS : 서브쿼리의 결과에 대해 존재 여부를 확인할 때 사용

// IN
select count(*) as 주문건수
from 고객
where 고객번호 in (select 고객번호 from 고객 where 도시 = '부산광역시');

// ANY
select 담당자명, 고객회사명, 마일리지 
from 고객 
where 마일리지 > any(select 마일리지 from 고객 where 도시 = '부산광역시');

// ALL
select 담당자명, 고객회사명, 마일리지 
from 고객
where 마일리지 > all (select avg(마일리지) from 고객 group by 지역);

// EXISTS
select 고객번호, 고객회사명 
from 고객
where exists (select * from 주문 where 고객번호 = 고객.고객번호);

 

🌱 사용 위치에 따른 서브쿼리

having절에 사용하는 서브쿼리

- 고객 전체의 평균마일리지보다 평균마일리지가 큰 도시에 대해 도시명과 도시의 평균마일리지 구하기

select 도시, avg(마일리지) as 평균마일리지
from 고객
group by 도시
having avg(마일리지) > (select avg(마일리지) from 고객);

 

from절에서 사용하는 서브쿼리 = 인라인 뷰

(*인라인 뷰 : SQL 문이 실행될 때만 임시적으로 생성되는 동적인 뷰, 때문에 데이터베이스에 해당 정보가 저장 X)

- 담당자명, 고객회사명, 마일리지, 도시, 해당 도시의 평균마일리지를 보이고, 고객이 위치하는 도시의 평균마일리지와 각 고객의 마일리지 간의 차이도 함께 보이기

select 고객.담당자명, 고객.고객회사명, 고객.마일리지, 고객.도시, 도시_평균마일리지, 도시_평균마일리지 - 마일리지 as 차이
from 고객, (select 도시, avg(마일리지) as 도시_평균마일리지 from 고객 group by 도시 ) as 도시별요약
where 고객.도시 = 도시별요약.도시;

 

select절에서 사용하는 서브쿼리 = 스칼라 서브쿼리

select 사원번호, 이름, 상사번호, (select 이름 from 사원 as 상사 where 상사.사원번호 = 사원.상번호) as 상사이름
from 사원;

---

CTE(Common Table Expressions)  : 쿼리를 통해 만들어낸 임시적인 데이터 세트

* 복잡한 쿼리를 간결하고 가독성 있게 작성하게 도와준다.

WITH  테이블명 AS (테이블 쿼리문)

with 도시별요약 as 
( 
select 도시, avg(마일리지) as 도시_평균마일리지 
from 고객 
group by 도시
)

select 고객.담당자명, 고객.고객회사명, 고객.마일리지, 고객.도시, 도시_평균마일리지, 도시_평균마일리지 - 마일리지 as 차이
from 고객, 도시별요약
where 고객.도시 = 도시별요약.도시;

---

🌱 상관 서브쿼리와 다중 컬럼 서브쿼리

상관 서브쿼리

- 메인쿼리와 서브쿼리 간의 상관관계를 포함하는 형태의 쿼리

 

다중 컬럼 서브쿼리 - 여러 컬럼의 값을 비교하거나 필터링 할 때 사용

* 서브쿼리의 결과의 구조와 메인쿼리의 컬럼 구조가 일치해야함

ex)  where 도시, 마일리지 - select 도시, max(마일리지) 식으로 컬럼 순서를 똑같이 맞춰줘야한다.

select 도시, 담당자명, 고객회사명, 마일리지 
from 고객
where (도시, 마일리지) in (select 도시, max(마일리지) from 고객 group by 도시);