Subquery(서브쿼리)
- Subquery(서브쿼리): 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문을 말한다.
- 메인쿼리가 서브쿼리를 포함하는 종속적인 관계이다.
- 서브쿼리는 메인 쿼리의 칼럼 사용 가능
- 메인쿼리는 서브쿼리의 칼럼 사용 불가
- 서브쿼리 사용 시 주의점
- 서브쿼리는 괄호로 묶어서 사용
- 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능
- Subquery 에서는 Order By 사용 X
- 서브쿼리 종류
- Scalar Subquery(스칼라 서브쿼리): SELECT절에 사용
- Inline View(인라인뷰): FROM절에 사용
- Nested Subquery(중첩 서브쿼리): WHERE절에 사용
Scalar Subquery(스칼라 서브쿼리): SELECT절에 사용
SELECT col1, (SELECT col2 FROM table2 WHERE condition)
FROM table1
WHERE condition
- 예제: 서울은평경찰서의 강도 검거 건수와 서울시 경찰서 전체의 평균강도검거건수를 조회
mysql> select case_number,
-> (select avg(case_number)
-> from crime_status
-> where crime_type='강도' and status_type like '검거') avg
-> from crime_status
-> where police_station like '은평' and crime_type like '강도' and status_type like '검거';
+
| case_number | avg |
+
| 1 | 4.1935 |
+
1 row in set (0.02 sec)
Inline View(인라인뷰): FROM절에 사용
- 메인쿼리에서는 인라인 뷰에서 조회한 Column만 사용 가능
SELECT a.column, b.column
FROM table1 a, (SELECT col1, col2 FROM table2) b
WHERE condition
- 예제: 경찰서별 가장 많이 발생한 범죄 건수와 이때의 범죄 유형을 조회
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
mysql> 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 ;
+
| police_station | crime_type | case_number |
+
| 중부 | 폭력 | 997 |
| 종로 | 폭력 | 964 |
| 남대문 | 절도 | 699 |
| 서대문 | 폭력 | 1292 |
| 혜화 | 폭력 | 747 |
| 용산 | 폭력 | 1617 |
| 성북 | 폭력 | 672 |
| 동대문 | 폭력 | 1784 |
| 마포 | 폭력 | 1844 |
| 영등포 | 폭력 | 2701 |
| 성동 | 폭력 | 1223 |
| 동작 | 폭력 | 1631 |
| 광진 | 폭력 | 1676 |
| 서부 | 폭력 | 748 |
| 강북 | 폭력 | 1817 |
| 금천 | 폭력 | 1471 |
| 중랑 | 폭력 | 2022 |
| 강남 | 폭력 | 2283 |
| 관악 | 폭력 | 2614 |
| 강서 | 폭력 | 2445 |
| 강동 | 폭력 | 1942 |
| 종암 | 폭력 | 758 |
| 구로 | 폭력 | 2204 |
| 서초 | 폭력 | 1750 |
| 양천 | 폭력 | 1582 |
| 송파 | 폭력 | 2675 |
| 노원 | 폭력 | 2163 |
| 방배 | 폭력 | 423 |
| 은평 | 폭력 | 1092 |
| 도봉 | 폭력 | 1234 |
| 수서 | 폭력 | 1394 |
+
31 rows in set (0.02 sec)
Nested Subquery(중첩 서브쿼리): WHERE절에 사용
Single Row: 하나의 행을 검색하는 서브쿼리
- 서브쿼리가 비교연산자(=, >, >=, <, <=, <>, !=)와 사용되는 경우, 서브쿼리의 검색 결과는 한 개의 결과값을 가져야 한다. (두개 이상인 경우 에러)
SELECT col_name
FROM tablename
WHERE col_name = (SELECT col_name
FROM tablename
WHERE condition)
mysql> select name from celeb where name = (select host from snl_show);
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> select host from snl_show;
+
| host |
+
| 강동원 |
| 유재석 |
| 차승원 |
| 이수현 |
| 이병헌 |
| 하지원 |
| 제시 |
| 조정석 |
| 조여정 |
| 옥주현 |
+
10 rows in set (0.02 sec)
mysql> select name from celeb where name = (select host from snl_show where id =1);
+
| name |
+
| 강동원 |
+
1 row in set (0.02 sec)
Multiple Row: 하나 이상의 행을 검색하는 서브쿼리
IN
- 서브쿼리 결과 중에 포함될 때: IN 을 사용한 서브쿼리
SELECT col_name
FROM tablename
WHERE col_name IN (SELECT col_name
FROM tablename
WHERE condition)
mysql> select host
-> from snl_show
-> where host in (select name
-> from celeb
-> where job_title like '%영화배우%');
+
| host |
+
| 강동원 |
| 차승원 |
+
2 rows in set (0.02 sec)
EXISTS
- EXISTS: 서브쿼리 결과에 값이 있으면 반환
// 범죄 검거 혹은 발생 건수가 2000건 보다 큰 경찰서 조회
select name
from police_station p
where exists (select police_station
from crime_status c
where p.name = c.reference and case_number > 2000);
select reference
from crime_status
where case_number > 2000);
+
| name |
+
| 서울강남경찰서 |
| 서울강서경찰서 |
| 서울관악경찰서 |
| 서울구로경찰서 |
| 서울노원경찰서 |
| 서울송파경찰서 |
| 서울영등포경찰서 |
| 서울중랑경찰서 |
+
8 rows in set (0.02 sec)
ANY
- ANY: 서브쿼리 결과 중에 최소한 하나라도 만족하면 (비교연산자 사용)
- IN과 다른 점은, 비교 연산자와 함께 사용이 가능하다는 점
- '=ANY (Subquery)'는 'IN (Subquery)'와 동일함.
select name
from celeb
where name = ANY (select host
from snl_show);
+
| name |
+
| 강동원 |
| 유재석 |
| 차승원 |
| 이수현 |
+
4 rows in set (0.01 sec)
All
- All: 서브쿼리 결과를 모두 만족하면 (비교연산자 사용)
select name
from celeb
where name = ANY (select host
from snl_show
where id = 1);
+
| name |
+
| 강동원 |
+
1 row in set (0.01 sec)
Multiple Column 서브쿼리(연관 서브쿼리): 하나 이상의 열을 검색하는 서브쿼리
- 서브쿼리 내에 메인쿼리 컬럼이 같이 사용되는 경우
select column_names
from tablename a
where (a.col1, a.col2, ...) IN (select b.col1, b.col2, ...
from tablename b
where a.column_name = b.column_name);
mysql> select name, sex, agency
-> from celeb
-> where (sex, agency) in (select sex, agency from celeb where name = '강동원');
+
| name | sex | agency |
+
| 강동원 | M | YG엔터테인먼트 |
| 차승원 | M | YG엔터테인먼트 |
+
2 rows in set (0.02 sec)
실습 예제
mysql> select avg(가격) 셀프주유평균가격, (select max(가격) from oil_price where 상표 like '%SK%') SK최고가격 from oil_price where 셀프='Y';
+
| 셀프주유평균가격 | SK최고가격 |
+
| 1517.6000 | 2160 |
+
1 row in set (0.07 sec)
mysql> select o.상호, o.상표, m.max_price
from oil_price o, (
select 상표, max(가격) max_price
from oil_price group by 상표
) m
where o.상표=m.상표 and o.가격=m.max_price;
+
| 상호 | 상표 | max_price |
+
| (주)MS주유소 | 현대오일뱅크 | 1498 |
| 쌍문주유소 | S-OIL | 1509 |
| 뉴서울(강남) | SK에너지 | 2160 |
| 신길주유소 | GS칼텍스 | 1498 |
+
4 rows in set (0.06 sec)
mysql> select 상호, 가격 from oil_price where 가격 > (select avg(가격) from oil_price);
+
| 상호 | 가격 |
+
| 21세기주유소 | 1598 |
| 살피재주유소 | 1635 |
| 뉴서울(강남) | 2160 |
+
3 rows in set (0.07 sec)
mysql> select 이름, 주유소, 주유일 from refueling where 주유소 in (select 상호 from oil_price where 가격 > (select avg(가격) from oil_price));
+
| 이름 | 주유소 | 주유일 |
+
| 유재석 | 뉴서울(강남) | 2021-10-01 |
| 이미주 | 뉴서울(강남) | 2021-10-01 |
| 유재석 | 뉴서울(강남) | 2021-10-03 |
| 아이유 | 뉴서울(강남) | 2021-10-14 |
| 아이유 | 뉴서울(강남) | 2021-10-14 |
| 유재석 | 뉴서울(강남) | 2021-10-16 |
+
6 rows in set (0.20 sec)
mysql> select o.이름, o.주유소, o.주유일 from refueling o, (select 상호, 가격 from oil_price where 가격 > (select avg(가격) from oil_price)) s where o.주유
소=s.상호;
+
| 이름 | 주유소 | 주유일 |
+
| 유재석 | 뉴서울(강남) | 2021-10-01 |
| 이미주 | 뉴서울(강남) | 2021-10-01 |
| 유재석 | 뉴서울(강남) | 2021-10-03 |
| 아이유 | 뉴서울(강남) | 2021-10-14 |
| 아이유 | 뉴서울(강남) | 2021-10-14 |
| 유재석 | 뉴서울(강남) | 2021-10-16 |
+
6 rows in set (0.06 sec)
mysql> select r.이름, r.주유소, r.금액, o.상표, o.가격 from oil_price o,
-> (select 이름, 주유소, 금액 from refueling where 금액 >= 100000) r
-> where o.상호=r.주유소;
+
| 이름 | 주유소 | 금액 | 상표 | 가격 |
+
| 이상순 | 타이거주유소 | 140000 | SK에너지 | 1484 |
| 유재석 | 뉴서울(강남) | 110000 | SK에너지 | 2160 |
| 아이유 | 뉴서울(강남) | 120000 | SK에너지 | 2160 |
| 아이유 | 뉴서울(강남) | 150000 | SK에너지 | 2160 |
| 이미주 | 뉴서울(강남) | 120000 | SK에너지 | 2160 |
+
5 rows in set (0.07 sec)
mysql> select r.이름, r.주유소, r.금액, o.상표, o.가격 from refueling r, oil_price o where r.금액>100000 and o.상호=r.주유소;
+
| 이름 | 주유소 | 금액 | 상표 | 가격 |
+
| 이상순 | 타이거주유소 | 140000 | SK에너지 | 1484 |
| 유재석 | 뉴서울(강남) | 110000 | SK에너지 | 2160 |
| 아이유 | 뉴서울(강남) | 120000 | SK에너지 | 2160 |
| 아이유 | 뉴서울(강남) | 150000 | SK에너지 | 2160 |
| 이미주 | 뉴서울(강남) | 120000 | SK에너지 | 2160 |
+
5 rows in set (0.04 sec)
