대부분의 보고서에는 몇 가지 논리가 필요합니다. 즉, 주문에 크거나 작은 라벨을 지정하고, 지불된 주문과 보류 중인 주문을 나란히 계산하거나, 긴급 항목을 먼저 표시합니다. SQL CASE WHEN은 이 모든 것을 쿼리 내에서 직접 처리하며 모든 주요 데이터베이스에서 작동합니다.

우리는 SQLite 3.50에서 모든 쿼리를 실행했으며 표시된 결과는 해당 실행에서 나왔습니다. CASE는 표준 SQL의 일부이므로 이 예에서는 PostgreSQL, MySQL, SQL Server 및 Oracle에서 동일한 방식으로 작동합니다.

CASE WHEN은 무엇인가요?

CASE는 별도의 명령이 아닌 표현식입니다. 조건을 순서대로 평가하고 값을 반환하므로 SELECT, WHERE, GROUP BY, ORDER BY 또는 함수 내부 등 값이 허용되는 모든 위치로 이동할 수 있습니다. 항상 END로 끝납니다.

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE fallback
END

샘플 테이블

CREATE TABLE orders (id INT, customer TEXT, total INT, status TEXT);

INSERT INTO orders VALUES
 (1,'Asha',1200,'paid'), (2,'Ben',300,'pending'),
 (3,'Asha',800,'paid'), (4,'Chitra',2500,'shipped'),
 (5,'Dev',150,'cancelled'), (6,'Ben',NULL,'pending');

주문 6에는 합계가 없습니다. 그 행은 나중에 중요합니다.

검색된 CASE

이는 각각의 WHEN이 자체적인 완전한 조건을 갖기 때문에 보다 유연한 형식입니다.

SELECT id, total,
       CASE
         WHEN total >= 1000 THEN 'large'
         WHEN total >= 500 THEN 'medium'
         ELSE 'small'
       END AS size
FROM orders;

-- 1 | 1200 | large
-- 2 |  300 | small
-- 3 |  800 | medium
-- 4 | 2500 | large
-- 5 |  150 | small
-- 6 | NULL | small

마지막 행을 보세요. 주문 6에는 합계가 없지만 ‘소형’이라는 라벨이 지정되어 있습니다. NULL과의 비교는 모두 사실이 아니므로 ELSE에 해당합니다. NULL 섹션에서 이 문제를 해결하는 방법을 보여줍니다. 항상 AS를 사용하여 결과에 이름을 지정하십시오. 그렇지 않으면 어색한 열 이름이 나올 수 있습니다.

간단한 사례

한 열을 고정 값과 비교할 때 더 짧은 형식이 더 잘 읽힙니다.

SELECT id,
       CASE status
         WHEN 'paid' THEN 'Complete'
         WHEN 'shipped' THEN 'Complete'
         WHEN 'pending' THEN 'Waiting'
       END AS label
FROM orders;

-- 1 | Complete
-- 2 | Waiting
-- 3 | Complete
-- 4 | Complete
-- 5 | NULL
-- 6 | Waiting

주문 문제

CASE는 첫 번째 true 조건에서 중지되며 이는 가장 일반적인 논리 버그입니다. 이전 쿼리의 두 조건을 바꿉니다.

SELECT id, total,
       CASE
         WHEN total >= 500 THEN 'medium'
         WHEN total >= 1000 THEN 'large'
         ELSE 'small'
       END AS size
FROM orders WHERE id IN (1, 4);

-- 1 | 1200 | medium
-- 4 | 2500 | medium

이제 그 어떤 것도 “큰” 일이 될 수 없습니다. 총 2500도 최소 500이므로 첫 번째 분기가 승리하고 두 번째 분기에는 도달하지 않습니다. 오류는 나타나지 않고 잘못된 레이블만 나타납니다. 범위의 경우 가장 제한적인 조건을 먼저 입력하거나 total >= 500 AND total < 1000과 같이 전체 범위를 작성하세요.

ELSE가 없으면 어떻게 되나요?

위의 간단한 CASE 예에서는 주문 5가 취소되었으며 이를 처리한 분기가 없으므로 결과는 NULL입니다. 다음 섹션에서 볼 수 있듯이 때로는 이것이 정확히 원하는 것일 수도 있습니다. 그러나 레이블을 작성할 때 일반적으로 명확한 ELSE 'other'가 더 좋습니다. 보고서에 공백을 두는 것보다 예상치 못한 값을 표시하기 때문입니다.

SUM을 사용한 피벗 스타일 보고서

이는 일상적인 SQL에서 가장 유용한 패턴 중 하나입니다. CASE를 집계로 래핑하면 행이 열로 바뀌며 고객당 한 행이 포함됩니다.

SELECT customer,
       SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS paid,
       SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders
FROM orders
GROUP BY customer
ORDER BY customer;

-- Asha   | 2000 | 0
-- Ben    |    0 | 2
-- Chitra |    0 | 0
-- Dev    |    0 | 0

이제 하나의 쿼리로 별도의 쿼리나 조인이 필요 없이 유료 수익과 보류 중인 주문 수를 모두 제공합니다. 이 패턴은 일부 데이터베이스에서만 제공되는 전용 PIVOT 구문과 달리 거의 모든 데이터베이스에서 작동합니다. GROUP BY가 처음이라면 SQL GROUP BY 및 HAVING을 읽어보세요.

조건부 개수

COUNT은 NULL을 건너뛰므로 ELSE가 없는 CASE는 일치하는 행만 계산합니다.

SELECT COUNT(*) AS all_orders,
       COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_orders,
       COUNT(CASE WHEN total >= 1000 THEN 1 END) AS large_orders
FROM orders;

-- 6 | 2 | 2

주의하세요: COUNT 안에 ELSE 0을 추가하면 0은 NULL이 아니기 때문에 모든 행이 계산됩니다. SUM에는 ELSE 0을 사용하고 COUNT에는 사용하지 마세요. PostgreSQL과 SQLite도 COUNT(*) FILTER (WHERE ...)을 지원하는데, 읽기는 좋지만 모든 곳에서 사용할 수 있는 것은 아닙니다.

CASE별로 그룹화

자신만의 버킷을 생성한 후 요약할 수 있습니다.

SELECT CASE WHEN total >= 1000 THEN 'large' ELSE 'other' END AS bucket,
       COUNT(*)
FROM orders
GROUP BY bucket
ORDER BY bucket;

-- large | 2
-- other | 4

별칭별 그룹화는 SQLite, PostgreSQL 및 MySQL에서 작동합니다. SQL Server에서는 이를 허용하지 않으므로 전체 식을 반복하거나 쿼리를 CTE 또는 하위 쿼리에 먼저 넣어야 합니다.

ORDER BY를 사용한 사용자 정의 정렬

알파벳 순서는 기업이 원하는 경우가 거의 없습니다. CASE를 사용하면 자신만의 순서를 정의할 수 있습니다.

SELECT id, status FROM orders
ORDER BY CASE status
           WHEN 'pending' THEN 1
           WHEN 'paid' THEN 2
           ELSE 3
         END, id;

-- 2 | pending
-- 6 | pending
-- 1 | paid
-- 3 | paid
-- 4 | shipped
-- 5 | cancelled

보류 중인 주문이 먼저 나오므로 작업 대기열에 편리합니다. 두 번째 정렬 열인 id은 각 그룹 내에서 예측 가능한 순서로 행을 유지합니다.

업데이트된 사례

CASE를 사용하면 하나의 문이 다른 행에 다른 변경 사항을 적용할 수 있습니다. 여기서는 소액 보류 주문이 검토 대상으로 표시됩니다.

UPDATE orders
SET status = CASE
               WHEN status = 'pending' AND total < 500 THEN 'review'
               ELSE status
             END;

SELECT id, status FROM orders WHERE customer = 'Ben';

-- 2 | review
-- 6 | pending

ELSE status은 다른 행을 변경하지 않고 유지합니다. 이를 그대로 두면 위험합니다. 일치하지 않는 행은 NULL로 설정되기 때문입니다. WHERE 절이 없으면 이 문은 모든 행에도 적용되므로 큰 테이블에서는 필터를 추가하고 커밋하기 전에 결과를 확인할 수 있도록 트랜잭션에서 변경 사항을 래핑하는 것을 고려하세요. 또한 주문 6은 NULL 합계가 total < 500를 충족하지 않았기 때문에 변경되지 않았습니다.

NULL 트랩

NULL은 CASE에 두 가지 별도의 문제를 발생시킵니다. 첫째, 간단한 형식은 같음을 사용하여 비교하며 NULL 자체는 물론 NULL과도 같지 않습니다.

SELECT CASE NULL WHEN NULL THEN 'matched' ELSE 'not matched' END;

-- not matched

따라서 CASE col WHEN NULL은 일치하지 않습니다. 대신 IS NULL로 검색된 양식을 사용하세요. 둘째, 순서 6에서 본 것처럼 NULL 값은 비교를 통과하여 ELSE에 들어갑니다. 해결 방법은 먼저 확인하는 것입니다.

SELECT id,
       CASE
         WHEN total IS NULL THEN 'unknown'
         WHEN total >= 1000 THEN 'large'
         ELSE 'other'
       END AS size
FROM orders WHERE id IN (1, 2, 6);

-- 1 | large
-- 2 | other
-- 6 | unknown

이제 주문 6에 정직한 라벨이 붙습니다. SQL NULL 값에 대한 가이드에서는 이 동작을 전체적으로 다루고 있습니다.

CASE와 다른 옵션

마지막 옵션은 생각해 볼 가치가 있습니다. CASE가 30개의 분기로 늘어나고 5개의 쿼리에 나타나는 경우 매핑을 테이블로 이동하고 테이블에 조인합니다. 그러면 변경은 5개 위치의 코드 변경이 아니라 단일 행 업데이트입니다.

일반적인 실수

• 잘못된 조건 순서. 넓은 조건은 먼저 좁은 조건을 숨깁니다.
• 잊어버림 END. 가장 자주 발생하는 구문 오류입니다.
• WHEN NULL를 사용합니다. 일치하지 않으므로 WHEN col IS NULL을 사용합니다.
• COUNT 안에 ELSE 0를 추가합니다. 모든 행의 개수를 계산합니다.
• 업데이트에서 ELSE를 생략합니다. NULL로 값을 지울 수 있습니다.
• WHERE의 CASE를 기준으로 필터링합니다. 인덱스 사용을 중지할 수 있습니다. SQL 쿼리 최적화에 대한 가이드를 참조하세요.

마무리

CASE WHEN은 많은 작업을 수행하는 작은 기능입니다. 세 가지 규칙을 기억하십시오. 첫 번째 실제 분기가 승리하고, 일치하지 않는 행은 ELSE 없이 NULL이 되며, NULL에는 자체 검사가 필요합니다. 연습하려면 현재 여러 쿼리로 작성하고 있는 보고서를 SUM(CASE ...)를 사용하여 하나의 쿼리로 생성해 보세요.

자주 묻는 질문

계속 학습하세요