Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
The CASE expression
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The CASE
expression is part of the standard SQL syntax. While some RDBMS also offer an IF
expression, or a DECODE
function, you can always rely on the two types of CASE
syntax:
SELECT -- Searched case CASE WHEN AUTHOR.FIRST_NAME = 'Paulo' THEN 'brazilian' WHEN AUTHOR.FIRST_NAME = 'George' THEN 'english' ELSE 'unknown' END, -- Simple case CASE AUTHOR.FIRST_NAME WHEN 'Paulo' THEN 'brazilian' WHEN 'George' THEN 'english' ELSE 'unknown' END FROM AUTHOR
create.select( // Searched case when(AUTHOR.FIRST_NAME.eq("Paulo"), "brazilian") .when(AUTHOR.FIRST_NAME.eq("George"), "english") .otherwise("unknown"); // Simple case choose(AUTHOR.FIRST_NAME) .when("Paulo", "brazilian") .when("George", "english") .otherwise("unknown")) .from(AUTHOR) .fetch();
In jOOQ, both syntaxes are supported (The second one is emulated in Derby, which only knows the first one). Unfortunately, both case and else are reserved words in Java. jOOQ chose to use decode() from the Oracle DECODE
function, or choose(), and otherwise(), which means the same as else.
A CASE
expression can be used anywhere where you can place a column expression (or Field). For instance, you can SELECT
the above expression, if you're selecting from AUTHOR
:
SELECT AUTHOR.FIRST_NAME, [... CASE EXPR ...] AS nationality FROM AUTHOR
Short forms of the CASE expression
The SQL standard and some vendors support a variety of short forms of the CASE
expression, usually in the form of functions. These include:
Sort indirection is often implemented with a CASE
clause of a SELECT
's ORDER BY
clause. See the manual's section about the ORDER BY clause for more details.
Feedback
Do you have any feedback about this page? We'd love to hear it!