[SQL] SQL Subquery

Subquery란?

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

 

Subquery 종류

1. Scalar Subquery - SELECT 절에 사용
2. Inline View - FROM 절에 사용
3. Nested Subquery - WHERE 절에 사용

 

1. Scalar Subquery

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

 

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

SELECT case_number,
(SELECT avg(case_number)
FROM crime_status
WHERE crime_type LIKE '강도' AND status_type LIKE '검거') avg
FROM crime_status
WHERE police_station LIKE '은평' AND crime_type LIKE '강도' AND status_type LIKE '검거';

 

2. Inline View

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

 

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

SELECT c.police_station, c.crime_type, c.case_number
FROM crime_status c,
(SELECT police_station, max(case_number) count
FROM crime_status
WHERE status_type LIKE '발생'
GROUP BY police_station) m
WHERE c.police_station = m.police_station
AND c.case_number = m.count
LIMIT 5;

 

3. Nested Subquery

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

 

3.1 Single Row Subquery (단일 행 서브쿼리)

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

SELECT name
FROM celeb
WHERE name = (SELECT host FROM snl_show WHERE id = 1);

 

3.2.1 Multiple Row - IN

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

SNL 에 출연한 영화배우를 조회

SELECT host
FROM snl_show
WHERE host IN (SELECT name FROM celeb WHERE JOB_TITLE LIKE '%영화배우%');

 

3.2.2 Multiple Row - EXISTS

IN과 유사한 기능

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

SELECT column_names
FROM table_name
WHERE EXISTS (SELECT column_name FROM table_name WHERE condition)
ORDER BY column_names;

 

3.2.3 Multiple Row - ANY

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

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

SELECT name
FROM celeb
WHERE name = ANY (SELECT host FROM snl_show);

 

3.2.3 Multiple Row - ALL

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

SELECT name
FROM celeb
WHERE name = ALL (SELECT host FROM snl_show WHERE id = 1);

 

3.2.4 Multi Column Subquery

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

SELECT name, sex, agency
FROM celeb
WHERE (sex, agency) IN (SELECT sex, agency FROM celeb WHERE name = '강동원');

 

4. 연습

(1)  주유 금액의 합 가장 큰 연예인을 상위 3명까지 검색하는 쿼리를 다음의 조건에 맞게 작성하세요.

• 출력되는 칼럼명은 name, age, total_price 으로 구성하세요.
• 주유금액의 합을 내림차순으로 정렬한 다음 age 순으로 정렬하세요.
• total_price 는 천단위 콤마를 적용해서 표시하세요.

select c.name, c.age, format(sum(r.금액), 0) total_price
from celeb c, refueling r
where c.name = r.이름
group by c.name, c.age
order by sum(r.금액) desc, c.age asc;

 

 

(2) 한번에 주유한 양(리터)이 가장 많은 연예인의 이름과 리터를 검색하세요.

• 출력되는 칼럼명은 name, liter 로 구성하세요.
• liter 를 내림차순으로 정렬하세요.
• liter 를 소수점 두번째 자리까지 표시하세요. (소수점 세번째 자리에서 반올림)

select r.이름 name, round(max(r.금액 / c.가격), 2) liter
from refueling r, oil_price c
where c.상호=r.주유소
group by r.이름
order by liter desc;

 

 

(3) 주유소 별로 한번에 가장 많이 주유한 금액을 검색하는 쿼리를 다음의 조건에 맞게 작성하세요.

• 출력되는 칼럼명은 gas_station, date, max_price 으로 구성하세요.
• max_price 를 내림차순으로 정렬한 다음, 주유일 내림차순으로 정렬하세요.
• 주유소별 최대 주유 금액이 같은 데이터가 여러개인 경우, 가장 빠른 주유일 기준으로 데이터를 출력하도록 쿼리를 작성합니다.

# Inline View 사용
select p.주유소 gas_station, p.주유일 date, p.금액 max_price
from (select min(주유일) 주유일, 주유소, 금액 from refueling group by 주유소, 금액) p,
(select 주유소, max(금액) 금액 from refueling group by 주유소) c
where p.주유소 = c.주유소 and p.금액 = c.금액
order by max_price desc, date desc;

 

# Scalar Subquery
select p.주유소 gas_station,
(select min(c.주유일)
from refueling c
where c.주유소 = p.주유소 and c.금액 = max(p.금액)) date,
max(p.금액) max_price
from refueling p
group by gas_station
order by max_price desc, date desc;

# Inline View (Inner Join)
select p.주유소 gas_station, p.주유일 date, p.금액 max_price
from (select min(주유일) 주유일, 주유소, 금액 from refueling group by 주유소, 금액) p
Inner join (select 주유소, max(금액) 금액 from refueling group by 주유소) c
On p.주유소 = c.주유소 and p.금액 = c.금액
order by max_price desc, date desc;