When selecting data from a table, there might be some NULL values that you don’t want to show, or you want to replace it with 0 for the aggregate functions. Then you can use COALESCE to replace the NULL with 0.

For example, we have the table salaries with 5 columns: emp_no, from_date, to_date, salary, bonus. But the bonus column is optional and may contain NULL values.

emp_no salary from_date to_date bonus
10001 60117 1986-06-26 1987-06-26 2000
10001 62102 1987-06-26 1988-06-25 NULL
10001 66074 1988-06-25 1989-06-25 NULL
10001 66596 1989-06-25 1990-06-25 3000
10001 66961 1990-06-25 1991-06-25 1500
10001 71046 1991-06-25 1992-06-24 NULL
10001 74333 1992-06-24 1993-06-24 NULL
10001 75286 1993-06-24 1994-06-24 2000

Run this SELECT … COALESCE … statement to return 0 as the alternative value when bonus value is NULL:

SELECT
  emp_no,
  salary,
  from_date,
  to_date,
  COALESCE(bonus, 0)
FROM
  salaries;

In MySQL you can also use IFNULL function to return 0 as the alternative for the NULL values:

SELECT
  emp_no,
  salary,
  from_date,
  to_date,
  IFNULL(bonus, 0)
FROM
  salaries;

In MS SQL Server, the equivalent is ISNULL function:

SELECT
  emp_no,
  salary,
  from_date,
  to_date,
  ISNULL(bonus, 0)
FROM
  salaries;

In Oracle, you can use NVL function:

SELECT
  emp_no,
  salary,
  from_date,
  to_date,
  NVL(bonus, 0)
FROM
  salaries;

Same result:

emp_no salary from_date to_date bonus
10001 60117 1986-06-26 1987-06-26 2000
10001 62102 1987-06-26 1988-06-25 0
10001 66074 1988-06-25 1989-06-25 0
10001 66596 1989-06-25 1990-06-25 3000
10001 66961 1990-06-25 1991-06-25 1500
10001 71046 1991-06-25 1992-06-24 0
10001 74333 1992-06-24 1993-06-24 0
10001 75286 1993-06-24 1994-06-24 2000

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

Need a quick edit on the go? Download for iOS

TablePlus in Dark mode