Case .. WHEN expression in Oracle SQL

sql

case-statement

oracle-sql

sql-queries

byAnton Shumikhin·Dec 2, 2024

Implement conditional logic in Oracle SQL with CASE statements. They act like the IF-THEN-ELSE commands in a SELECT statement. Here's the basic format:

SELECT 
  CASE job_id
    WHEN 'IT_PROG' THEN 'Programmer'
    WHEN 'ST_CLERK' THEN 'Stock Clerk'
    ELSE 'Other'
  END as job_title
FROM employees;

This logic assigns job_title based on job_id, all set as 'Other' if no terms match.

Advanced usage patterns of CASE in Oracle SQL

Grouping values with IN in CASE

SELECT 
  CASE
    WHEN job_id IN ('IT_PROG', 'IT_SUPPORT', 'IT_MGR') THEN 'IT Department'
    ELSE 'Non-IT'
  END as department
FROM employees;

Here, we've put the nerds 🤓 in the 'IT Department' by grouping multiple job_id values using the IN keyword.

Optimize your data model and queries

Consider creating a mapping table and using a join to link status codes to understandable descriptions. It's like talking to translators, you give them gibberish, they give back poetry:

SELECT e.employee_id, e.job_id, s.status_description
FROM employees e
LEFT OUTER JOIN status_lookup s
ON e.job_id = s.job_id;

DECODE function: CASE's mysterious cousin

In Oracle SQL, DECODE is a slicker alternative to CASE for simple equal-to conditions:

SELECT employee_id,
       DECODE(job_id, 'IT_PROG', 'Programmer', 'ST_CLERK', 'Stock Clerk', 'Other') AS job_title
FROM employees;

This DECODE is no Morse code! A job title is delivered as per labeled job_id.

Driving through the `CASE` junction would be like:

CASE
    WHEN condition1 THEN action1 🟢
    WHEN condition2 THEN action2 🟠
    WHEN condition3 THEN action3 🔴
    ELSE default_action 🔵
END;

Green 🟢: All clear! Go ahead with the action.
Orange 🟠: Not so fast! Assess if there's a better route.
Red 🔴: Stop! Dedicate your action to fulfill these conditions.
Blue 🔵: None of the conditions met? Proceed with the default.

Pushing the boundaries of CASE WHEN in SQL

Aggregate fun with CASE

Clever use of CASE can spice up your aggregation functions:

SELECT
  SUM(CASE WHEN sales > 10000 THEN 1 ELSE 0 END) as high_sales_count
FROM orders;

Above, we're using CASE to count rich customers. 💸

Handle complex situations with nested CASE

You can nest CASE expressions for more complex decisions:

SELECT 
  CASE 
    WHEN age < 20 THEN 'Teenager'
    WHEN age BETWEEN 20 AND 65 
      THEN CASE 
             WHEN occupation = 'Doctor' THEN 'Adult Doctor'
             ELSE 'Adult Other Than Doctor :( '
           END
    ELSE 'Senior'
  END as life_stage
FROM persons;

Navigate common pit traps

Matched CASE vs. Searched CASE: Ensure you use the right type of CASE. A matched CASE uses literal values, whereas a searched CASE allows for complex boolean logic.
Performance considerations: Use CASE wisely. An efficient query might require a join or subquery rather than a lumbering CASE beast.
NULL handling: CASE has an implicit ELSE NULL. If no conditions are met and no ELSE clause is specified, your answer will be NULL. Be prepared!