Panduan SQL COALESCE

COALESCE(value1, value2, ...)

SELECT COALESCE(NULL, 'second', 'third');
-- Output: second

SELECT COALESCE(NULL, NULL);
-- Output: NULL

-- Input: users
-- id | name         | nickname
-- ---+--------------+---------
-- 1  | Maria Garcia | Mia
-- 2  | Tom Becker   | NULL
-- 3  | Priya Shah   | P

SELECT id, name, COALESCE(nickname, name) AS display_name
FROM users;

-- Output:
-- id | name         | display_name
-- ---+--------------+-------------
-- 1  | Maria Garcia | Mia
-- 2  | Tom Becker   | Tom Becker
-- 3  | Priya Shah   | P

-- Input: products
-- name         | stock
-- -------------+------
-- Desk Lamp    | 12
-- Office Chair | NULL

SELECT name, COALESCE(stock, 'N/A') AS stock FROM products;
-- PostgreSQL: ERROR: invalid input syntax for type integer: "N/A"
-- SQL Server: Conversion failed when converting the varchar value 'N/A' to data type int.

-- (in MySQL, cast with CAST(stock AS CHAR))
SELECT name, COALESCE(CAST(stock AS VARCHAR(10)), 'N/A') AS stock FROM products;

-- Output:
-- name         | stock
-- -------------+------
-- Desk Lamp    | 12
-- Office Chair | N/A

SELECT COALESCE('', 'No name');
-- Output: '' (an empty string)

SELECT COALESCE(NULLIF('', ''), 'No name');
-- Output: No name

SELECT IFNULL(NULL, 'fallback');
-- Output: fallback

-- SQL Server
DECLARE @code VARCHAR(2) = NULL;
SELECT ISNULL(@code, 'none') AS with_isnull, COALESCE(@code, 'none') AS with_coalesce;

-- Output:
-- with_isnull | with_coalesce
-- ------------+--------------
-- no          | none

-- Input: products
-- product_id | name          | sale_price | regular_price
-- -----------+---------------+------------+--------------
-- 1          | Desk Lamp     | 19.99      | 24.99
-- 2          | Office Chair  | NULL       | 149.00
-- 3          | Standing Desk | NULL       | NULL

SELECT product_id, name, COALESCE(sale_price, regular_price, 0.00) AS price
FROM products;

-- Output:
-- product_id | name          | price
-- -----------+---------------+-------
-- 1          | Desk Lamp     | 19.99
-- 2          | Office Chair  | 149.00
-- 3          | Standing Desk | 0.00

-- Input: contacts
-- name | primary_phone | mobile_phone | work_phone
-- -----+---------------+--------------+-----------
-- Ada  | 555-0101      | 555-0199     | NULL
-- Bo   | NULL          | 555-0142     | 555-0177
-- Cy   | NULL          | NULL         | 555-0123
-- Di   | NULL          | NULL         | NULL

SELECT name, COALESCE(primary_phone, mobile_phone, work_phone, 'No phone') AS phone
FROM contacts;

-- Output:
-- name | phone
-- -----+---------
-- Ada  | 555-0101
-- Bo   | 555-0142
-- Cy   | 555-0123
-- Di   | No phone

-- Input: employees
-- employee_id | first_name | last_name | email
-- ------------+------------+-----------+-----------------
-- 1           | Grace      | Hopper    | ghopper@navy.mil
-- 2           | Alan       | Turing    | NULL

SELECT
  employee_id,
  first_name || ' ' || last_name AS full_name,
  COALESCE(
    email,
    LOWER(first_name) || '.' || LOWER(last_name) || '@company.com'
  ) AS email
FROM employees;

-- Output:
-- employee_id | full_name    | email
-- ------------+--------------+------------------------
-- 1           | Grace Hopper | ghopper@navy.mil
-- 2           | Alan Turing  | alan.turing@company.com

-- Input: order_items
-- order_id | product  | quantity | unit_price | discount
-- ---------+----------+----------+------------+---------
-- 101      | Keyboard | 1        | 49.00      | 5.00
-- 101      | Mouse    | 2        | 19.50      | NULL
-- 102      | Monitor  | 1        | 189.00     | NULL
-- 102      | Cable    | 3        | 6.00       | 2.00

-- Wrong: lines with a NULL discount drop out of the total
SELECT order_id, SUM(quantity * unit_price - discount) AS total
FROM order_items
GROUP BY order_id
ORDER BY order_id;

-- Output:
-- order_id | total
-- ---------+------
-- 101      | 44.00
-- 102      | 16.00

-- Right: a missing discount counts as 0
SELECT order_id, SUM(quantity * unit_price - COALESCE(discount, 0)) AS total
FROM order_items
GROUP BY order_id
ORDER BY order_id;

-- Output:
-- order_id | total
-- ---------+-------
-- 101      | 83.00
-- 102      | 205.00