Subquery
하나의 SQL 문 안에 포함되어 있는 또 다른 SQL 문을 말한다.
하나의 SQL 문으로는 조회가 되지 않는 여러 중복 조건문에 따른 조회를 하기 위해서 사용한다.
메인 쿼리가 서브 쿼리를 포함하는 종속적인 관계로
-
서브 쿼리는 메인 쿼리의 칼럼 사용 가능
-
메인 쿼리는 서브 쿼리의 칼럼 사용 불가
-
Subquery는 괄호'()'로 묶어서 사용
-
단일 행 혹은 복수 행 비교 연산자와 함께 사용 가능
-
Subquery에서는 order by(정렬)을 사용 불가
Subquery의 종류
- Scalar Subquery : select 절에 사용
- Inline View : from 절에 사용
- Nested Subquery : where 절에 사용
실습 tables : crime_status,
mysql> select * from crime_status;
+------+----------------+---------------+-------------+-------------+--------------------------+
| year | police_station | crime_type | status_type | case_number | reference |
+------+----------------+---------------+-------------+-------------+--------------------------+
| 2020 | 중부 | 살인 | 발생 | 1 | 서울중부경찰서 |
| 2020 | 중부 | 살인 | 검거 | 1 | 서울중부경찰서 |
| 2020 | 중부 | 강도 | 발생 | 3 | 서울중부경찰서 |
| 2020 | 중부 | 강도 | 검거 | 4 | 서울중부경찰서 |
| 2020 | 중부 | 강간,추행 | 발생 | 113 | 서울중부경찰서 |
| 2020 | 중부 | 강간,추행 | 검거 | 76 | 서울중부경찰서 |
| 2020 | 중부 | 절도 | 발생 | 943 | 서울중부경찰서 |
| 2020 | 중부 | 절도 | 검거 | 495 | 서울중부경찰서 |
| 2020 | 중부 | 폭력 | 발생 | 997 | 서울중부경찰서 |
| 2020 | 중부 | 폭력 | 검거 | 830 | 서울중부경찰서 |
| 2020 | 종로 | 살인 | 발생 | 9 | 서울종로경찰서 |
310 rows
mysql> select * from police_station;
+--------------------------+----------------------------------------------------------------------------------+
| name | address |
+--------------------------+----------------------------------------------------------------------------------+
| 서울강남경찰서 | 서울특별시 강남구 테헤란로 114길 11 |
| 서울강동경찰서 | 서울특별시 강동구 성내로 33 |
| 서울강북경찰서 | 서울특별시 강북구 오패산로 406 |
| 서울강서경찰서 | 서울특별시 양천구 화곡로 73 |
| 서울관악경찰서 | 서울특별시 관악구 관악로5길 33 |
| 서울광진경찰서 | 서울특별시 광진구 광나루로 447 광진소방서 임시청사 (능동) |
| 서울구로경찰서 | 서울특별시 구로구 가마산로 235 |
| 서울금천경찰서 | 서울특별시 관악구 남부순환로 1435 |
| 서울남대문경찰서 | 서울특별시 중구 한강대로 410 |
| 서울노원경찰서 | 서울특별시 노원구 노원로 283 |
31 rowsScalar Subquery
SELECT 절에서 사용하는 서브쿼리로, 결과는 하나의 Column 이어야 한다.
select 절에서 하나의 컬럼으로 반환받는다.
- 문법
select column1, (select column2 from table_name where condition)
from table_name
where condition;
ex) 서울 은평 결찰서의 강도 검거 건수와 서울시 경찰서 전체의 평균 강도 검거 건수 조회
하나의 SQL문으로 조회를 할 수 없다.
mysql> 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 '검거';
+-------------+--------+
| case_number | avg |
+-------------+--------+
| 1 | 4.1935 |
+-------------+--------+
1 row in set (0.00 sec)Inline View
FROM 절에 사용하는 서브쿼리로, 메인쿼리에서는 인라인 뷰에서 조회한 Column만 사용가능하다.
다시 말해서 from 절에서 실행된 서브 쿼리는 하나의 table처럼 반환되어 사용된다.
- 문법
select a.column, b.column
from tableA a, (select column1, column2 from tableB) b
where condition;
ex) 경찰서별 가장 많이 발생한 범죄 건수와 범죄 유형을 조회
논리 연산자 등을 사용하여 '가장 많이 발생한 범죄'와 '범죄 유형'은 하나의 SQL문으로 조회를 할 수 없다.
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 # 서브 쿼리 끝, 결찰서별 가장 많이 발생한 범죄 건수 table 생성
-> where c.police_station = m.police_station and c.case_number = m.count; self join으로 가장 많이 발생한 범죄 건수에 해당하는 범죄 유형도 같이 조회할 수 있게 된다.
+----------------+------------+-------------+
| 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.00 sec)Nested Subquery
WHERE 절에서 사용하는 서브쿼리로 3가지 타입이 있다.
- Single Row - 하나의 열(결과)을 검색하는 서브쿼리
- Multiple Row - 하나 이상의 열(여러 결과)을 검색하는 서브쿼리
- Multiple Column - 하나 이상의 행(여러 결과)을 검색하는 서브쿼리
두 개의 table로 실습 진행
celeb table
mysql> select * from celeb;
+----+-----------+------------+------+------+-------------------------+--------------------------+
| ID | NAME | BIRTHDAY | AGE | SEX | JOB_TITLE | AGENCY |
+----+-----------+------------+------+------+-------------------------+--------------------------+
| 1 | 아이유 | 1993-05-16 | 29 | F | 가수, 텔런트 | EDAM엔터테이먼트 |
| 2 | 이미주 | 1994-09-23 | 28 | F | 가수 | 울림엔터테이먼트 |
| 3 | 송강 | 1994-04-23 | 28 | M | 텔런트 | 나무엑터스 |
| 4 | 강동원 | 1981-01-18 | 41 | M | 영화배우, 텔런트 | YG엔터테이먼트 |
| 5 | 유재석 | 1972-08-14 | 50 | M | MC, 개그맨 | 안테나 |
| 6 | 차승원 | 1970-06-07 | 48 | M | 영화배우, 모델 | YG엔터테이먼트 |
| 7 | 이수현 | 1999-05-04 | 23 | F | 가수 | YG엔터테이먼트 |
+----+-----------+------------+------+------+-------------------------+--------------------------+
7 rows in set (0.00 sec)snl_show table
mysql> select * from snl_show;
+----+--------+---------+----------------+-----------+
| ID | SEASON | EPISODE | BROADCAST_DATE | HOST |
+----+--------+---------+----------------+-----------+
| 1 | 8 | 7 | 2020-09-05 | 강동원 |
| 2 | 8 | 8 | 2020-09-12 | 유재석 |
| 3 | 8 | 9 | 2020-09-19 | 차승원 |
| 4 | 8 | 10 | 2020-09-26 | 이수현 |
| 5 | 9 | 1 | 2021-09-04 | 이병헌 |
| 6 | 9 | 2 | 2021-09-11 | 하지원 |
| 7 | 9 | 3 | 2021-09-18 | 제시 |
| 8 | 9 | 4 | 2021-09-25 | 조정석 |
| 9 | 9 | 5 | 2021-10-02 | 조여정 |
| 10 | 9 | 6 | 2021-10-09 | 옥주현 |
+----+--------+---------+----------------+-----------+
10 rows in set (0.00 sec)Single Row
서브 쿼리가 비교 연산자(=, !=, <, > ...)와 사용되는 경우, 조회 결과는 '하나의 결과값'을 가진다.
- 문법
select column_name
from table_name
where column_name = (select column_name
from table_name
where condition);
ex) snl_show 출연한 celeb의 이름 조회
에러 : 1개 이상의(4개의 결과) 결과로 인해 에러 발생한다. single row 서브 쿼리는 하나의 결과만 반환한다.
mysql> select name
-> from celeb
-> where name = (select host from snl_show);
ERROR 1242 (21000): Subquery returns more than 1 rowsnl_show 출연한 celeb의 이름 '하나'만 조회
# limit
mysql> select name
-> from celeb
-> where name = (select host from snl_show limit 1);
+-----------+
| name |
+-----------+
| 강동원 |
+-----------+
1 row in set (0.00 sec)
# id
mysql> select name
-> from celeb
-> where name = (select host from snl_show where id=1);
+-----------+
| name |
+-----------+
| 강동원 |
+-----------+
1 row in set (0.00 sec)Multiple Row
비교 연산자가 아닌,
in와 exists, any 혹은 all로 쿼리가 작성될 때에는 결과가 여러 개 조회된다.
order by를 사용할 수 있다.
IN
- 문법
select column_names
from table_name
where column_name in (select column_name
from table_name
where condition)
order by column_names;
ex) snl_show 출연한 영화배우 조회(celeb의 셀럽 중 '영화배우' 직업을 가진 셀럽 이름 목록)
mysql> select host
-> from snl_show
-> where host
-> in (select name from celeb where job_title like '%영화배우%')
-> order by host desc;
+-----------+
| host |
+-----------+
| 차승원 |
| 강동원 |
+-----------+
2 rows in set (0.00 sec)EXISTS
서브 쿼리에서 결과가 있다면 모두 조회
- 문법
select column_names
from table_name
where exists (select column_name
from table_name
where condition)
order by column_names;
ex) 범죄 검거 또는 발생 건수가 2,000건 보다 큰 결찰서 조회
self join 방법으로도 가능하며, 쿼리문을 작성하기 편한 것으로 사용.
# exists 사용 쿼리 : 정렬하여 반환
mysql> select name
-> from police_station p
-> where exists (
-> select police_station
-> from crime_status c
-> where p.name = c.reference and case_number > 2000);
+--------------------------+
| name |
+--------------------------+
| 서울강남경찰서 |
| 서울강서경찰서 |
| 서울관악경찰서 |
| 서울구로경찰서 |
| 서울노원경찰서 |
| 서울송파경찰서 |
| 서울영등포경찰서 |
| 서울중랑경찰서 |
+--------------------------+
8 rows in set (0.00 sec)
# self join 사용 쿼리
mysql> select distinct crime_status.reference
-> from crime_status, police_station
-> where police_station.name = crime_status.reference and case_number > 2000
-> order by crime_status.reference;
+--------------------------+
| reference |
+--------------------------+
| 서울강남경찰서 |
| 서울강서경찰서 |
| 서울관악경찰서 |
| 서울구로경찰서 |
| 서울노원경찰서 |
| 서울송파경찰서 |
| 서울영등포경찰서 |
| 서울중랑경찰서 |
+--------------------------+
8 rows in set (0.00 sec)any
서브쿼리 결과 중에 최소한 하나라도 만족하면 조회(비교연산자 사용)
- 문법
select column_names
from table_name
where column_name = any (select column_name
from table_name
where condition)
order by column_names;
ex) snl_show에 출연한 적이 있는 연예인 이름 조회
self join으로 가능한데, 편한 것을 쓰면 된다.
# any 쿼리 사용
mysql> select name
-> from celeb
-> where name = any (
-> select host from snl_show);
+-----------+
| name |
+-----------+
| 강동원 |
| 유재석 |
| 차승원 |
| 이수현 |
+-----------+
4 rows in set (0.00 sec)
# self join 쿼리 사용
mysql> select name
-> from celeb, snl_show
-> where celeb.name = snl_show.host;
+-----------+
| name |
+-----------+
| 강동원 |
| 유재석 |
| 차승원 |
| 이수현 |
+-----------+
4 rows in set (0.00 sec)all
서브쿼리 결과를 모두 만족하면 조회(비교연산자 사용)
- 문법
select column_names
from table_name
where column_name = all (select column_name
from table_name
where condition)
order by column_names;
ex) ex) snl_show에 출연한 적이 있는 연예인 이름 조회
서브 쿼리의 모든 결과가 name을 만족하지 못하기에 조건 id를 줘서 맞춰준 경우다.
mysql> select name
-> from celeb
-> where name = all (
-> select host from snl_show where id = 1);
+-----------+
| name |
+-----------+
| 강동원 |
+-----------+
1 row in set (0.00 sec)Multiple Column
연관 서브쿼리로,
서브쿼리에서 메인쿼리 컬럼을 같이 사용할 수 있으며, '여러 컬럼'을 조회하는 경우 사용
- 문법
select column_name
from table_name a
where (a.column1, a.column2,...) in (select b.column1, b.columns,...,
from table_name b
where a.column_name = b.column_name
order by column_name;
ex) 강동원과 성별, 소속사가 같은 셀럽의 이름, 성별, 소속사 조회
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.00 sec)