interplanetary detour

TABLE STRUCTURE

QUERY

EXPLAIN

 UNION

특징

• 여러 개의 테이블을 세로로 합치는 개념이다(추가적인 레코드 출력).
• UNION하는 두 테이블의 컬럼명이 다른 경우에는 첫 번째 테이블의 컬럼명으로 출력된다.
• 중복되는 데이터는 자동으로 DISTINCT해서 출력한다(UNION ALL 사용 시 전부 출력).
• LIMIT을 걸면 UNION을 통해 테이블을 합친 후에 수행한다. 괄호로 SELECT문을 묶는다면 해당 단일 쿼리 안에서 LIMIT을 수행하도록 할 수 있다.

JOIN

특징

• 여러 개의 테이블을 가로로 합치는 개념이다(레코드 당 추가적인 정보).
• 각 테이블에 같은 컬럼이 있을 때만 JOIN을 할 수 있으며, 그 컬럼이 '붙이기 위한 키워드'가 된다.
• JOIN은 INNER JOIN을 기본으로 사용하며, 일치하지 않는 값은 출력하지 않는다(곱집합).
• LEFT OUTER JOIN (LEFT JOIN) = 왼쪽 테이블을 우선시하는 합집합.
• RIGHT OUTER JOIN (RIGHT JOIN) = 오른쪽 테이블을 우선시하는 합집합.
• CROSS JOIN = 두 테이블의 레코드들의 모든 조합 (모든 경우의 수)
• INNER JOIN과 OUTER JOIN은 결합 조건을 ON 구에 적어야 한다. 비교 연산자 뿐만 아니라 논리 연산자 등 다양한 연산자로 조건을 설정할 수 있다.
• 컬럼명 간 등호를 사용하여 결합 조건을 설정할 경우 ON 대신 USING 구를 사용할 수도 있다.

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

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도 사용 가능하다.

IFNULL(인수1, 인수2)

1. 인수1이 NULL이 아니면 인수1 반환, NULL이면 (2)번 과정으로.
2. 인수2가 NULL이 아니면 인수2 반환, NULL이면 NULL 반환.

SELECT
    *
FROM
    product
ORDER BY
    IFNULL(price, 999999) ASC;

price가 NULL일 경우에 값을 999999로 만들어서 맨 마지막에 위치하도록 정렬한다.

NULLIF(인수1, 인수2)

1. 인수1 = 인수2 → NULL 반환
2. 인수1 ≠ 인수2 → 인수1 반환

데이터가 NULL일 경우에 0이 아닌 NULL 그대로 나오게 해야할 경우 사용한다.

ex) 나눗셈의 분모 지정 시

데이터베이스에서 NULL 처리를 할 때 CASE 또는 IF 함수를 사용해도 되지만 더 리소스를 적게 사용할 수 있는 함수가 있다.

바로 COALESCE 함수인데, 인수로 들어가는 값을 차례대로 확인하여 NULL이 아닌 데이터가 나왔을 때 그 값을 반환한다.

아래 나오는 inquiry 테이블과는 다르지만 어느 반에서 학생들이 시험을 봤다고 가정하자. 그런데 시험을 아예 보지 않은 학생이 있다면 데이터는 NULL로 들어가지만 반 평균 점수를 낼 때는 값을 포함시켜야 할 것이다. 이 때 NULL 데이터가 계산식에 들어가면 해당 데이터를 제외하고 계산하거나 오류가 생길 수 있으니 처리해주어야 하는데 그 역할을 해주는 게 COALESCE 함수이다.

SELECT
    AVG(COALESCE(star, 0))
FROM
    inquiry;

위와 같이 COALESCE 함수를 사용해서 쿼리문을 작성하면 NULL 값을 0으로 치환해서 계산하지만

SELECT
    AVG(star)
FROM
    inquiry;

위와 같이 작성한다면 NULL 값을 제외하고 계산하기 때문에 그 결과값이 달라진다.

First Match 기법을 사용하기 때문에 위의 조건부터 체크하며 하나의 조건이 만족하면 해당 처리를 수행 후 그 아래는 확인하지 않고 바로 END로 이동한다. 어떤 것에도 들어맞지 않으면 ELSE의 처리를 수행한다.

조건 CASE

SELECT
    customer, quantity,
    CASE
        WHEN quantity <= 3 THEN 1000
        WHEN quantity <= 7 THEN 1200
        WHEN quantity <= 10 THEN 1500
        ELSE 2000
    END AS delivery_fee
FROM
    delivery;

WHEN 뒤에 어떤 조건을 주고, THEN 뒤에 해당 조건을 만족한다면 실행할 동작을 작성한다.

만약 "WHEN quantity <= 10 THEN 1500"이 맨 위로 갈 경우 전부 맨 위에서 걸릴 것이기 때문에 항상 내려갈수록 조건의 범위가 좁아지도록 설정해주어야 한다(피라미드 형태).

값 CASE

SELECT
    customer,
    CASE delivery_time
        WHEN 1 THEN "오전"
        WHEN 2 THEN "오후"
        WHEN 3 THEN "야간"
        ELSE "지정 없음"
    END AS delivery_time2
FROM
    delivery;

WHEN 뒤에 어떤 값을 주고, CASE 뒤의 Column이 해당 값이라면 THEN 뒤의 값을 END AS 뒤의 별명으로 출력한다.

SELECT
    customer, quantity,
    IF(
        quantity > 5, "Yes", "No"
    ) AS novelty
FROM
    delivery;

IF 함수 안의 인수

• 첫 번째 : 조건
• 두 번째 : TRUE
• 세 번째 : FALSE

IF 함수 중첩

SELECT
    customer, quantity,
    IF(
        quantity > 5, "Yes",
        IF(
            quantity > 3, "Next Time", "No"
        )
    ) AS novelty
FROM
    delivery;

첫 번째 IF 함수의 FALSE 자리에 또 다른 IF 함수를 작성함으로써 중첩시킬 수 있다.

첫 번째 IF 함수가 TRUE일 경우 해당 값으로 진행되고, FALSE일 경우 안 쪽의 IF 함수를 확인한다.

CASE 또는 IF 함수는 주로 SELECT절에서 사용하지만,

WHERE절(조건 분기)과 ORDER BY절(분류 기준 재정의)에서도 사용할 수 있다.

모든 SELECT 쿼리는 위의 순서도를 따른다.

아래의 예시 쿼리문 두 개를 통해 WHERE구와 HAVING구가 각각 어느 시점에서 필터링을 하는지 확인해보았다.

(패드에 글씨 쓰는 게 아직 익숙치 않아 처참하게 널부러진 글씨들은 이해 바람.)

WHERE

HAVING

요약하자면 WHERE는 테이블을 선택한 후 바로 필터링 작업을 하는 반면

HAVING은 그룹핑까지 끝낸 후에 하나의 그룹을 제외하는 방식으로 필터링을 거친다는 것인데

둘 중 어느 방식이 효율적인가는 상황에 따라 다를 것 같다.

한 가지 알 수 있는 사실은 WHERE구로 필터링을 하면 WHERE의 필터링 과정에서 처리할 데이터가 많아지는 것이고,

HAVING구로 필터링을 하면 GROUP BY로 그룹핑하는 과정에서 처리할 데이터의 양이 늘어난다는 것이다.

실무에서는 시뮬레이션 과정을 거쳐 코스트가 더 적은 방법을 찾는다고 한다.