More Nefarious Nullable Nonsense – Coalesce

OK so it’s not really nefarious but I couldn’t think of another word beginning with “N”.
You may also be relieved to know that this is the dying parp of the vuvuzela on this blog.

This is really just a bit about coalesce – a little used but extremely useful SQL function.

Let’s get the World Cup example out of the way. Here’s a table containing details of the four semi-finalists from this year’s tournament.

Conveniently enough, they consist of two previous winners, one with a best performance as beaten-finalists, and one with a previous best of 4th. Too good an opportunity to miss….

create table final_four (
  team varchar2(30) NOT NULL,
  winners VARCHAR2(30),
  runner_up VARCHAR2(30),
  third VARCHAR2(20),
  fourth VARCHAR2(20),
  best VARCHAR2(30))
/

insert into final_four( team, winners, runner_up, third, fourth, best)
values( 'URUGUAY', '1930, 1950', null, null, '1970', 'WINNER');

insert into final_four( team, winners, runner_up, third, fourth, best)
values( 'GERMANY', '1954, 1974, 1990', '1966, 1982, 1986, 2002', '1970, 2006', '1958', 'WINNER');

insert into final_four( team, winners, runner_up, third, fourth, best)
values( 'HOLLAND', null, '1974,1978', '1998', NULL, 'RU');

insert into final_four( team, winners, runner_up, third, fourth, best)
values( 'SPAIN', null, null, null, '1950', '4TH');

Just a note, this data is all off the top of my head so it may well be either (a) factually wrong or (b) further evidence that I should really get out more. Either way, it allows me to illustrate the use of COALESCE.

If we want to query this table to find the years that each team achieved their best performance, there’s a few options.

First off, especially for those of us who remember the days before Oracle started appending random letters of the alphabet to it’s database versions…

SELECT team, best,
  DECODE( winners, null, 
    DECODE( runner_up, null, 
        DECODE( third, null, fourth, third), 
            runner_up), winners) years
from final_four 
/ 

TEAM		BEST	   YEARS
--------------- ---------- ------------------------------
URUGUAY 	WINNER	   1930, 1950
GERMANY 	WINNER	   1954, 1974, 1990
HOLLAND 	RU	   1974,1978
SPAIN		4TH	   1950

Then there’s the nested NVL version :

SELECT team, best,
    NVL( winners, NVL( runner_up, NVL( third, fourth))) years
FROM final_four
/

TEAM		BEST	   YEARS
--------------- ---------- ------------------------------
URUGUAY 	WINNER	   1930, 1950
GERMANY 	WINNER	   1954, 1974, 1990
HOLLAND 	RU	   1974,1978
SPAIN		4TH	   1950

Salvation from ever multiplying brackets and assorted nested madness is offered by …

SELECT team, best, 
    COALESCE( winners, runner_up, third, fourth) years
FROM final_four
/
TEAM		BEST	   YEARS
--------------- ---------- ------------------------------
URUGUAY 	WINNER	   1930, 1950
GERMANY 	WINNER	   1954, 1974, 1990
HOLLAND 	RU	   1974,1978
SPAIN		4TH	   1950

If you’re looking to return the first non-null value of more than two columns then COALESCE does the job with less typing than anything else.

Incidentally, just in case you’re wondering if the excitement of watching the crowning of only the third new nation to win the World Cup in my lifetime has caused amnesia on my part, here’s what happened when I tried the same query using CASE :

SELECT team, best,
    CASE winners WHEN NULL THEN (
        CASE runner_up WHEN NULL THEN (
            CASE third WHEN NULL THEN fourth ELSE third END)
        ELSE runner_up END)
    ELSE winners END years
FROM final_four
/

TEAM		BEST	   YEARS
--------------- ---------- ------------------------------
URUGUAY 	WINNER	   1930, 1950
GERMANY 	WINNER	   1954, 1974, 1990
HOLLAND 	RU
SPAIN		4TH

Now I admit it’s possible that I may have not got the syntax quite right ( I tried it with and without brackets, the result is the same). However, it does seem – on 10g XE at least – that CASE isn’t really in the mood to – as it were – play ball.

That awful pun seems like a good point at which to finish, so I’ll just say that I’ll try the CASE case on 11g at some point.
In the meantime, feel free to point and laugh if you spot the elementary mistake.

About these ads

4 thoughts on “More Nefarious Nullable Nonsense – Coalesce

  1. Replace the first bit of the CASE with

    CASE NVL(winners,’0′)
    WHEN ‘0’ THEN

    and Holland works fine.
    So it looks like CASE has trouble with WHEN NULL. I’m not claiming that’s a global problem, but in this particular example it definately doesn’t react well to WHEN NULL.

    • Martin,

      you are officially a clever-clogs and should definitely put in for that pay-rise !
      I’ve had another mess around with this stuff and I’ll post a follow-up.

      In the meantime, I’ve made some minor corrections to this post ( like writing proper sentences). This is obviously a classic illustration of why you shouldn’t post anything at the end of a night on the beer. Oh well, if you can’t be a shining example, you can always be a terrible warning.

  2. Hi Mike,

    What about the following:

    SELECT team, best,
        CASE WHEN winners is not null THEN winners
             WHEN runner_up is not null THEN runner_up
             WHEN third is not null THEN third
             WHEN fourth is not null then fourth
             ELSE null
        END years
    FROM final_four
    /
    
    • Martin,

      just tried this to see what error I got ( as I could have sworn I had tried it before) and…it worked. Which kind of makes sense seeing as IS NOT NULL is a comparison condition.
      Thanks – nice one !

      Mike

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s