COALESCE - 現場ログ

コウアレス

(if objects or ideas coalesce, they combine to form one single group ( quoted from longman ))

引数を走査していき、NULL ではない初めの値を返す

引数が全て NULL の場合は NULL を返す

CREATE TABLE staffs(
  id INTEGER
    NOT NULL,
  name CHAR(32)
    NOT NULL,
  basesalary INTEGER
    NOT NULL,
  bonus INTEGER,
  PRIMARY KEY(id)
);

INSERT INTO staffs
(id, name, basesalary, bonus)
VALUES
(1, 'nameA', 20, 30);

INSERT INTO staffs
(id, name, basesalary)
VALUES
(2, 'nameB', 20);

INSERT INTO staffs
(id, name, basesalary, bonus)
VALUES
(3, 'nameC', 30, 40);

SELECT name, (basesalary + bonus) as totalsalary
FROM staffs;

-- at id = 2, NULL returns because bonus set includes NULL

 id |               name               | totalsalary 
----+----------------------------------+-------------
  1 | nameA                            |          50
  2 | nameB                            |            
  3 | nameC                            |          70

SELECT name, (basesalary + COALESCE(bonus, 0)) as totalsalary
FROM staffs;

 id |               name               | totalsalary 
----+----------------------------------+-------------
  1 | nameA                            |          50
  2 | nameB                            |          20
  3 | nameC                            |          70

PostgreSQL: Documentation: 9.4: Conditional Expressions