MySQL COALESCE() function

When I first became a DBA, I had a hard time understanding what the COALESCE function did and why you would ever use it.

If you read the explanation from documentations, it seems pretty straightforward:

"MySQL COALESCE() function returns the first non-NULL value of a list, or NULL if there are no non-NULL values." (lifted from here: http://www.w3resource.com/mysql/comparision-functions-and-operators/coalesce-function.php)

It wasn't too long time until I was writing a query for a database I had created myself where I needed the COALESCE function. I created a table that would pull data from a beta environment and a prod environment. I knew that when I was pulling beta data the prod values would always be NULL and when pulling the prod values, the beta values would always be NULL. So I needed to COALESCE the columns I was pulling in.


-- Example data:

CREATE DATABASE IF NOT EXISTS `beta`;

DROP TABLE IF EXISTS `beta`.`shards`;
CREATE TABLE `beta`.`shards` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shard_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`),
  KEY `idx_shard_name` (`shard_name`)
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `beta`.`shards` ( `shard_name`) VALUES ('db2');


CREATE DATABASE IF NOT EXISTS `prod`;

DROP TABLE IF EXISTS `prod`.`shards`;
CREATE TABLE `prod`.`shards` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shard_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`),
  KEY `idx_shard_name` (`shard_name`)
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `prod`.`shards` ( `shard_name`) VALUES ('db1');

CREATE DATABASE IF NOT EXISTS `analysis`;

DROP TABLE IF EXISTS `analysis`.`data_pull`;
CREATE TABLE `analysis`.`data_pull` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `external_id` varchar(70) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `schema_name` varchar(70) COLLATE utf8_unicode_ci NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS `analysis`.`schema_sizes`;
CREATE TABLE `analysis`.`schema_sizes` (
  `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
  `schema_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'database name',
  `total_size_mb` decimal(12,3) unsigned NOT NULL COMMENT 'total size of data and index size calculated using is schema. Calculated independently of the the data_size column and index size column',
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `analysis`.`schema_sizes` (`id`, `schema_name`, `total_size_mb`, `date`) VALUES
(NULL, 'db1', '100','2016-07-22'),
(NULL, 'db2', '200','2016-07-22');

-- Example Query:

SELECT
COALESCE(c.id,c2.id) as external_id,
ss.schema_name,
ss.date
FROM schema_sizes ss
LEFT JOIN prod. shards c ON (ss.schema_name = c.shard_name)
LEFT JOIN beta. shards c2 ON (ss.schema_name = c2.shard_name)
GROUP BY ss.schema_name, ss.date;

This example is kinda simple but it the end results shows how the COALESCE pulls the id values from the beta and prod shard table so that I don't end up with NULL values in the external_id column.