- SQL Subquery

- About Subquery

- 하나의 SQL문(메인쿼리)안에 들어있는 또 다른 SQL문(서브쿼리)을 말한다.

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

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

- Subquery 사용시 주의

- Subquery는 괄호를 묶어서 사용

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

- Subquery에서는 order by를 사용X

- Subquery 종류

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

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

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

- 스칼라 서브쿼리(Scalar Subquery)

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

SELECT column1, (SELECT column2 FROM table2 WHERE condition)
FROM table1
WHERE condition;

• 예) 서울은평경찰서의 강도 검거건수와 서울시 경찰서 전체의 평균 강도 검거건수를 조회(두개의 쿼리 결과를 하나로 가져올 수 있음)
  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 '검거';

- 인라인 뷰(Inline View)

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

SELECT a.column, b.column
FROM table1 a, (SELECT column1, column2 FROM table2) b
WHERE condition;
(from절에는 테이블이 옴. from절에 온 쿼리를 하나의 테이블로 인지 = a테이블과 b테이블을 조인해서 사용)

• 예) 경찰서별로 가장 많이 발생한 범죄 건수와 범죄 유형을 조회
  select c.police_station, c.crime_type, c.case_number
  -> from crime_status c, (select police_station, max(case_number) count fro
  m crime_status where status_type like '발생' group by police_station) m
  -> where c.police_station = m.police_station and c.case_number = m.count;

- 중첩 서브쿼리(Nested Subquery)

= WHERE 절에서 사용하는 서브쿼리

• single Row : 하나의 열을 검색하는 서브쿼리
• multiple Row : 하나 이상의 열을 검색하는 서브쿼리
• multuple column : 하나 이상의 행을 검색하는 서브쿼리

- Single Row Subquery

서브쿼리가 비교연산자( = , > , >= , < , <= , <> , != ) 와 사용되는 경우, 서브쿼리의 검색 결과는 한 개의 결과값을 가져야 한다.(두개 이상인 경우 에러)
SELECT column_names
FROM table_name
WHERE column_name = (SELECT column_name FROM table_name WHERE condition)
ORDER BY column_name;

• 예)
  SELECT name FROM celeb WHERE name = (SELECT host FROM snl_show WHERE id=1);

- Multiple Row - IN

서브쿼리 결과 중에 포함될 때
SELECT column_names
FROM table_name
WHERE column_name IN (SELECT column_name FROM table_name WHERE condition)
ORDER BY column_name;

• 예) snl에 출연한 영화배우를 조회(행이 두개 이상)
  SELECT host FROM snl_show WHERE host = (SELECT name FROM celeb WHERE JOB_TITLE LIKE '%영화배우%');
  = JOIN을 사용할 수도 있음

- Multiple Row - EXISTS

서브쿼리 결과에 값이 있으면 반환
SELECT column_names
FROM table_name
WHERE EXISTS IN (SELECT column_name FROM table_name WHERE condition)
ORDER BY column_names;

• 예) 범죄 검거 혹은 발생 건수가 2000건보다 큰 경찰서 조회
  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

서브쿼리 결과중에 최소한 하나라도 만족하면(비교연산자 사용)
SELECT column_names
FROM table_name
WHERE column_name = ANY (SELECT column_name
FROM table_name
WHERE condition)
ORDER BY column_names;

• 예) SNL에 출연한적이 있는 연예인 조회
  SELECT name
  FROM celeb
  WHERE name = ANY (SELECT host
  FROM snl_show);
  

- Multiple Row - ALL

서브쿼리 결과를 모두 만족하면(비교연산자 사용)
SELECT column_names
FROM table_name
WHERE column_name = ALL (SELECT column_name
FROM table_name
WHERE condition)
ORDER BY column_names;

• 예)
  SELECT name
  FROM celeb
  WHERE name = ALL (SELECT host
  FROM snl_show
  WHERE id=1);
  = 이름이 결과를 모두 만족하는 결과로 강동원이 조회됨.
  

- Multi Column Subquery - 연관 서브쿼리

서브쿼리 내에 메인쿼리 컬럼이 같이 사용되는 경우
SELECT column_names
FROM table_name a
WHERE (a.column1, a.column2, ...) IN (SELECT b.column1, b.column2, ...)
FROM table_name b
WHERE a.column_name = b.column_name)
ORDER BY column_names;

• 예) 강동원과 성별, 소속사가 같은 연예인의 이름, 성별, 소속사를 조회
  SELECT name, sex, agency
  FROM celeb
  WHERE (sex, agency) IN (SELECT sex, agency FROM celeb WHERE name = '강동원');

- 문제

1. oil_price 테이블에서 셀프주유의 평균가격과 sk에너지의 가장 비싼 가격을 scalar subquery를 사용해서 조회
   = SELECT max(가격), (SELECT avg(가격) from oil_price where 셀프 ='Y') from oil_price where 상표='SK에너지';

2. oil_price 테이블에서 상표별로 가장 비싼 가격과 상호를 inline view를 사용해 조회
   = select o.상호, o.상표, s.max_price from oil_price o, (select 상표, max(가격) max_price from oil_price group by 상표) s
   -> where o.상표 = s. 상표 and o.가격 = s.max_price;

3. 평균가격보다 높은 주유소 상호와 가격을 nested subquery를 사용해 조회
   = select 상호, 가격 from oil_price where 가격 > (select avg(가격) from oi
   l_price);

4. 3번에서 조회한 주유소에서 주유한 연예인의 이름과 주유소이름, 주유일을 nested subquery를 사용해 조회(refueling 테이블)
   = select 이름, 주유소, 주유일
   -> from refueling
   -> where 주유소 in (select 상호 from oil_price where 가격 > (select avg(가격) from oil_price));

5. refueling 테이블과 oil_Price 테이블에서 10만원 이상 주유한 연예인 이름, 상호, 상표, 주유금액, 가격을 inline view를 사용해 조회
   = select r.이름, o.상호, o.상표, r.금액, o.가격
   -> from oil_price o,
   -> (select 이름, 주유소, 금액 from refueling where 금액 >= 100000) r
   -> where o.상호 = r.주유소;