[TIL] SQL/Subquery

📖 이론

📌 Subquery

하나의 SQL 문 안에 포함되어 있는 또 다른 SQL 문
(메인쿼리가 서브쿼리를 포함하는 종속적인 관계)

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

🚫 주의사항

• 서브쿼리는 괄호로 묶어서 사용
• 단일 행 혹은 복수 행 비교 연산자와 함께 사용 가능
• 서브쿼리 에서는 order by 사용불가

📌 subquery 종류

스칼라 서브쿼리 (Scalar Subquery) - SELECT 절에 사용
인라인 뷰 (Inline View) - FROM 절에 사용
중첩 서브쿼리 (Nested Subquery) - WHERE 절에 사용

📌 Nested Subquery

Single Row - 하나의 열을 검색하는 서브쿼리
Multiple Row - 하나 이상의 열을 검색하는 서브쿼리
Multiple Column - 하나 이상의 행을 검색하는 서브쿼리

---

💻 실습

SCALAR SUBQUERY 문법

select column1, (select column2 from table2 where condition)
from table1
where condition

🚫 결과는 하나의 Column 이어야 함

INLINE VIEW 문법

select a.column, b.column
from table1 a, (select column1, column2 from table2) b
where condition

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

NESTED SUBQUERY 문법

- single row
//서브쿼리가 비교연사자와 사용되는 경우

select column_name from tablename
where column_name = (select column_name from tablename where condition)
order by column_name

🚫 서브쿼리의 검색 결과는 한 개의 결과값을 가져야함 (두개 이상인 경우 에러)
🚫 괄호 없으면 에러 발생

-Multiple Row - IN
//서브쿼리 결과 중에 포함되는 경우

select column_name from tablename
where column_name in (select column_name from tablename where condition)
order by column_name
`
-Multiple Row - EXISTS
//서브쿼리 결과에 값이 있으면 반환

select column_name from tablename
where exists (select column_name from tablename where condition)
order by column_name

-Multiple Row - ANY
//서브쿼리 결과 중에 최소한 하나라도 만족하는 경우 (비교연산자 사용)

select column_name from tablename
where column_name = any (select column_name from tablename where condition)
order by column_name

-Multiple Row - ALL
//서브쿼리 결과를 모두 만족하는 경우(비교 연산자 사용)

select column_name from tablename
where column_name = all (select column_name from tablename where condition)
order by column_name

-Multi Column Subquery - 연관 서브쿼리
//서브쿼리 내에 메인쿼리 컬럼이 같이 사용되는 경우

select column_name from tablename a
where (a.column1, a.column2, ...) (select b.column1, b.column2, ... from tablename b where a.column = b.column)
order by column_name

---

🧸 review

• 공공데이터 csv 파일로 연습하기
  5대범죄 발생 검거 현황
5대범죄 발생 장소별 현황
  

• 서울시 살인이 가장 많이 발생한 장소와 살인 발생 건수 조회

select ANY_VALUE(장소) 장소, max(발생건수) 발생건수,
(select sum(건수) from 지역별_5대범죄 where 죄종='살인' and 발생검거='발생') 총발생건수
from 장소별_5대범죄 
where 범죄명='살인'

❗️sql_mode=only_full_group_by 에러
group by 절에 포함되지 않는 column 을 select 할 경우 발생하는 에러

해결방안
ANY_VALUE(column)함수 사용

• 지역별로 가장 많이 발생한 범죄 건수와 범죄 유형을 inline view 를 조회
  (발생건수가 큰 순으로 정렬하여 5개 조회)

select 지역, 죄종 유형, 건수 발생건수 
from 지역별_5대범죄 a,
(select 지역 지역구분, max(건수) 발생건수 from 지역별_5대범죄 where 발생검거='발생' group by 지역구분) b 
where a.지역 = b.지역구분 and a.건수=b.발생건수
order by 건수 desc
limit 5;

• 발생 건수가 가장 큰 범죄 유형과 지역을 nested subquery 를 사용하여 조회

select 지역, 죄종 범죄유형 
from 지역별_5대범죄 
where 건수 in (select max(건수) from 지역별_5대범죄 where 발생검거='발생');

• 발생 건수가 2000 이상인 지역과 범죄유형을 중복없이 nested subquery 를 사용하여 조회

select distinct(지역),죄종 범죄유형
from 지역별_5대범죄 a
where exists 
(select 지역, 죄종 from 지역별_5대범죄 b where a.지역 = b.지역 and 발생검거='발생' and a.죄종=b.죄종 and 건수 >=2000);

실습확인

---