Case Expression in SQL

Introduction

• The presentation focuses on the case expression in SQL.
• It is akin to switch-case statements in languages like C, C++, Java, and Python.
• The SQL case expression is similar to the if-then-else structure.

Understanding Case Expression

• Functionality: Checks conditions and returns a value when the first condition is satisfied.
• Acts like an if-else ladder, processing sequentially through conditions.
• Stops further checks once a true condition is found.
• If no condition matches, else part is returned, which is optional; defaults to null if not defined.

Syntax of Case Expression

• Begins with the keyword CASE.
• Multiple conditions can be defined using WHEN condition THEN result.
• Optional ELSE for unmatched cases.
• Ends with END.

Example Syntax

CASE
 WHEN condition1 THEN result1
 WHEN condition2 THEN result2
 ...
 ELSE result_n
END;

Examples

Example 1: Categorizing Birth Years

• Context: Table students with attributes ID, name, year of birth.
• Query: Selects students and categorizes them by birth year generation:
  • Conditions:
    • 1965-1980: Gen X
    • 1981-1996: Millennials
    • 1997-2012: Gen Z
    • 2013-2025: Gen Alpha
    • Else: Golden Era
• Output: Adds a description column with generation category.

Example Query

SELECT name, year,
  CASE
    WHEN year BETWEEN 1965 AND 1980 THEN 'Gen X'
    WHEN year BETWEEN 1981 AND 1996 THEN 'Millennials'
    WHEN year BETWEEN 1997 AND 2012 THEN 'Gen Z'
    WHEN year BETWEEN 2013 AND 2025 THEN 'Gen Alpha'
    ELSE 'Golden Era'
  END AS description
FROM students;

Example 2: Ordering with Null Checks

• Context: Table students with potential null values in the year column.
• Query: Orders the list based on year, or by name if year is null:
• Output:
  • Orders by name for null years.
  • Orders by year for non-null years.

Example Query

SELECT id, name
FROM students
ORDER BY
  CASE
    WHEN year IS NULL THEN name
    ELSE year
  END;

Conclusion

• The case expression provides a powerful conditional tool within SQL for creating dynamic queries.
• It is vital for handling multiple conditions and custom ordering in data retrieval tasks.

[Thank you for watching!]