SQL SUBQUERY

서브쿼리 (sql subquery)

• 하나의 sql문 안에 포함되어 있는 또 다른 sql문을 의미한다.

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

  • 서브쿼리는 메인쿼리의 칼럼을 사용할 수 있다.
  • 메인쿼리는 서브쿼리의 칼럼을 사용할 수 없다.

• 서브쿼리는 괄호로 묶어서 사용한다.

• 단일 행 혹은 복수 행 비교 연산자와 함께 사용이 가능하다.

• 서브쿼리에서는 order by를 사용할 수 없다.

• 서브쿼리의 종류

  • 스칼라 서브쿼리(Scalar Subquery) : SELECT 절에 사용한다.
  • 인라인 뷰(Inline View) : FROM 절에 사용한다.
  • 중첩 서브쿼리(Nested Subquery) : WHERE 절에 사용한다.

---

스칼라 서브쿼리(scalar subquery)

• SELECT 절에서 사용하는 서브쿼리

• scalar는 '한번에 한가지만 처리하는'이라는 뜻을 가지고 있다. 따라서 스칼라 서브쿼리로 부터 나오는 결과는 '하나의 행' 이어야 한다.

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

SELECT case_number,
	(SELECT AVG(case_number)
     FROM crime_status
     WHERE crime_type LIKE '강도' AND status_type LIKE '검거') AS avg
FROM crime_status
WHERE police_station LIKE '은평' AND crime_type LIKE '강도' AND status_type LIKE '검거';

---

인라인 뷰(inline view)

• FROM 절에서 사용하는 서브쿼리

• 서브쿼리를 통해 임시적으로 쓰고 버리는 테이블을 생성하는것

• 메인쿼리에서는 인라인 뷰에서(서브쿼리에서) 조회한 컬럼만 사용가능하다.

• 메인쿼리와 서브쿼리간 충돌이 발생하지 않도록 필요한 각 부분에 별칭(alias)을 지정 해야 함

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

SELECT c.police_station, c.crime_type, c.case_number
FROM crime_status c,
	(SELECT police_station, MAX(case_number) AS count
     FROM crime_status
     WHERE status_type LIKE '발생'
     GROUP BY police_station) AS m
WHERE c.police_station = m.police_station 
AND c.case_number = m.count;

---

중첩 서브쿼리(nested subquery)

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

• 단일 행 서브쿼리 (Single Row) : 하나의 행을 검색하는 서브쿼리

• 다중 행 서브쿼리 (Multiple Row) : 하나 이상의 행을 검색하는 서브쿼리

• 다중 칼럼 서브쿼리 (Multiple Column) : 하나 이상의 열을 검색하는 서브쿼리

단일 행 서브쿼리(single row subquery)

• 서브쿼리가 비교연산자(=, >, >=, <, <=, <>, !=)와 사용되는 경우, 서브쿼리의 검색 결과는 한개 행의 결과값을 가져야 한다.(2개 이상인 경우는 에러)

✔ snl_show테이블에서 id가 1인 호스트를 찾은 뒤 celeb 테이블의 name은?

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

다중 행 서브쿼리(multiple row subquery)

• 서브쿼리의 결과가 2건 이상 반환될 수 있다면 반드시 다중 행 비교 연산자(IN, ALL, ANY, EXISTS등)와 함께 사용해야 한다.

✔ IN 예제 - celeb의 영화배우중 snl_show에 출연한 host를 조회

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

✔ EXISTS 예제(결과값이 있으면 반환) - case_number가 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);

✔ ANY 예제(비교연산자 사용해야함)(하나라도 만족하면) - snl_show에 출연한적이 있는 host를 celeb에서 조회

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

✔ ALL 예제(비교연산자 사용해야함)(모두 만족하면) - snl_show에 출연한 host중 id가 1인 celeb의 name은?

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

다중 칼럼 서브쿼리 (multiple column subquery)

• 서브쿼리 결과로 여러 개의 컬럼이 반환되어 메인쿼리의 조건과 동시에 비교되는 것을 의미한다.

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

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

---

예시

실습 테이블

✔ 정대리의 급여와 테이블 전체 평균 급여를 구하기 - 스칼라 서브쿼리

SELECT name, salary,
	(
    SELECT ROUND(AVG(salary), -1)
    FROM employee
    )
    AS '평균급여'
FROM employee
WHERE name='정대리';

✔ 직급이 사원이고 연봉이 2,200,000 초과인 사람들의 이름과 급여를 구하기 - 인라인 뷰

SELECT A.name, A.salary
FROM
	(
    SELECT *
    FROM employee
    WHERE office_worker = '사원'
    ) A
WHERE A.salary > 2200000;

✔ 정대리라는 사람의 직급을 구하기 - 단일 행 중첩 서브쿼리

SELECT office_worker
FROM employee
WHERE office_worker =
	(
    SELECT office_worker
    FROM employee
    WHERE name='정대리'
    )
;

✔ 정대리보다 급여가 높은 사람들의 이름과 직급 구하기 - 다중 행 중첩 서브쿼리

SELECT name, office_worker
FROM employee
WHERE salary >
	(
    SELECT salary
    FROM employee
    WHERE name = '정대리'
    )
;

✔ 직급이 사원인 사람들을 구하기 - 다중 행 중첩 서브쿼리

SELECT *
FROM employee
WHERE office_worker IN
	(
    SELECT office_worker
    FROM employee
    WHERE office_worker='사원'
    )
;