[mysql] SQL Subquery

[SQL Subquery]

• 하나의 SQL 문 안에 포함되어 있는 또다른 SQL 문을 말한다.
• 메인 쿼리가 서브쿼리를 포함하는 종속적인 관계이다.
• 서브 쿼리는 메인 쿼리의 칼럼을 사용 가능하다.
• 메인 쿼리는 서브 쿼리의 칼럼을 사용 불가능하다.
• Subquery 는 괄호를 묶어서 사용해주어야한다.
• 단일 행 혹은 복수 행 비교 연산자와 함께 사용 가능하다.
• Subquery 에서는 order by 를 사용 불가능하다.
• Subquery 종류
  • 스카라 서브쿼리 (Scalar Subquery) : SELECT 절에 사용
  • 인라인 뷰 (Inline View) : FROM 절에 사용
  • 중첩 서브쿼리 (Nested Subquery) : WHERE 절에 사용

---

[Scalar Subquery]

• Select 절에서 사용되고 있기 때문에 하나의 Column을 반환하는 쿼리여야 한다.

 

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

SELECT case_number 은평_경찰서_강도_검거_건수,
    (SELECT avg(case_number)
    FROM crime_status
    WHERE crime_type = '강도' AND status_type='검거') 서울시_전체_강도_검거_건수
FROM crime_status
WHERE police_station = '은평' AND crime_type = '강도' AND status_type='검거';

---

[Inline View]

• FROM 절에는 테이블만 온다. 마찬가지로 FROM 절에 사용된 서브쿼리는 마치 테이블 취급하여 VIEW TABLE 이라고한다.

 

• 경찰서 별로 가장 많이 발생한 범죄 건수와 범죄 유형을 조회

1 단계 ) 경찰서 별, 발생한 범죄 유형과 그에 따른 건수 

SELECT police_station, crime_type, case_number
FROM crime_status
WHERE status_type LIKE '발생'
GROUP BY police_station, crime_type;

2 단계 )  경찰서 별, 발생한 범죄 건수 중 최댓값

SELECT police_station, max(case_number)
FROM crime_status
WHERE status_type = '발생'
GROUP BY police_station;

3 단계 )  경찰서 별로 가장 많이 발생한 범죄 건수와 범죄 유형

SELECT c.police_station as 경찰서, c.crime_type as 범죄_유형, c.case_number as 건수
FROM crime_status c,
    ( SELECT police_station, max(case_number) as max
    FROM crime_status
    WHERE status_type = '발생'
    GROUP BY police_station) as m
WHERE m.max = c.case_number AND m.police_station = c.police_station
GROUP BY 경찰서;

다음처럼,  경찰서 별 발생한 범죄 건수 최댓값 테이블 과

경찰서와 범죄 건수 가 서로 같은 칼럼을 조회 하면 된다.

중요) 다른 테이블과 조인할 때는 반드시 기준이 되는 KEY값 고려해서 해주기!!!! 
위에서는 m.police_station = c.police_station 이 JOIN의 기준이었다.

 

---

[Nested Subquery] 

• WHERE 절에 사용
• .세가지 타입으로 나뉜다.
  
  • SINGLE ROW             : 하나의 열을 검색하는 서브 쿼리
  • MULTIPLE ROW         : 하나 이상의 열을 검색하는 서브 쿼리
  • MULTIPLE COLUMN  : 하나 이상의 행을 검색하는 서브 쿼리

 

1. SINGLE ROW

• 하나의 행
• 서브 쿼리가 비교 연산자( =, >, >=, ><, ... )와 사용되는 경우, 서브 쿼리의 검색 결과는 한 개의 결과 값 (single row) 을 가져야 한다.

 

• celeb 중 snl show 에 참여한 사람 중 id = 1 인 사람을 보고 싶다.
• celelb 중 snl show 에 참여한 사람 중 id = 7 인 사람을 보고 싶다.

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

SELECT name FROM celeb
WHERE name = (SELECT host FROM snl_show WHERE id=7);

 snl_show id 1 인 강동원이 celeb 이기도 해서 조회 되었다.

 snl_show id 7 인 제시가  celeb 이 아니어서 조회 되지 않았다.

 

2. MULTIPLE ROW

• 하나 이상의 행
• 서브 쿼리가 IN 과 함께 사용 되는 경우
  ( 참고로 JOIN을 쓰면 될 것 같은 경우가 있는데, JOIN 이 더 효율이 좋다고 한다. )
• 서브 쿼리가 EXISTS 랑 함께 사용 되는 경우
• 서브 쿼리가 ANY 와 함께 사용 되는 경우 (서브 쿼리 결과 중 하나라도 만족하면 TRUE)
• 서브 쿼리가 ALL 과 함께 사용 되는 경우 (서브 쿼리 결과 모두가 만족해야 TRUE)

 

• SNL에 출연한 영화 배우를 조회하고 싶다. (IN)

SELECT host FROM snl_show
WHERE host IN (SELECT name FROM celeb WHERE job_title LIKE '%영화%');

• 범죄 검거 혹은 발생 건수가 2000건 보다 큰 경찰서 조회 (EXISTS)

SELECT name FROM police_station p
WHERE EXISTS (SELECT police_station FROM crime_status c WHERE p.name = c.reference AND
    case_number > 2000);

• SNL에 출연한 적 있는 연예인 이름 조회 (ANY)

SELECT name FROM celeb
WHERE name = ANY (SELECT host FROM snl_show);

• SNL에 출연한 적 있으면서 id가 1인 연예인 이름 조회 (ALL)

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

 

3. MULTIPLE COLUMN

• 연관 서브쿼리라고도 합니다.
• 칼럼 여러개를 반환하는 서브쿼리
• 하나 이상의 열

 

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

SELECT name, sex, agency FROM celeb
WHERE (sex, agency) IN ( SELECT sex, agency FROM celeb WHERE name='강동원');

 

---

[실습] 

• oil_price 테이블에서 셀프주유의 평균가격과 SK에너지의 가장 비싼 가격을 Scalar Subquery 를 사용하여 조회

SELECT AVG(가격) 셀프_평균가,
    (SELECT MAX(가격)
    FROM oil_price
    WHERE 상표 = 'SK에너지' ) SK에너지_최고가
FROM oil_price
WHERE 셀프 = 'Y';

• oil_price 테이블에서 상표별로 가장 비싼 가격과 상호를 Inline View 를 사용하여 조회하세요.

SELECT o.상표, o.상호, o.가격
FROM oil_price o,
    (SELECT 상표, MAX(가격) as max
    FROM oil_price
    GROUP BY 상표
    ) m
WHERE o.상표 = m.상표 AND o.가격 = m.max;

• 평균가격 보다 높은 주유소 상호와 가격을 Nested Subquery 를 사용하여 조회하세요.

SELECT 상호, 가격
FROM oil_price
WHERE 가격 > ( SELECT AVG(가격) FROM oil_price );

• 3번에서 조회한 주유소에서 주유한 연예인의 이름과 주유소, 주유일을 Nested Subquery 를 사용하여 조회하세요. (refueling 테이블)

SELECT 이름, 주유소, 주유일
FROM refueling
WHERE 주유소 IN ( SELECT 상호 FROM oil_price WHERE 가격 > ( SELECT AVG(가격) FROM oil_price ));

• 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.주유소;