204 — Advanced Expressions and Set Operations

Intermediate

Master CASE WHEN for conditional logic, handle NULLs gracefully with COALESCE, manipulate strings and dates, and combine result sets with UNION and friends. This lesson uses SQLite-flavored functions and emphasizes how result grain changes when you group or stack results.

Learning Objectives

Use CASE WHEN to create conditional columns and categorize data

Handle NULLs with COALESCE and NULLIF

Apply string functions to clean and transform text

Use date functions for time-based analysis

Combine result sets with UNION, INTERSECT, and EXCEPT

Build dashboard-ready queries with expressions

State the result grain before using CASE, GROUP BY, or UNION in a report

Step 1

CASE WHEN — conditional logic in SQL

Use CASE WHEN to add conditional logic directly in your SQL query.

Commands to Run

SELECT name, price, CASE WHEN price < 20 THEN 'Budget' WHEN price < 50 THEN 'Mid-Range' WHEN price < 100 THEN 'Premium' ELSE 'Luxury' END AS price_tier FROM products ORDER BY price;

What This Does

CASE WHEN evaluates conditions in order and returns the value for the first TRUE condition. It works like an if/else-if/else chain. The ELSE clause catches anything that didn't match earlier conditions. END closes the CASE block. This lets you create new categories, labels, or calculated values on the fly without modifying your data.

Expected Outcome

Every product now has a price_tier column: Budget, Mid-Range, Premium, or Luxury. Products are sorted by price so you can see the tier boundaries clearly.

Pro Tips

1
CASE WHEN conditions are evaluated top to bottom — put the most specific conditions first
2
Always include an ELSE clause to handle unexpected values gracefully

Was this step helpful?