[RDBMS] 서브 쿼리

서브 쿼리란 쿼리 안의 쿼리를 말한다. 서브 쿼리를 감싸고 있는 원래의 쿼리는 메인 쿼리라고 부른다.

 

SELECT [컬럼명] FROM [테이블명]

    WHERE [컬럼명] [연산자] (SELECT ~);

 

서브 쿼리는 위의 형식과 같이 괄호로 감싸며, 서브 쿼리에는 세미콜론을 사용하지 않는다.

 

아래의 예제들을 살펴보자.

---

WHERE절에서의 사용

SELECT -- (3)
    order_id, price
FROM -- (1)
    productorder
WHERE -- (2)
    price >= (
        SELECT -- (2-2)
            AVG(price)
        FROM -- (2-1)
            productorder
    );

 

WHERE절에서 서브 쿼리를 날리는 쿼리문이다. 서브 쿼리로 가져온 결과를 메인 쿼리의 조건으로 사용하고 있다.

 

---

SELECT절에서의 사용

SELECT -- (2)
    order_id, price,
    (
        SELECT -- (2-2)
            COUNT(*)
        FROM -- (2-1)
            productorder
    ) AS order_count
FROM -- (1)
    productorder
ORDER BY -- (3)
    price
LIMIT -- (4)
    3;

 

SELECT절에서 서브 쿼리를 사용했다. 원래라면 집약 함수와 일반 컬럼을 동시에 가져올 수 없기 때문에

SELECT
    order_id, price, COUNT(*)
~

이런 식의 쿼리를 작성하면 에러가 발생해 결과값을 도출할 수 없지만 서브 쿼리를 사용함으로써 가능케 하는 것이다.

 

---

HAVING절에서의 사용

SELECT
    customer_id, AVG(price)
FROM
    productorder
GROUP BY
    customer_id
HAVING
    AVG(price) < (
        SELECT
            AVG(price)
        FROM
            productorder
    );

 

WHERE절에서 사용한 것과 비슷하게 HAVING절에서도 서브 쿼리로 가져온 값으로 조건을 걸어줄 수 있다.

---

서로 다른 테이블

SELECT
    customer_id, customer_name
FROM
    customer
WHERE
    membertype_id = (
        SELECT
            membertype_id
        FROM
            membertype
        WHERE
            membertype = "할인 회원"
    );

 

메인 쿼리의 customer 테이블과는 다른 membertype 테이블에서 membertype 컬럼이 할인 회원인 멤버의 아이디만 가져와서 조건을 걸 수도 있다.

---

복수행 서브 쿼리

SELECT
    order_id, product_id
FROM
    productorder
WHERE
    product_id IN (
        SELECT
            product_id
        FROM
            product
        WHERE
            price >= 150
    );

 

 

위와 같이 서브 쿼리의 결과가 단일행이 아닌 복수행으로 반환되는 경우 일반적인 연산자(<, >, = 등...)는 사용할 수 없다. 그 대신 IN, ANY, ALL 등의 연산자를 통해 비교할 수 있다.

 

IN : 리스트에 이 값이 하나라도 있으면 1 반환.

ANY : 조건이 일치하는 값이 리스트에 하나라도 있으면 1 반환.

ALL : 리스트에 있는 값이 전부 조건에 일치하면 1 반환.

---

ANY, ALL 연산자

SELECT
    *
FROM
    product
WHERE
    stock < ANY (
        SELECT
            SUM(quantity)
        FROM
            productorder
        GROUP BY
            product_id
    );

 

위와 같은 방식으로 사용할 수 있다.

 

---

서브 쿼리에서의 LIMIT 오류 해결법

SELECT
    customer_id, customer_name
FROM
    customer
WHERE
    customer_id IN (
        SELECT
            customer_id
        FROM (
            SELECT
                customer_id
            FROM
                productorder
            ORDER BY
                price DESC
            LIMIT 3
        ) AS tmp
    );

 

서브 쿼리를 IN, ANY, ALL 등의 연산자를 통해 처리할 때 해당 서브 쿼리 내부에서 LIMIT 예약어 사용 시 MySQL 버전에 따라 오류가 발생할 수 있다. 이 때 서브 쿼리를 이중으로 중첩시켜서 제일 내부에 있는 서브 쿼리에서 LIMIT을 걸어주면 문제없이 LIMIT을 사용할 수 있다.

---

상관 서브 쿼리

SELECT
    product_id,
    product_name
FROM
    product
WHERE
    3 < (
        SELECT
            SUM(quantity)
        FROM
            productorder
        WHERE
            product.product_id = productorder.product_id
    );

 

상관 서브 쿼리의 실행 구조

상관 서브 쿼리란 기존의 메인 쿼리 이전에 서브 쿼리를 완전히 끝내고 그 결과값을 통해 메인 쿼리를 하는 방식과는 달리 메인 쿼리를 수행하는 중에 서브 쿼리도 수행하는 것이다. 서브 쿼리의 결과를 매번 비교해서 메인 쿼리에게 전달한다.

---

외래 키(Foreign Key)

어떤 테이블의 PRIMARY KEY 값이 다른 테이블의 컬럼 중에 포함될 경우 이것을 외래 키라고 한다.

예를 들어 customer 테이블과 order 테이블이 있을 때 customer 테이블에 있는 customer_id는 PK, order 테이블에 있는 customer_id는 FK이다.

 

customer 테이블에 존재하지 않는 값은 order 테이블에서도 외래 키로 사용할 수 없다.

ex) customer_id = 100 은 존재하지 않기 때문에 order 테이블에도 넣을 수 없음.

---

EXISTS

SELECT
    product_id, product_name
FROM
    product AS p
WHERE
    EXISTS (
        SELECT
            *
        FROM
            productorder AS po
        WHERE
            p.product_id = po.product_id
    );

 

EXISTS 뒤에 오는 서브 쿼리의 결과값이 존재하면 1을 반환(출력), 하나의 레코드도 리턴되지 않는다면 0을 반환한다.

존재하지 않으면 출력하는 NOT EXISTS도 사용 가능하다.