COALESCE returns the first non-NULL value from the input list.

Here are some example on how COALESCE works.

To use COALESCE on a simple list of values:

SELECT 
    COALESCE(NULL, 'Tom', 'Bob', NULL) result;

So the result will be:

result
Tom

To use COALESCE on a data table.

We have table employees:

CREATE TABLE employees (
  emp_no int(11) NOT NULL,
  birth_date date NOT NULL,
  first_name varchar(14) NOT NULL,
  last_name varchar(16) NOT NULL,
  gender enum('M','F') NOT NULL,
  hire_date date NOT NULL,
  email text,
  PRIMARY KEY (emp_no);

So there are some employees without email address and date of birth information. So we use COALESCE to extract those NULL values as not provided:

SELECT
  emp_no,
  COALESCE(birth_date, 'Not Provided') birth_date,
  first_name,
  last_name,
  gender,
  hire_date,
  COALESCE(email, 'Not Provided') email
FROM
  employees;

The output will be like this:

emp_no birth_date first_name last_name gender hire_date email
10001 1953-09-02 Georgi Facello M 1986-06-26 [email protected]
10002 1964-06-02 Bezalel Simmel F 1985-11-21 Not Provided
10003 Not Provided Parto Bamford M 1986-08-28 Not Provided
10004 1954-05-01 Chirstian Koblick M 1986-12-01 [email protected]
10005 1955-01-21 Kyoichi Maliniak M 1989-09-12 Not Provided
10006 1953-04-20 Anneke Preusig F 1989-06-02 [email protected]
10007 Not Provided Tzvetan Zielinski F 1989-02-10 [email protected]
10008 1958-02-19 Saniya Kalloufi M 1994-09-15 Not Provided
10009 1952-04-19 Sumant Peac F 1985-02-18 [email protected]
10010 Not Provided Duangkaew Piveteau F 1989-08-24 Not Provided
10011 Not Provided Mary Sluis F 1990-01-22 [email protected]
10012 1960-10-04 Patricio Bridgland M 1992-12-18 [email protected]
10013 1963-06-07 Eberhardt Terkki M 1985-10-20 Not Provided

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