Explain Codes LogoExplain Codes Logo

If statement in select (ORACLE)

sql
case-statement
oracle-nulls
sql-best-practices
Anton ShumikhinbyAnton Shumikhin·Oct 11, 2024
TLDR

Use the CASE expression for implementing IF logic in your SELECT statement:

SELECT your_column, CASE WHEN your_condition THEN 'ResultWhenTrue' ELSE 'ResultWhenFalse' END AS new_result_column FROM your_table;

Update your_condition, 'ResultWhenTrue', 'ResultWhenFalse', new_result_column, and your_table to match your dataset. The above code evaluates each row against your condition and reflects the output in new_result_column.

Column Equality & Truthiness–Reality Is Relative 🌌

If you're interested in column comparisons, check out:

SELECT CASE WHEN ISSUE_DIVISION = ISSUE_DIVISION_2 THEN 1 ELSE 0 END AS are_they_same FROM your_table;

And just a friendly neighborhood reminder, in Oracle world null ≠ null 🥷. If you're about peace, love and equality then NVL or COALESCE are your best pals.

Better Simple than Sorry–No Time for Drama! 🪂

Nested case statements? They might display your SQL prowess, but also increase the potential for headaches. Stick to plain and simple structures when you can avoid complexity.

When Syntax Spells Trouble–Beware of the Quotes! 😈

Oracle isn't very forgiving when it comes to syntax. Confuse your single quotes (') with double quotes (") and you're in a soup. Also, incorrect or inconsistent column references can lead to unexpected output.

Nulls are Never Alone–Except When They Are 🦠

Null values in Oracle are like teenagers. They simply refuse to be equal! A CASE statement considering nulls might not behave as you'd expect. Leverage Oracle's NVL or COALESCE functions to handle null columns.