SQL Subquery

(1) About Subquery

• 하나의 SQL 문 안에 포함되어 있는 또 다른 SQL 문을 말한다.
• 메인쿼리가 서브쿼리를 포함하는 종속적인 관계이다.
• 서브쿼리는 메인쿼리의 칼럼 사용 가능
• 메인쿼리는 서브쿼리의 칼럼 사용 불가

Subquery 사용시 주의

• Subquery 는 괄호로 묶어서 사용
• 단일 행 혹은 복수 행 비교 연산자와 함께 사용 가능
• subquery 에서는 order by를 사용X

Subquery 종류

• Scalar Subquery - SELECT 절에 사용
• Inline View - FROM 절에 사용
• 중첩 서브쿼리 (Nested Subquery) - WHERE 절에 사용

(2) Scalar Subquery

• SELECT 절에서 사용하는 서브쿼리로, 결과는 하나의 Column 이어야 한다.

# 문법
select column1, (select column2 from table2 where condition)
from table1
where condition;

예제

1. 서울 은평경찰서의 강도 검거 건수와 서울시 경찰서 전체의 평균 강도 검거 건수 조회

select case_number,
	(select avg(case_number)
     from crime_status
     where crime_type like '강도' and status_type like '검거') as avg
from crime_status
where police_station like '은평' and crime_type like '강도' and status_type like '검거';

(3) Inline View

• FROM 절에 사용하는 서브쿼리로, 메인쿼리에서는 인라인 뷰에서 조회한 Column만 사용 가능하다.

# 문법
select a.column, b.column
from table1 as a, (select column1, column2 from table2) as b
where condition;

예제

1. 경찰서 별로 가장 많이 발생한 범죄 건수와 범죄 유형 조회
step1. 경찰서별 case_number

select police_station, case_number
from crime_status
where status_type like '발생'
group by polcie_staion;

step2. 전체 쿼리 작성

select c.police_station, c.crime_type, c.case_number
from crime_status as c,
	(select police_station, max(case_number) as count
     from crime_status
     where status_type like '발생'
     group by police_station) as m
where c.police_station = m.police_station
	and c.case_number = m.count;

(4) Nested Subquery

WHERE 절에서 사용하는 서브쿼리
• Single Row - 하나의 열을 검색하는 서브쿼리
• Multiple Row - 하나 이상의 열을 검색하는 서브쿼리
• Multiple Column - 하나 이상의 행을 검색하는 서브쿼리

Single Row Subquery

• 서브쿼리가 비교연산자( =, >, >=, <, <=, <>, !=)와 사용되는 경우, 서브쿼리의 검색 결과는 한 개의 결과값을 가져야 한다. (두개 이상인 경우 에러 발생)

# 문법
select column_names
from table_name
where column_name = (select column_name
					 from table_name
                     where condition)
order by column_name;

예제

1. 괄호 없이 (에러)

select name
from celeb
where name = select host from snl_show;

2. 한 개 이상의 결과 (에러)

select name
from celeb
where name = (select host from snl_show);

3.

select name
from celeb
where name = (select host from snl_show where id = 1);

Multiple Row - IN

• 서브쿼리 결과 중에 포함 될때

# 문법
select column_names
from table_name
where column_name in (select column_name
					  from table_name
                      where condition)
order by column_names;

예제

1. SNL에 출연한 영화배우 조회

select host
from snl_show
where host in (select name
			   from celeb
               where job_title like '%영화배우%');

Multiple Row - EXISTS

• 서브쿼리 결과에 값이 있으면 반환

# 문법
select column_names
from table_name
where exists (select column_name
			  from table_name
              where condition)
order by column_names;

예제

1. 범죄 검거 혹은 발생 건수가 2000건 보다 큰 경찰서 조회

select name
from police_station as p
where exists (select police_station
			  from crime_status as c
              where p.name = c.reference and case_number > 2000);

Multiple Row - ANY

• 서브쿼리 결과 중에 최소한 하나라도 만족하면 (비교연산자 사용)

# 문법
select column_names
from table_name
where column_name = any (select column_name
			  			 from table_name
              			 where condition)
order by column_names;

예제

1. SNL에 출연한 적이 있는 연예인 이름 조회

select name
from celeb
where name = any (select host from snl_show);

Multiple Row - ALL

• 서브쿼리 결과를 모두 만족하면 (비교 연산자 사용)

# 문법
select column_names
from table_name
where column_name = all (select column_name
			  			 from table_name
              			 where condition)
order by column_names;

예제

1. SNL에 출연한 적이 있고 id = 1인 연예인 이름 조회

select name
from celeb
where name = all (select host
				  from snl_show
                  where id = 1);

Multi Column Subquery - 연관 서브쿼리

• 서브쿼리 내에 메인쿼리 컬럼이 같이 사용되는 경우

# 문법
select column_names
from tablename as a
where (a.column1, a.column2, ...) in (select b.column1, b.column2, ...
									  from tablename as b
                                      where a.column_name = b.column_name)
order by column_names;

예제

1. 강동원과 성별, 소속사가 같은 연예인의 이름, 성별, 소속사 조회

select name, sex, agency
from celeb
where (sex, agency) in (select sex, agency
						from celeb
                        where name = '강동원');

문제풀이

1. oil_price 테이블에서 셀프주유의 평균가격과 SK에너지의 가장 비싼 가격을 Scalar Subquery를 사용하여 조회

select max(가격), (select avg(가격)
				  from oil_price
                  where 셀프 = 'Y')
from oil_price
where 상표 = 'SK에너지';

2. oil_price 테이블에서 상표별로 가장 비싼 가격과 상호를 Inline View를 사용하여 조회하세요.

select o.상호, o.상표, s.max_price
from oil_price as o, (select 상표, max(가격) as max_price
					  from oil_price
                      group by 상표) as s
where o.상표 = s.상표 and o.가격 = s.max_price;

3. 평균가격 보다 높은 주유소 상호와 가격을 Nested Subquery를 사용하여 조회하세요.

select 상호, 가격
from oil_price
where 가격 > (select avg(가격)
			 from oil_price);

4. 3번에서 조회한 주유소에서 주유한 연예인의 이름과 주유소, 주유일을 Nested Subquery를 사용하여 조회하세요. (refueling 테이블)

select 이름, 주유소, 주유일
from refueling
where 주유소 in (select 상호
				from oil_price
                where 가격 > (select avg(가격)
                			 from oil_price));

5. refueling 테이블과 oil_price 테이블에서 10만원 이상 주유한 연예인 이름, 상호, 상표, 주유 금액, 가격을 Inline View를 사용하여 조회하세요.

select r.이름, o.상호, o.상표, r.금액, o.가격
from oil_price as o, (select 이름, 주유소, 금액
					  from refueling
                      where 금액 >= 100000) as r
where o.상호 = r.주유소;