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:

JavaScript example of DECODE logic

Here, we see that the arguments, after the initial x, function in pairs until we reach the final argument:

bracketed pairs of arguments
Pairs grouped in green and pink

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:

SQLBolt-inspired database table

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 == 1

This, 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 == 2

This 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:

Output ordered alphabetically

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:

Output ordered by release year

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/

--

--

--

Software Engineer | JavaScript Enthusiast

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Array In JavaScript

Jump Game II 👀

10 Small JavaScript Apps

Setting Up a Rails API for a React-Redux App

Angular navigation events give a better UX

A comprehensive guide to setting CI with CircleCI, Jest, Enzyme for React: org setup update 2

A layman’s definition of ECMAScript, JavaScript and their buddy — Babel

Going serverless on contact-forms

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Owen Haupt

Owen Haupt

Software Engineer | JavaScript Enthusiast

More from Medium

Managing Data in Containers

Database Anomalies

Virtualizing your data with IBM Watson Query

5 Use Cases for SQL Window Functions in a Real-Life Cat System