CASE

특정 조건이 충족될 때 특정 값을 선택(select) 하기 위해 사용한다.
CASE문은 쿼리 속 여러 곳에서 사용될 수 있고, 각각의 CASE문은 하나의 결과를 리턴해야 한다.

SELECT CASE WHEN <expression> THEN '<value1>'
ELSE '<value2>' END 
FROM <table>

custom row data 생성하기

-- Example

SELECT
    o.orderid,
    o.customerid,
CASE WHEN o.customerid = 1
THEN 'my first customer'
ELSE 'not my firsts customer'
END,
    o.netamount
FROM ordersas o
ORDER BY o.customerid;

orderid | customerid |          case          | netamount
---------+------------+------------------------+-----------
   10677 |          2 | not my firsts customer |      5.08
    2337 |          3 | not my firsts customer |     39.06
    9077 |          6 | not my firsts customer |    323.30
    6239 |          7 | not my firsts customer |    341.44
    1187 |         11 | not my firsts customer |    285.39
                         ...

WHERE 안에서 필터링하기

-- Example
-- Q. If customerid > 10, select the record if netamount < 100.
-- Else, select the record if netamount > 100.

SELECT
    o.orderid,
    o.customerid,
    o.netamount
FROM orders AS o
WHERE 
    CASE WHEN o.customerid > 10 THEN o.netamount < 100 
    ELSE o.netamount > 100 END 
ORDER BY o.customerid;

orderid | customerid | netamount
---------+------------+-----------
    9077 |          6 |    323.30
    6239 |          7 |    341.44
    9447 |         13 |     83.31
    3075 |         15 |     33.63
    9748 |         19 |     71.29
              ...

Aggregate Function 안에서 사용

-- Example
-- Q. Sum of the netamount that is greater than 100?

SELECT SUM(
        CASE WHEN o.netamount < 100 THEN 0
        ELSE o.netamount END
  ) AS "returns",
SUM(o.netamount) AS "normal total"
FROM orders AS o;

  returns   | normal total
------------+--------------
 2217261.25 |   2371719.74

NULL IF

조건을 충족하면 NULL 을 리턴한다.

-- If value1 equals value2, return NULL
-- If not, return value1

NULLIF(value1, value2)