드뎌...! 분기를 할 수 있게 되었다.
프로그래밍을 1도 모르는 나지만 익숙한게 있다면 바로 if - then - else..!
프로그래머스랑 해커랭크에서 조건이 붙을때마다 뭔가 쿼리에 자꾸만 if...then...을 치고 싶었기에 기다렸던 챕터다.

case, 언제 사용?

SELECT, INSERT, UPDATE, DELETE 문에 사용할 수 있다.
나 혼자 쿼리 결과를 본다면 1과 0으로 나타낼 수도 있고, 이렇게 뽑아낸 결과를 원하는 문자로 바꿀 수도 있다. (예를 들면 활성화고객 = 1 = Active / 비활성화고객 = 0 = Inactive 이런식으로...)

case 표현식

두가지 유형이 있다.
❶ Searched case 표현식
❷ Simple case 표현식
결론부터 말하자면 왠만하면 Searched case 표현식을 써라.

구문 사용법은 아래와 같다.

각 기호가 나타내는 걸 살펴보면...

여기서 반환할 표현식은 동일한 타입이어야 한다. (예를 들면 date, number, varchar 등...)

예제 : 활성 고객의 대여횟수를 검색해라. (이때, 서브쿼리를 활용할 것)

• 대여횟수 → rental 테이블에 있다.
• 활성 고객 → customer 테이블에서 Active = 1 인 고객이다.

SELECT
    c.first_name,
    c.last_name,
    CASE
        WHEN active = 0
        THEN 0
        ELSE
            (SELECT COUNT(*)
             FROM rental r
             WHERE r.customer_id = c.customer_id)
    END num_rentals
FROM customer c ;

콤마주의 콤마주의 콤마주의 콤마주의 🤬🤬🤬🤬🤬🤬
CASE 전에 콤마(,)로 구분 안해서 에러 떠서 또 한참 찾았다.

결과를 보면

비활성 고객의 경우 대여횟수가 0으로 표시된다.

SQL문에서 활용할 수 있는 다양한 조건식

결과셋 변환

행 ←→ 열을 바꿔서 보여주고 싶을 때 사용할 수 있다.

예제 : 5월, 6월, 7월의 영화 대여 횟수를 보여주는 쿼리를 작성해라. 이때, 각 열에 각 월이 들어오도록 나타내라.

SELECT
    SUM(CASE
            WHEN monthname(rental_date) = 'May'
            THEN 1
            ELSE 0
        END) May_rentals ,
    SUM(CASE
            WHEN monthname(rental_date) = 'June'
            THEN 1
            ELSE 0
        END) June_rentals ,
    SUM(CASE
            WHEN monthname(rental_date) = 'July'
            THEN 1
            ELSE 0
        END) July_rentals
FROM rental
WHERE rental_date
    BETWEEN '2005-05-01'
    AND '2005-08-01' ;

반드시 단일 행에 결과값이 나와야 하는 상황이 아니라면, 각 월의 영화 대여횟수는 아래 쿼리로도 충분히 뽑아낼 수 있기 때문에 자주 사용할 것 같아보이진 않는다.

SELECT
    monthname(rental_date) rental_month
    , COUNT(*) num_rental
FROM rental
WHERE rental_date
    BETWEEN '2005-05-01'
    AND '2005-08-01'
GROUP BY monthname(rental_date) ;

존재 여부 확인

수량이 궁금한게 아니고, 있는지 없는지를 확인하고 싶을 때도 조건식을 활용할 수 있다.

예제: 이름이 S로 시작하는 배우들이 각각 G 등급, PG 등급, NC-17 등급 영화에 출연했는지 여부를 알아보자.

SELECT
    a.first_name
    , a.last_name
    ,
    CASE
        WHEN EXISTS(SELECT 1
                    FROM film_actor fa
                        INNER JOIN film f
                        ON fa.film_id = f.film_id
                    WHERE fa.actor_id = a.actor_id
                    AND f.rating = 'G')
        THEN 'Y'
        ELSE 'N'
    END  g_actor
    ,
    CASE
        WHEN EXISTS(SELECT 1
                    FROM film_actor fa
                        INNER JOIN film f
                        ON fa.film_id = f.film_id
                    WHERE fa.actor_id = a.actor_id
                    AND f.rating = 'PG')
        THEN 'Y'
        ELSE 'N'
    END pg_actor
    ,
    CASE
        WHEN EXISTS(SELECT 1
                    FROM film_actor fa
                        INNER JOIN film f
                        ON fa.film_id = f.film_id
                    WHERE fa.actor_id = a.actor_id
                    AND f.rating = 'NC-17')
        THEN 'Y'
        ELSE 'N'
    END nc17_actor
FROM actor a
WHERE a.first_name LIKE 'S%' ;

• 각 CASE 표현식은 film_actor와 film 테이블에 대한 상관 서브쿼리를 포함한다.
• 여기서 WHEN절은 EXISTS 연산자를 사용하므로, 배우가 해당 등급의 영화에 1번이라도 출연한 경우는 true로 평가된다.

예제 : Simple case 표현식을 사용해, 각 영화 DVD의 재고 수를 계산한 다음, 0개면 'Out of Stock', 1~2개면 'Scarce', 3~4개면 'Available', 그 이상이면 'Common'으로 나타나도록 해보자.

SELECT
    f.title
    ,
    CASE (SELECT count(*)
          FROM inventory i
          WHERE i.film_id = f.film_id)
        WHEN 0 THEN 'Out of Stock'
        WHEN 1 THEN 'Scarce'
        WHEN 2 THEN 'Scarce'
        WHEN 3 THEN 'Available'
        WHEN 4 THEN 'Available'
        ELSE 'Common'
    END film_availability
FROM film f ;

0으로 나누기 오류 방지

나눗셈이 포함된 계산을 수행할 때는 분모가 0이 되지 않도록 항상 주의해야 한다. 분모가 0이 되면 계산 결과가 null로 반환된다. 분모에 CASE 표현식으로 조건식을 작성하면 계산에서 오류가 발생하거나 null이 되는 일이 생기지 않는다.
뿐만 아니라, 계산할 때 CASE 표현식을 사용하면 null값을 숫자(보통 0 또는 1) 로 변환해서 계산 결과가 null이 나오지 않도록 할 수 있다.

예제 : 각 고객의 평균 지불액을 계산해보자.

• 어떤 고객은 영화를 한편도 대여하지 않았을 수 있다. → 지불금액이 '0'일 것이다.

SELECT
    c.first_name
    , c.last_name
    , SUM(p.amount) total_payment
    , COUNT(p.amount) num_payment
    , SUM(p.amount) / CASE
                         WHEN COUNT(p.amount) = 0
                         THEN 1
                         ELSE COUNT(p.amount)
                      END avg_payment
FROM customer c
    LEFT OUTER JOIN payment p
    ON c.customer_id = p.customer_id
GROUP BY c.first_name, c.last_name ;

조건부 업데이트

테이블의 행을 업데이트할 때 조건식이 필요한 경우가 있다. 이때 UPDATE 문에 CASE 표현식을 사용할 수 있다.

예제 : 지난 90일 동안 영화를 대여하지 않은 고객의 customer.active 열을 0으로 설정하는 작업을 매주 실행해야 한다면..?

UPDATE customer
SET active =
    CASE
        WHEN 90 <= (SELECT datediff(now(), max(rental_date))
                    FROM rental r
                    WHERE r.customer_id = customer.customer_id)
        THEN 0
        ELSE 1
    END
WHERE active = 1 ;

Null 값을 원하는 문자열로 바꾸기

값이 null일 때 'unknown' 이라는 단어로 표시되도록 조건식을 사용해보자.

SELECT
    c.first_name
    , c.last_name
    ,
    CASE
        WHEN a.address IS NULL
        THEN 'Unknown'
        ELSE a.address
    END,
    CASE
        WHEN ct.city IS NULL
        THEN 'Unknown'
        ELSE ct.city
    END,
    CASE
        WHEN cn.country IS NULL
        THEN 'Unknown'
        ELSE cn.country
    END
FROM customer c
    LEFT OUTER JOIN address a ON c.address_id = a.address_id
    LEFT OUTER JOIN city ct ON a.city_id = ct.city_id
    LEFT OUTER JOIN country cn ON ct.country_id = cn.country_id ;

또 다른 조건문

책에 나와있지는 않지만 CASE 표현식 이외에도 다양한 조건문을 쓰는 방식이 있다.

• MySQL 조건문 정리