SQL Subquery

---

• 하나의 SQL 문 안에 포함되어 있는 또 다른 SQL 문

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

  • 서브쿼리는 메인쿼리의 칼럼 사용 ⭕
  • 메인쿼리는 서브쿼리의 칼럼 사용 ❌

• 서브쿼리는 괄호() 로 묶어서 사용

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

• Subquery 에서는 ORDER BY 사용 ❌

종류_총 3가지

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

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

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

스카라 서브쿼리(Scalar Subquery)

• SELECT 절에 사용
• 결과는 하나의 Column 이어야 한다

---

인라인 뷰 (Inline View)

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

---

중첩 서브쿼리 (Nested Subquery)

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

---

• Single Row : 하나의 열을 검색하는 서브쿼리
  
  

• Multiple Row : 하나 이상의 열을 검색하는 서브쿼리

  • 일반적
    
    
  • EXISTS 와 함께 : 결과 값의 존재 여부
    
    
  • ANY 와 함께 : 최소한 한개 만족
    
    
  • ALL 와 함께 : 모두 만족
    
    

• Multiple Column : 하나 이상의 행을 검색하는 서브쿼리
  
  

예제

---

1. 스카라 서브쿼리(Scalar Subquery)

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

• 서울은평경찰서 강도 검거 건수

  mysql> SELECT police_station, case_number
      -> from crime_status
      -> where police_station like '은평' and crime_stype like '강도' and status_type like '검거'
      -> ;
  +----------------+-------------+
  | police_station | case_number |
  +----------------+-------------+
  | 은평           |           1 |
  +----------------+-------------+

• 서울시 경찰서 전체 강도 검거 건수

  mysql> SELECT police_station, case_number
      -> from crime_status
      -> where crime_stype like '강도' and status_type like '검거'
      -> ;
  +----------------+-------------+
  | police_station | case_number |
  +----------------+-------------+
  | 중부           |           4 |
  | 종로           |           4 |
  | 남대문         |           2 |
  | 서대문         |           1 |
  | 혜화           |           3 |
  | 용산           |           6 |
  | 성북           |           2 |
  | 동대문         |           5 |
  | 마포           |           3 |
  | 영등포         |           5 |
  | 성동           |           3 |
  | 동작           |           2 |
  | 광진           |           4 |
  | 서부           |           2 |
  | 강북           |           4 |
  | 금천           |           6 |
  | 중랑           |           6 |
  | 강남           |          10 |
  | 관악           |           3 |
  | 강서           |           5 |
  | 강동           |          14 |
  | 종암           |           0 |
  | 구로           |           5 |
  | 서초           |           3 |
  | 양천           |           3 |
  | 송파           |          17 |
  | 노원           |           3 |
  | 방배           |           1 |
  | 은평           |           1 |
  | 도봉           |           2 |
  | 수서           |           1 |
  +----------------+-------------+

• 최종 쿼리

  • 별칭(Alias) 활용 참고

mysql> select case_number,
    -> (select avg(case_number) from crime_status where crime_stype like '강도' and status_type like '검거') avg 💡별칭(Alias)
    -> from crime_status
    -> where police_station like '은평' and crime_stype like '강도' and status_type like '검거'
    -> ;
+-------------+--------+
| case_number | avg    |
+-------------+--------+
|           1 | 4.1935 |
+-------------+--------+

2. 인라인 뷰 (Inline View)

• from 절에서 사용

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

• 별로 : group by

mysql> select police_station, case_number
    -> from crime_status
    -> where status_type like '발생'
    -> group by police_station;
+----------------+-------------+
| police_station | case_number |
+----------------+-------------+
| 중부           |           1 |
| 종로           |           9 |
| 남대문         |           1 |
| 서대문         |           3 |
| 혜화           |           0 |
| 용산           |           5 |
| 성북           |           0 |
| 동대문         |          10 |
| 마포           |           9 |
| 영등포         |           7 |
| 성동           |           5 |
| 동작           |           1 |
| 광진           |           2 |
| 서부           |           3 |
| 강북           |           9 |
| 금천           |           4 |
| 중랑           |          10 |
| 강남           |           5 |
| 관악           |          12 |
| 강서           |          10 |
| 강동           |           3 |
| 종암           |           4 |
| 구로           |           6 |
| 서초           |           4 |
| 양천           |           5 |
| 송파           |           6 |
| 노원           |           4 |
| 방배           |           1 |
| 은평           |           1 |
| 도봉           |           0 |
| 수서           |           1 |
+----------------+-------------+

• 최종 쿼리

		📌c 이름 주고. 어떤 경찰서, 어떤 범죄 유형, 얼마나?
mysql> select c.police_station, c.crime_stype, c.case_number


    -> from crime_status as c,

		📌가장 많이 발생한 (건수)  count로 Alias
	->  (select police_station, max(case_number) as count
    		
    ->      from crime_status
    
    		📌가장 많이 [발생]한
    ->      where status_type like '발생'
    
    		📌경찰서로 묶고, m으로 Alias
    ->      group by police_station) as m
    
    	📌2개를 JOIN, 원래 테이블 컬럼 = 새로만든 테이블
    -> where c.police_station = m.police_station

		📌케이스 넘버
	-> and c.case_number = m.count;
+----------------+-------------+-------------+
| police_station | crime_stype | 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 |
+----------------+-------------+-------------+

3. 중첩 서브쿼리 (Nested Subquery)

---

Single Row Subquery

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

• () 없어, 에러

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

• 한 개 이상의 결과 (에러)

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

• 최종 쿼리

select name
from celeb

📌이름과 같은지 비교
where name = 

📌 행 1개 출력
(select host from snl_show where id = 1);

mysql> select name
    -> from celeb
    -> where name =
    -> (select host from snl_show where id = 1);
+--------+
| name   |
+--------+
| 강동원 |
+--------+

Multiple Row - IN

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

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

mysql> select name from celeb where job_title like '%영화배우%';
+--------+
| name   |
+--------+
| 강동원 |
| 차승원 |
+--------+

select host

from snl_show

📌host를 받는데
where host in 

📌위에서 2개 행의 결과에 있는 사람
(select name from celeb where job_title like '%영화배우%');

mysql> select host
    -> from snl_show
    -> where host in (select name
    ->                     from celeb
    ->                where job_title like '%영화배우%');
+--------+
| host   |
+--------+
| 강동원 |
| 차승원 |
+--------+

Multiple Row - EXISTS

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

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

• 테이블 확인

  mysql> select * from police_station limit 1;
  +----------------+-------------------------------------+
  | name           | adress                              |
  +----------------+-------------------------------------+
  | 서울강남경찰서 | 서울특별시 강남구 테헤란로 114길 11 |
  +----------------+-------------------------------------+
  1 row in set (0.15 sec)
  
  mysql> select * from crime_status limit 1;
  +------+----------------+-------------+-------------+-------------+----------------+
  | year | police_station | crime_stype | status_type | case_number | REFERENCE      |
  +------+----------------+-------------+-------------+-------------+----------------+
  | 2020 | 중부           | 살인        | 발생        |           1 |  서울중부경찰서 |
  +------+----------------+-------------+-------------+-------------+----------------+
  1 row in set (0.14 sec)

• 2000건 보다 큰 경찰서

  mysql> select police_station
      -> from crime_status
      -> where case_number > 2000;
  +----------------+
  | police_station |
  +----------------+
  | 영등포         |
  | 영등포         |
  | 영등포         |
  | 중랑           |
  | 강남           |
  | 관악           |
  | 관악           |
  | 관악           |
  | 강서           |
  | 강서           |
  | 구로           |
  | 송파           |
  | 송파           |
  | 송파           |
  | 노원           |
  +----------------+

• 기존에 만든, reference 컬럼 확인

  mysql> select police_station,
      -> reference
      -> from crime_status
      -> where case_number > 2000;
  +----------------+------------------+
  | police_station | reference 📌     |
  +----------------+------------------+
  | 영등포         | 서울영등포경찰서 |
  | 영등포         | 서울영등포경찰서 |
  | 영등포         | 서울영등포경찰서 |
  | 중랑           | 서울중랑경찰서   |
  | 강남           | 서울강남경찰서   |
  | 관악           | 서울관악경찰서   |
  | 관악           | 서울관악경찰서   |
  | 관악           | 서울관악경찰서   |
  | 강서           | 서울강서경찰서   |
  | 강서           | 서울강서경찰서   |
  | 구로           | 서울구로경찰서   |
  | 송파           | 서울송파경찰서   |
  | 송파           | 서울송파경찰서   |
  | 송파           | 서울송파경찰서   |
  | 노원           | 서울노원경찰서   |
  +----------------+------------------+

• 최종 쿼리

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);

mysql> 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);
+------------------+
| name             |
+------------------+
| 서울강남경찰서   |
| 서울강서경찰서   |
| 서울관악경찰서   |
| 서울구로경찰서   |
| 서울노원경찰서   |
| 서울송파경찰서   |
| 서울영등포경찰서 |
| 서울중랑경찰서   |
+------------------+

Multiple Row - ANY

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

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

select name
from celeb

📌같은게 있다면 📌 host 이름 비교, snl_show에 있는
where name = any (select host from snl_show);

mysql> select name
    -> from celeb
    -> where name = any (select host from snl_show);
+--------+
| name   |
+--------+
| 강동원 |
| 유재석 |
| 차승원 |
| 이수현 |
+--------+

Multiple Row - ALL

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

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

select name
from celeb

📌이름 가져올건데
where name 

  📌모두 만족하면				📌 조건이 1인 사람 
= all (select host from snl_show where id = 1);

mysql> select name
    -> from celeb
    -> where name = all (select host
    ->                   from snl_show
    ->                   where id = 1);
+--------+
| name   |
+--------+
| 강동원 |
+--------+