SQL CASE Expression Tutorial 🎯

Welcome to our deep dive into SQL's powerful CASE expression! This tutorial is designed for both beginners and intermediates, so let's get started.

Introduction 📝

The CASE expression in SQL is a flexible tool that allows you to perform conditional logic within a SQL query. It's similar to the if-else statement in programming languages, but specifically tailored for SQL.

Basic Syntax 💡

The basic syntax of a SQL CASE expression is as follows:

sql

CASE
    WHEN condition_1 THEN result_1
    WHEN condition_2 THEN result_2
    ...
    ELSE default_result
END

Each WHEN clause checks a condition, and if the condition is true, the corresponding result is returned. If none of the conditions are true, the ELSE clause's result is returned.

Example 💡

Let's consider a simple example:

sql

SELECT name,
       CASE
           WHEN age < 18 THEN 'Minor'
           WHEN age >= 18 AND age < 65 THEN 'Adult'
           ELSE 'Senior'
       END AS age_group
FROM people;

In this example, we're selecting the name and a new column age_group from a people table. The age_group column is calculated using a CASE expression that checks the age of each person and assigns them to a corresponding age group.

Nested CASE Expressions 💡

You can nest CASE expressions to create more complex conditions. For example:

sql

SELECT name,
       CASE
           WHEN income > 50000 THEN 'High Income'
           ELSE CASE
                   WHEN income > 30000 THEN 'Medium Income'
                   ELSE 'Low Income'
               END
       END AS income_group
FROM people;

In this example, we first check if the income is greater than 50000. If it is, we assign the High Income label. If not, we check if the income is greater than 30000, and if it is, we assign the Medium Income label. If neither condition is true, we assign the Low Income label.

COALESCE Function 💡

The COALESCE function can be used to provide a default value when all conditions in a CASE expression are false. For example:

sql

SELECT name,
       COALESCE(
           CASE
               WHEN age < 18 THEN 'Minor'
               WHEN age >= 18 AND age < 65 THEN 'Adult'
               ELSE 'Senior'
           END,
           'Unknown'
       ) AS age_group
FROM people;

In this example, we've added 'Unknown' as the default value in case no condition in the CASE expression is true.

Quiz 🎯

Conclusion 📝

The SQL CASE expression is a versatile tool that helps you make decisions within your SQL queries. By understanding its syntax and nested usage, you can write more complex and powerful SQL queries. Happy coding! 🚀