SQL - [Subquery]

솔비·2024년 1월 25일

📚 SQL. 관계형 데이터베이스

목록 보기

13/19

About Subquery

🧷 설명

하나의 SQL 문 안에 포함되어 있는 또 다른 SQL 문을 말한다.

메인쿼리가 서브쿼리를 포함하는 종속적인 관계이다.

서브쿼리는 메인쿼리의 칼럼 사용 가능

메인쿼리는 서브쿼리의 칼럼 사용 불가

🧷 사용시 주의

Subquery 는 괄호로 묶어서 사용

단일 행 혹은 복수 행 비교 연산자와 함께 사용 가능

subquery 에서는 order by 를 사용X

🧷 Subquery 종류

스카라 서브쿼리 (Scalar Subquery) - SELECT 절에 사용

인라인 뷰 (Inline View) - FROM 절에 사용

중첩 서브쿼리 (Nested Subquery) - WHERE 절에 사용

스카라 서브쿼리 (Scalar Subquery) - SELECT 절

🧷 스칼라 서브쿼리

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

mysql> SELECT case_number,
    ->          ( SELECT avg(case_number)
    ->            FROM crime_status
    ->             WHERE crime_type LIKE '강도' AND status_type LIKE '검거' ) as '서울시전체 강도 검거 평균'
    -> FROM crime_status
    -> WHERE police_station LIKE '은평' AND crime_type LIKE '강도' AND status_type LIKE '검거';
+-------------+--------------------------------------+
| case_number | 서울시전체 강도 검거 평균            |
+-------------+--------------------------------------+
|           1 |                               4.1935 |
+-------------+--------------------------------------+
1 row in set (0.15 sec)

인라인 뷰 (Inline View) - FROM 절

🧷 인라인 뷰

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

mysql> SELECT c.police_station, c.crime_type, c.case_number
    -> FROM crime_status c
    ->          ,(SELECT police_station, MAX(case_number) cnt
    ->            FROM crime_status
    ->            WHERE status_type LIKE '발생'
    ->            GROUP BY police_station) m
    -> WHERE c.police_station = m. police_station AND c.case_number = m.cnt;
+----------------+------------+-------------+
| 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.15 sec)

# 참고
mysql> SELECT police_station, MAX(case_number) cnt
    -> FROM crime_status
    -> WHERE status_type LIKE '발생'
    -> GROUP BY police_station;
+----------------+------+
| police_station | cnt  |
+----------------+------+
| 중부           |  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.15 sec)

중첩 서브쿼리 (Nested Subquery) - WHERE 절

🧷 인라인 뷰
• Single Row - 하나의 데이터를 검색하는 서브쿼리
• Multiple Row - 하나 이상의 열을 검색하는 서브쿼리
• Multiple Column - 하나 이상의 행을 검색하는 서브쿼리

1. Single Row - 하나의 데이터를 검색하는 서브쿼리

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

snl_show 테이블에서 id가 1인 셀럽의 이름 추출

mysql> SELECT name FROM celeb
    -> WHERE name = ( SELECT host FROM snl_show WHERE id = 1);
+-----------+
| name      |
+-----------+
| 강동원    |
+-----------+
1 row in set (0.15 sec)

2. Multiple Row - 하나 이상의 데이터를 검색하는 서브쿼리

SNL에 출연한 영화배우 조회

mysql> SELECT host
    -> FROM snl_show
    ->  WHERE host IN ( SELECT name FROM celeb WHERE job_title LIKE '%영화배우%' );
+-----------+
| host      |
+-----------+
| 강동원    |
| 차승원    |
+-----------+
2 rows in set (0.15 sec)

🌟 EXIT

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

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

mysql> Select * from crime_status limit 3;
+------+----------------+------------+-------------+-------------+-----------------------+
| year | police_station | crime_type | status_type | case_number | reference             |
+------+----------------+------------+-------------+-------------+-----------------------+
| 2020 | 중부           | 살인       | 발생        |           1 | 서울중부경찰서        |
| 2020 | 중부           | 살인       | 검거        |           1 | 서울중부경찰서        |
| 2020 | 중부           | 강도       | 발생        |           3 | 서울중부경찰서        |
+------+----------------+------------+-------------+-------------+-----------------------+
3 rows in set (0.15 sec)

mysql> Select * from police_station limit 3;
+-----------------------+--------------------------------------------------+
| name                  | address                                          |
+-----------------------+--------------------------------------------------+
| 서울강남경찰서        | 서울특별시 강남구 테헤란로 114길 11              |
| 서울강동경찰서        | 서울특별시 강동구 성내로 33                      |
| 서울강북경찰서        | 서울특별시 강북구 오패산로 406                   |
+-----------------------+--------------------------------------------------+

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.15 sec)

🌟 ANY

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

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

mysql> SELECT * FROM celeb LIMIT 3;
+----+-----------+------------+------+------+-------------------+--------------------------+
| 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    | 탤런트            | 나무엑터스               |
+----+-----------+------------+------+------+-------------------+--------------------------+
3 rows in set (0.15 sec)

mysql> SELECT  * FROM snl_show LIMIT 3;
+----+--------+---------+----------------+-----------+
| ID | SEASON | EPISODE | BROADCAST_DATE | HOST      |
+----+--------+---------+----------------+-----------+
|  1 |      8 |       7 | 2020-09-05     | 강동원    |
|  2 |      8 |       8 | 2020-09-12     | 유재석    |
|  3 |      8 |       9 | 2020-09-19     | 차승원    |
+----+--------+---------+----------------+-----------+
3 rows in set (0.15 sec)


mysql> SELECT name
    -> FROM celeb
    -> WHERE name = ANY ( SELECT host FROM snl_show ) ;
+-----------+
| name      |
+-----------+
| 강동원    |
| 유재석    |
| 차승원    |
| 이수현    |
+-----------+
4 rows in set (0.15 sec)

🌟 ALL

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

snl_show에서 id가 1인 연예인의 이름

mysql> SELECT name FROM celeb
    -> WHERE name = ALL ( SELECT host FROM snl_show WHERE id = 1);
+-----------+
| name      |
+-----------+
| 강동원    |
+-----------+
1 row in set (0.15 sec)

3. Multiple Column - 하나 이상의 행을 검색하는 서브쿼리

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

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

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.15 sec)

연습문제

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

mysql> SELECT * FROM oil_price LIMIT 3;
+------+----------------------+-------------------------------------------------+--------+--------+--------------------+--------------+
| ID   | 상호                 | 주소                                            | 가격   | 셀프   | 상표               | 구           |
+------+----------------------+-------------------------------------------------+--------+--------+--------------------+--------------+
|    1 | 타이거주유소         | 서울 은평구 수색로 188(중산동)                  |   1484 | N      | SK에너지           | 은평구       |
|    2 | (주)명연에너지       | 서울 은평구 수색로 236(수색동)                  |   1485 | Y      | 현대오일뱅크       | 은평구       |
|    3 | 성락주유소           | 서울 영등포구 가마산로 414(신길동)              |   1498 | Y      | S-OIL              | 영등포구     |
+------+----------------------+-------------------------------------------------+--------+--------+--------------------+--------------+
3 rows in set (0.00 sec)

mysql> SELECT AVG(가격) AS '셀프주유소 평균', (SELECT MAX(가격) FROM oil_price WHERE 상표 LIKE 'SK에너지') AS 'SK MAX 가격'
    -> FROM oil_price
    -> WHERE 셀프 = 'Y';
+------------------------+---------------+
| 셀프주유소 평균        | SK MAX 가격   |
+------------------------+---------------+
|              1517.6000 |          2160 |
+------------------------+---------------+
1 row in set (0.02 sec)

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

mysql> SELECT a.상호, a.상표, b.max_price
    -> FROM oil_price a, ( SELECT 상표, MAX(가격) as max_price FROM oil_price GROUP BY 상표) b
    -> WHERE a.상표 = b.상표 AND a.가격 = b.max_price;
+-------------------+--------------------+-----------+
| 상호              | 상표               | max_price |
+-------------------+--------------------+-----------+
| (주)MS주유소      | 현대오일뱅크       |      1498 |
| 쌍문주유소        | S-OIL              |      1509 |
| 뉴서울(강남)      | SK에너지           |      2160 |
| 신길주유소        | GS칼텍스           |      1498 |
+-------------------+--------------------+-----------+
4 rows in set (0.01 sec)

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

mysql> SELECT 상호, 가격
    -> FROM oil_price
    -> WHERE 가격 > (SELECT AVG(가격) FROM oil_price);
+--------------------+--------+
| 상호               | 가격   |
+--------------------+--------+
| 21세기주유소       |   1598 |
| 살피재주유소       |   1635 |
| 뉴서울(강남)       |   2160 |
+--------------------+--------+
3 rows in set (0.00 sec)

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

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.00 sec)

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

mysql> SELECT r.이름, o.상호, o.상표, r.금액, o.가격
    -> FROM refueling r ,
    -> ( SELECT 상호, 가격, 상표 FROM oil_price ) o
    -> WHERE r.금액 >= 100000 AND r.주유소 = o.상호 ;
+-----------+--------------------+-------------+--------+--------+
| 이름      | 상호               | 상표        | 금액   | 가격   |
+-----------+--------------------+-------------+--------+--------+
| 이상순    | 타이거주유소       | SK에너지    | 140000 |   1484 |
| 유재석    | 뉴서울(강남)       | SK에너지    | 110000 |   2160 |
| 아이유    | 뉴서울(강남)       | SK에너지    | 120000 |   2160 |
| 아이유    | 뉴서울(강남)       | SK에너지    | 150000 |   2160 |
| 이미주    | 뉴서울(강남)       | SK에너지    | 120000 |   2160 |
+-----------+--------------------+-------------+--------+--------+
5 rows in set (0.00 sec)