coalesce - database - null processing - nulls - sql coalesce

SQL Coalesce — Weeding Through The NULLs

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:

foodCONCAT(‘$’, 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.

Start Learning

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)
);