Exploring DECODE, CASE, and more in SQL
During a recent coding challenge, I ran into a SQL query which contained an unfamiliar keyword: DECODE. Perhaps like some of you, I’d never encountered DECODE before. I decided to take some notes, Googling to my heart’s content. Medium didn’t have much of anything on the topic, so I wanted to turn my findings into an article to share with you all.
First, we’ll talk about DECODE, an Oracle-specific SQL function. After learning how DECODE works, we will take a brief look at analogous operators in other database systems (PostgreSQL and MongoDB).
Oracle DECODE
To begin, let’s define the DECODE function in general terms. SQL’s DECODE function allows you to add procedural if-then-else logic to queries.¹ You can think of the DECODE function as having an “output” or “return value” which can be used in the containing SQL query.
So, let’s generalize the anatomy of the DECODE function:
DECODE(x, c1, r1, c2, r2, ...[, d])Using this skeleton, I think the easiest way to visualize the functionality of DECODE is by writing a code version (here, in JavaScript) that accomplishes the same logic:
Here, we see that the arguments, after the initial x, function in pairs until we reach the final argument:
In each pair, the former argument represents a value we will equality check with the first argument, x (lines 4 and 6 in the above Gist). The latter is the value to be returned by DECODE if equality is established (lines 5 and 7). The final argument for DECODE is the default case (line 10). In DECODE, the final argument is optional. If it is not provided, DECODE will default to null.
Pixar Movies
Let’s put this into practice. We’ll start with a simple (but not-super-practical) use case for DECODE. First, some database schema:
Here is a small list of Pixar movies. Let’s look at a query and translate the logic into JavaScript.
SELECT * FROM movies ORDER BY DECODE(x, 1, id, 2, title, year);We can now choose a value for x. Let’s walk through using x = 2. First, we will treat DECODE’s initial argument (2) like a variable (x) that we will be referencing. Next, take the subsequent pair of arguments: 1 and id. We equality check the former with our “variable”, which looks like this:
x = 2
return x == 1This, of course, returns false. Therefore, we won’t return id as the result of our DECODE. Instead, we move on to the next pair: 2 and title. We perform an equality check in the same way:
x = 2
return x == 2This time, the check returns true. Since we have established equality, we will return title as the result of DECODE. In the previous JavaScript Gist, the output is formatted as ORDER BY title. This represents how the containing query will look once DECODE has been resolved. That’s it! We just ran this SQL query and DECODE function:
SELECT * FROM movies ORDER BY DECODE(2, 1, id, 2, title, year);Here’s the output:
Next, consider this query:
SELECT * FROM movies ORDER BY DECODE(3, 1, id, 2, title, year);Will we fulfill any of the equality checks inherent in DECODE? After taking two pairs of arguments –[1, id] and [2, title]– we see that none of the equality checks will return true. We will then be left with one leftover term: year. We already noted that the final term is an optional default case for DECODE. For this reason, this DECODE’s return value will be year. Had we not passed that final argument following our pairs, DECODE’s return value would be null.
Here’s the output:
Cool! So now we are familiar with DECODE but you might be wondering, “what is a relevant use case for DECODE?”
Star Wars
In a 2017 TechRepublic post, Ant Pruitt gave a more practical example that I will borrow.
The DECODE function offers a handy way to make data easier to analyze and more understandable when you share it with others.
Here’s the schema for their example:
Let’s say we want to organize the customers from our table by geographic region. We can use DECODE to alter our output based on the existing columns. Consider the query:
Here, DECODE determines each row’s value for the newly aliased region column. Our first “variable” argument, state_code, is simply determined by each row’s state_code value. Then, each state_code has a corresponding region that is returned (AS region), along with customer_id and status, in the output table.
DECODEing in other languages
Being a frequent user of PostgreSQL, I was curious if there was a similar function to DECODE in PostgreSQL. After some searching, it became clear that there isn’t a true equivalent. One search result revealed an archived email thread from 2003 that describes Postgres’s closest match: CASE. This isn’t surprising. You might have noticed that the logic we have been describing is very similar to your run-of-the-mill switch statements, found in several programming languages. Here is a link to the PostgreSQL documentation, where CASE and other conditional tools are discussed. Truth be told, it isn’t too complicated.
Another perspective is that DECODE can simply be thought of as a shorthand for CASE. It does seem to be true that anything you can accomplish with DECODE can also be done with CASE (in Oracle). In PostgreSQL, CASE also allows different structurings of the function (1, 2). This is a tiny bit of flexibility that you miss out on with Oracle’s DECODE.
Finally, in MongoDB, we see similar conditional logic in $switch and $cond operators. As its name suggests, $switch behaves more like a switch statement or CASE, while $cond behaves more like a simple if-then-else statement.
If I had to guess, most of us are not using Oracle SQL, but even if you don’t use it, I hope you found this as interesting as I did!
Resources and links:
Citations:
[1]: SQL Tutorial. (21 April 2020)
https://www.sqltutorial.org/sql-comparison-functions/sql-decode/
SQL refresher: How to use the DECODE function to produce user-friendly data. https://www.techrepublic.com/article/sql-refresher-how-to-use-the-decode-function-for-user-friendly-data/
