Relational databases store information in tables — with columns that are analogous to elements in a data structure and rows that are one instance of that data structure. In SQL, a table cell without a value contains a special marker, NULL, which is not the same as zero, an empty string, or any other kind of value that works with equality tests; i.e. NULL != 0
, NULL != "
“, etc. The SQL Coalesce statement is one way of processing these NULL values for common uses like text processing.
The format of the Coalesce statement is
COALESCE( value_1, value_2, ..., value_n )
The Coalesce statement returns the first non-NULL value, as evaluated from left to right.
In a simple example,
SELECT COALESCE( NULL, NULL, "@jack", 3.14159, 'fooble@example.com' ) ;
The string value “@jack” is the first non-NULL encountered and so is returned.
SQL Coalesce has great utility in ensuring that output contains contextually appropriate content (rather than a NULL) and for in-place substitution.
SQL Coalesce — Nicknames Processing
One of the challenges to using SQL Coalesce is that its use cases are more complicated than the basic SQL statements, so let’s cover some ways it comes in handy. Bulk text processing allows a program to make many (hopefully appropriate) changes at one time. Imagine the marketing faux pas with sending out emails or letters that start with “Dear NULL” — not good! SQL Coalesce can process nicknames, for example, to side-step such circumstances?
INSERT INTO nicks ( nickname, firstname, lastname )
VALUES
( "Saffy", "Saifedean", "Fortesque" ),
( NULL, "Andreas", "Avalanche" ),
( "Cheesesteak", "Phil", "Smith" ) ;
SELECT COALESCE( nickname, firstname ) user_name
FROM nicks ;
The SELECT statement above returns a nickname if it exists and the person’s first name if it doesn’t. The system addresses users who specified a nickname with their chosen name, and everyone else by their first name.
user_name |
Saffy |
Andreas |
Cheesesteak |
The above code specifies that user_name should hold the result of COALESCE(). In this way business logic processing below can work with what COALESCE() has returned..Because we don’t do any further work with it, we omit it, like this:
SELECT COALESCE( nickname, firstname )
SQL Coalesce — Dealing With Multiple Pay Schedules
Imagine you’re running a restaurant, and today you’re calculating the monthly food costs. Some suppliers charge us weekly; others monthly. A table to support such calculations might look like the following. The CHECK
clause, a sanity-checking step, ensures that we’ve recorded either a weekly or monthly value.
CREATE TABLE food_costs (
food VARCHAR(33),
weekly_cost DECIMAL(6,2),
monthly_cost DECIMAL(6,2),
CHECK(
weekly_cost IS NOT NULL OR
monthly_cost IS NOT NULL)
);
When declaring DECIMAL
data types in SQL, you should also specify the precision and scale. Now we fill the table with our repeating ingredient needs:
INSERT INTO
food_costs( food, weekly_cost, monthly_cost )
VALUES
( "sparkling water", 100, NULL ),
( "Parmesano Reggiano", NULL, 5000 ) ;
When we calculate our monthly expenditure we can either pull over the monthly cost or convert the weekly cost into monthly. (Remember four weeks does not equal a month.)
SELECT
food,
CONCAT('$', FORMAT(COALESCE(
weekly_cost*52/12,
monthly_cost
), 2))
FROM
food_costs;
The results can be handed over to our restaurant’s finance people for processing:
food | CONCAT(‘$’, FORMAT(COALESCE( weekly_cost*52/12, monthly_cost ), 2)) |
sparkling water | $433.33 |
Parmesano Reggiano | $5000.00 |
SQL Coalesce — Making Things Generally More Pretty
In addition to on-the-fly substitution (like the nicknames use case), SQL Coalesce is useful for in-place substitution. Consider full names that may include nicknames; to concatenate them into a complete name string the following pattern is appropriate:
SELECT CONCAT(firstname, ' ', COALESCE(nickname,''), ' ', lastname)
FROM nicks ;
CONCAT(firstname, ‘ ‘, COALESCE(nickname,”), ‘ ‘, lastname) |
Saifedean Saffy Fortesque |
Andreas Avalanche |
Phil Cheesesteak Smith |
The same technique is used to substitute the string “N/A” in a report text field (or web page) when the user doesn’t have a phone number on file.
COALESCE( phone, 'N/A' ) phone_number
SQL Coalesce — Syntactic Sugar for SQL Case
In computer science, an expression that is equivalent to another is referred to as “syntactic sugar” because it’s sweeter to use. SQL Coalesce is syntactic sugar for the SQL Case statement. SQL Coalesce can expand to handle other numbers of parameters, just as the following example does for three parameters:
COALESCE( expression_1, expression_2, expression_3 )
The example above is equivalent to the following SQL Case statement. (In fact, the computer program that reads and “understands” the SQL may convert all types of COALESCE() into Case statements behind the scenes, although COALESCE() is much easier for human computer programmers to write, remember, and maintain.)
CASE
WHEN expression_1 IS NOT NULL THEN expression_1
WHEN expression_2 IS NOT NULL THEN expression_2
ELSE expression_3
END
Conclusion
We’ve examined several use cases where several mutually-exclusive values exist (like the nicknames and restaurant food cost intervals) and instances where expected data may not exist (like a phone number) in which SQL Coalesce can gracefully provide a necessary value for future processing..
Those use cases and techniques, examined above, piggy-backed upon proper table design (providing columns for mutually exclusive values) and judicious use of CHECK()
. SQL Coalesce — along with best-practice design — provides us the tools to generate meaningful output without computing faux pas.
To learn more about SQL, enroll in our SQL Nanodegree program, described in detail here.
SQL Coalesce Sample Table Code
The following will build the code sample tables referred to in this blog post. Examine them interactively at SQL Fiddle.
CREATE TABLE nicks (
nicknameVARCHAR(33),
firstnameVARCHAR(33),
lastname VARCHAR(33)
) ;
CREATE TABLE food_costs (
food VARCHAR(33),
weekly_cost DECIMAL(6,2),
monthly_cost DECIMAL(6,2)
CHECK(
weekly_cost IS NOT NULL OR
monthly_cost IS NOT NULL)
);