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.