CASE Statements Tutorial in SQL

DON'T MISS THIS ONE!! 🤯

Got BDE?⚡️

If you wanna support my newsletter and click rates, please CLICK A LINK somewhere in here ❤️ And subscribe on YouTube 🎥— Just hit 7.5K subs 🎉

And BIG announcement… I’m moving my courses to Kajabi to provide an even better user experience!! 🥳 I’m also planning some big, BIG Black Friday deals! Reply to this email on what you wanna see 👀

If you’ve been keeping up with the angry face emoji LORE from last week 🔗, I have a new advancement for you LOL. Last week’s post prompted another angry email from a different person. Guess a lot of people are mad about this emoji: 😡

CASE Statements: The Swiss Army Knife of SQL

Case is a serious SUPER POWER in SQL because it can help you categorize data, replace nulls, create binary columns, and even PIVOT data in SQL. Yes, like making a pivot table summarize your data! We don’t have time to learn alllll of this here, but head on over to my 🔗intermediate course if you want to learn more on CASE and become an expert 😉 CASE is one the biggest ROI SQL skills you can learn, so my intermediate course has a WHOLE chapter on them.

Case works like a fancy if then statement where you can define multiple conditions and it will output the result for the first condition met. Here’s the basic syntax:

case 
   when condition1 then output1
   when condition2 then output2
   ...
   when conditionN then outputN
   end as column_name

Because it steps through the conditions in order, ORDER MATTERS if your logic is dependent on the previous conditions. That means if you’re relying on waterfall logic and depending on and building off of the previous conditions above, you have to make sure your logic is super tight and there are no holes or unaccounted for edge cases. It’s really easy to accidentally forget an equals sign or mess up your numbers and produce incorrect results. See the query below where we rely on waterfall logic to classify revenue.

select 
    order_id,
    price * quantity as rev,
    case 
        when rev < 100 then ‘small’
        when rev < 500 then ‘med’
        when rev < 1000 then ‘large’ 
        else ‘extra large’
    end as rev_amount
from 
    orders
;

If the first condition isn’t true, we go to the second. If the second condition isn’t true, we go to the third. And so on until a condition is true! For example, the second condition (classifying med) relies on the logic that the number is less than 500, and assumes it’s also greater than or equal to 100 since the first condition was NOT met. And for the 3rd condition (classifying large) relies on the logic that the number is less than 1000, and assumes it’s also greater than or equal to 500 since the first and second conditions were NOT met. This is what I mean by waterfall logic!

To emphasize my point further, see what happens when you switch the order of the conditions and put large on top. Does this change your output? It does, because it’s no longer relying on the small and medium conditions since they’re further down in the logic and CASE returns the FIRST met condition output. This produces INCORRECT results below and accidentally misclassifies all the small and medium values as LARGE:

select 
    order_id,
    price * quantity as rev,
    case
        when rev < 1000 then ‘large’ 
        when rev < 100 then ‘small’
        when rev < 500 then ‘med’ 
        else ‘extra large’
    end as rev_amount
from 
    orders
; 

This is why order matters when you use dependent waterfall logic and why you have to be so careful when defining logic in CASE. If you want to write a query that does not rely on waterfall logic (and order doesn’t matter), you can clearly define the upper and lower endpoints of your value ranges. Just be careful with your equal signs here and don’t leave any holes in your logic. See the YouTube video 🔗 for an example of this!

Keep that BDE rolling 💅🏼

Jess Ramos 💕

---

⚡️If you’re new here:

💁🏽‍♀️ Who Am I?

I’m Jess Ramos, the founder of Big Data Energy and the creator of the BEST SQL course and community: Big SQL Energy⚡️. Check me out on socials: 🔗YouTube, 🔗LinkedIn, 🔗Instagram, and 🔗TikTok. And of course subscribe to my 🔗newsletter here for all my upcoming lessons and updates— all for free!

---

⚡️ Social Highlights:

jessramosdata

A post shared by @jessramosdata