Subquery
- subquery 란 ?
- 하나의 SQL 문 안에, 다른 SQL문이 포함되는 것을 말한다. 서브쿼리는 메인쿼리의 칼럼을 사용 가능하나, 메인쿼리는 서브커리의 컬럼을 사용할 수 없다.
- subquery 에서는 order by 를 쓸수없으며, 단일 행 혹은 복수 행 비교연산자와 함께 쓸 수 있다. subquery는 괄호로 묶어서 쓴다.
1. Scalar Subquery (select 절)
- 은평구에서, 강도가 검거된 경우의 수를 구하고 서울전체에서 강도가 검거된 경우의 수 평균을 구하는 경우의 코드는 다음과 같다.
select case_number,
-> (select avg(case_number)
-> from crime_status
-> where crime_type like '강도' and status_type like '검거') avg
-> from crime_status
-> where police_station like '은평' and crime_type like '강도' and status_type like '검거';- select 절 안에, 서브쿼리문이 하나 있다. 서브쿼리에서는 메인쿼리의 컬럼들을 사용할 수 있으므로 위와 같이 작성이 가능하다. select 절에서, 은평구 강도검거 건수를 하나 조회하고, select 절 안의 서브쿼리에서 서울시 전체의 강도검거 건수평균을 조회한다.
- 서브쿼리문을 통해서, 쿼리문의 결과로 서울시 전체 강도검거 건수의 평균을 구했고, avg라는 변수명으로 조회하였다.
2. Inline View (from 절)
- 경찰서 별로 가장 많이 발생한 범죄 건수와 범죄 유형을 조회하는 코드는 아래와 같다.
select c.police_station, c.crime_type, 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 테이블이 된다.
- from 절에서, 인라인뷰를 사용하여 경찰서별로 조회할 수 있는 테이블을 생성한다. 경찰서와 각 경찰서에서 발생한 건수들의 가장 큰 값의 컬럼만 가져오는 테이블을 m으로 생성한다.
- where 절에서, 본래 경찰서명과 만든 테이블의 경찰서명이 같은경우와 발생건수가 만든 테이블의 (max건수) 가 같은 경우의 (경찰서명, 범죄타입, 발생횟수) 컬럼만 조회하게 된다.
3. Nested Subquery (where 절)
- 서브쿼리가 비교연산자와(<,>,=,<=,>=,!=) 사용되는 경우, 서브쿼리의 검색 결과는 하나의 결과값만 가져야한다. 두개 이상일 경우 에러발생함
1) Single Row
- 하나의 열을 검색하는 서브쿼리
- 사용해볼 celeb 과 snl의 테이블 구조이다.
- 서브쿼리에 괄호가 없으면 에러가 난다. 또, 비교연산자를 사용하는데 결과값이 여러개이면 에러가 난다.
- 결과값이 하나 일수 있도록, 서브쿼리 내에서 where 문을 이용하여 id=1로 지정해주었다.
- 즉, 위의 쿼리문은 snl에 나온 사람중 id가 1인 사람이 celeb 테이블에 나온 사람이라면 이름을 출력하게 된다.
2) Multiple Row
- 하나의 이상의 열을 검색하는 서브쿼리, 서브쿼리 결과중에 있으면 출력한다.
- 서브쿼리 celeb 테이블에서 배우의 직업을 가진 이름인 사람이 snl에 host로 온적이 있다면 출력한다.
- 위의 예제 single row 에서, where 서브쿼리에서 name=의 결과값이 여러개여서 에러가 났었다. 이 에러를 수정할수 있는 표현으로 = 대신 in 으로 바꿔서 이렇게 쓸 수 있다.
3) Multiple column
- 하나의 이상의 행을 검색하는 서브쿼리
화이팅!