sql

[mysql] SQL Subquery

scone 2022. 7. 7. 03:48

[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.주유소;
저작자표시 비영리 (새창열림)