'if' in 'SELECT' statement - How to Manipulate Output Based on Column Values

sql

case

ifnull

null-handling

byAnton Shumikhin·Aug 21, 2024

For applying conditional logic in SQL SELECT statements, utilize the CASE syntax:

SELECT
  column1,
  column2,
  CASE WHEN column1 > 10 THEN 'High' ELSE 'Low' END as Status
FROM
  your_table;

This setup classifies the column1 values as either 'High' or 'Low' and portrays them in a new column dubbed Status.

Tackling Various Cases in SQL

Working with data that showcase multiple states requires robust methods of handling. Let's delve into scenarios where our SQL statements can cover diverse instances effectively.

When dealing with financial data

With financial entries that could either swing to positive or negative, CASE comes to the rescue:

SELECT
  transaction_id,
  transaction_type,  
  CASE
    WHEN transaction_type = 'Credit' THEN amount   -- Your money is safe and sound!
    WHEN transaction_type = 'Debit' THEN -amount  -- Oopsie! Money is being withdrawn.
    ELSE 0   -- Ghost transaction? 👻 Let's keep it as 0.
  END as AdjustedAmount
FROM
  transactions;

The above query ensures AdjustedAmount seamlessly syncs with transaction_type and churns accurate values.

Handy `IF()` for simpler cases

For less complex conditions, IF() brings brevity in MySQL:

SELECT
  id,
  IF(report_type = 'P', amount, -amount) AS adjusted_amount
FROM
  financial_report;

This query adjusts the amount based on the report_type, negating it for 'N'.

Navigating through `NULL`

Preemptive measures are essential in preventing NULL errors. The IFNULL() statement, comes to aid holding a reliable fallback value:

SELECT
  id,
  IFNULL(amount, 0) as SafeAmount     -- Don't let 'NULL' ruin your day
FROM
  transactions;

Here, SafeAmount column replaces any NULL amounts with a safe zero.

Chaining conditions for robustness

Continue the NULL safe practice with an exceptional combination of IF() and IFNULL():

SELECT
  id,
  IF(report_type = 'P', IFNULL(amount, 0), -IFNULL(amount, 0)) as amount     -- Playing it safe, always!
FROM
  financial_report;

This efficient chaining handles report type differentiation and drafts a contingency for potential NULL values.

Further Exploration of SQL Quirks and Features

Let's dive deeper into SQL's offering, exploring how to handle different behaviors, handle nulls, and using advanced case statements.

Flipping negatives on its head

Negative amounts are an anomaly we’d rather avoid. Thankfully, SQL lets us apply -amount within CASE when needed:

SELECT
  id,
  CASE
    WHEN transaction_type = 'N' THEN -amount     -- Flip it! 🔀
    ELSE amount
  END as AdjustedAmount
FROM
  transactions;

This proves handy when we want our AdjustedAmount to be absolute.

Having a net for the unknown

The ELSE clause within CASE provides a safety net for undefined conditions:

SELECT
  id,
  CASE
    WHEN transaction_type IN ('P', 'N') THEN IF(transaction_type = 'P', amount, -amount)
    ELSE NULL   -- Better safe than sorry 🤷‍♂️
  END as Outcome
FROM
  transactions;

This foundationally gives an Outcome only for recognized transaction_type and a safety NULL for the rest.

Versatility of display

CASE allows for varied conditions and responses:

SELECT
  Country,
  CASE
    WHEN Continent IN ('Asia', 'Europe') THEN 'Eurasia'    -- We're all united after all 🌍
    WHEN Continent = 'America' THEN 'New World'
    ELSE 'Other'
  END as Region
FROM
  World;

This makes it possible to analyze data across regions easily.

Post-condition ordering

You can organize your query's outcome using ORDER BY post-CASE:

SELECT
  Country,
  CASE
    WHEN Population > 50000000 THEN 'Large'
    ELSE 'Small'
  END as Size
FROM
  Countries
ORDER BY
  Size DESC;   -- Let's arrange from big to small, Size matters! 😉

This places your Size in descending order, making skimming through the result an easy task.

Direct embedding for quick output

CASE is versatile enough to be directly embedded within a SELECT statement for immediate output:

SELECT
  Country,
  GDP_Per_Capita,
  CASE
    WHEN GDP_Per_Capita > 30000 THEN 'High Income'      -- 💲💲💲
    WHEN GDP_Per_Capita BETWEEN 10000 AND 30000 THEN 'Middle Income'
    ELSE 'Low Income'     -- Well, it's not all about money, right? 🤷‍♂️
  END as Income_Status
FROM
  Economic_Data;