SQL 10일차 chapter17(~11)

Subquery

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

메인쿼리가 서브쿼리를 포함
즉, 서브쿼리는 메인쿼리 칼럼 사용 가능/ 반대는 불가

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

스칼라 서브쿼리 - select 절
인라인 뷰 - from 절
중첩 서브쿼리 - where 절

Scalar Subquery

select 절에서 사용하는 서브쿼리. 결과는 하나의 컬럼이어야한다.

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

select case_number, (select avg(case_number) from crime_status where crime_stype like '강도' and status_type like'검거')avg
    -> from crime_status
    -> where police_station like '은평' and crime_stype like '강도'and status_type like '검거';

Inline View

from절에 사용하는 서브쿼리. 메인쿼리에서는 인라인뷰에서 조회한 컬럼만 사용 가능

ex)
경찰서 별 가장 많이 발생한 범죄건수, 범죄유형 조회

select c.police_station, c.crime_stype, 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
    -> where c.police_station = m.police_station and c.case_number = m.count;

경찰서별 최대범죄 건수 m 이라는 테이블을 따로 만들어서 두개를 join

Nested Subquery(중첩서브쿼리)

where 절에서 사용하는 서브쿼리
Single Row / Multiple Row / Multiple column

Single Row = 서브쿼리가 비교연산자와 사용되는경우,
서브쿼리의 검색결과는 한개의 결과값을 가져야한다

결과값이 한개 이상이면 이렇게 에러 뜸

Multiple Row - IN
snl에 출연한 영화배우 조회

 select host from snl_show
-> where host in (select name from celeb where job_title like '%영화배우%');

Multiple Row - EXISTS
범죄검거 혹은 발생 건수가 2천건 보다 큰 경찰서 조회

select name from police_station p
    -> where exists (select police_station from crime_status c where p.name = c.reference and case_number > 2000);

Multiple Row - ANY
snl에 출연한적 있는 연예인 이름

select name from celeb
    -> where name = any(select host from snl_show);

Multiple Row - ALL
서브쿼리 결과를 모두 만족하면(비교연산자 사용)
select name from celeb
-> where name = all (select host from snl_show where id = 1);
괄호 내 조건을 모두 만족하면, 이름을 출력

Multi Column Subquery
하나 이상의 열을 검색하는 서브쿼리
서브쿼리 내에 메인쿼리 컬럼이 같이 사용되는 경우

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

select name, sex, agency from celeb
    -> where (sex, agency) in (select sex, agency from celeb where name = '강동원');