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 | +----------------+-------------+ -
최종 쿼리
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 |
+--------+
| 강동원 |
+--------+
비전공자의 데이터 공부법