The SQL DECODE function allows you to add procedural if-then-else logic to queries.

SELECT DECODE(1, 1, 'Equal');

You can think of the DECODE function as having an "output," or "return value", which can be used in the containing SQL query. Above, DECODE is being passed three arguments. The first and second are equality-checked against each other, i.e.:

1 == 1 # ??

If the equality check "returns true", then the third argument will be the "result" or "return value" of the DECODE function. Let's add an example schema to demonstrate an example.

Movies

Example Query:

SELECT title FROM movies ORDER BY DECODE(*x*, 1, id, 2, title, released);

Let's breakdown this query:

• SELECT title : we will be returning each row's title (FROM movies)
• Which attribute to ORDER BY will be determined by the DECODE function:
  • The first argument (x) will be checked against the second argument (1)
  • If equality is established, we "return" the third argument (id) to ORDER BY
  • If equality is not established, we then compare the first argument (x) to the next (fourth) argument (2)
  • If equality is established, we "return" the fifth argument (title) to ORDER BY
  • If equality is not established, we then return the final argument (released) to ORDER BY

Here's an example of this logic in the form of a JavaScript if-else block:

/* Note how the arguments are used sequentially */
const *x* = someValue; // first arg
let decodeResult;

if (x === 1) { // second arg
	decodeResult = "id"; // third arg
} else if (x === 2) { // fourth arg
	decodeResult = "title"; // fifth arg
} else {
	decodeResult = "released"; // sixth (final) arg
}

return `ORDER BY ${decodeResult}`;

We see that the arguments are used systematically and in order. Let's look at some real examples/output using our Movies table:

SELECT title FROM movies ORDER BY DECODE(2, 1, id, 2, title, released);

• SEE RESULT