CASE WHEN

Understand `CASE WHEN` for data manipulation

9 minutesVideo LessonPDF notes

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Ready to continue?

Mark this lesson as complete when you're ready to proceed.

Key moments

Introduction to CASE — The CASE statement adds if/else logic to a SELECT query.
Basic CASE structure — A simple CASE statement requires WHEN, THEN, and END keywords.
Multiple WHEN conditions — Multiple WHEN clauses can be chained to categorize data into different groups.
Aliasing and BETWEEN — The BETWEEN operator defines inclusive ranges, and AS aliases the final CASE column.
Conditional Salary Raise — A new scenario requires calculating different pay raises based on current salary thresholds.
Calculation methods — The raise calculation can be written using addition or simplified using multiplication.
Handling edge cases — Careful definition of conditions is necessary to avoid excluding boundary values like 50,000.
Second CASE for Bonus — A second, independent CASE statement is used to calculate a bonus based on Department ID.
Summary and Use Cases — CASE statements are powerful for labeling, categorization, and conditional calculations.

PDF notes

Bottom line: You will learn how to implement conditional logic (like if/else) directly within your SQL queries using the CASE statement. This allows you to categorize data, apply custom labels, or perform different calculations based on specific column values. This logic is executed row-by-row during the query execution.

This lesson on CASE WHEN is hands-on and example-driven. You will learn how to implement conditional logic (like if/else) directly within your SQL queries using the CASE statement. This allows you to categorize data, apply custom labels, or perform different calculations based on specific column values. This logic is executed row-by-row during the query execution.

What You'll Be Able To Do

Write a CASE statement to assign descriptive labels based on numerical criteria.
Apply multiple WHEN conditions within a single CASE block.
Use BETWEEN within a WHEN clause to define an inclusive range.
Calculate new column values using conditional arithmetic within the THEN clause.
Alias the resulting column of a CASE statement using AS.
Construct multiple independent CASE statements within one SELECT query.

Topics Covered in CASE WHEN

Introduction to CASE (0:00 - 0:45) — The CASE statement adds if/else logic to a SELECT query.
Basic CASE structure (0:45 - 1:45) — A simple CASE statement requires WHEN, THEN, and END keywords.
Multiple WHEN conditions (1:45 - 3:15) — Multiple WHEN clauses can be chained to categorize data into different groups.
Aliasing and BETWEEN (3:15 - 4:30) — The BETWEEN operator defines inclusive ranges, and AS aliases the final CASE column.
Conditional Salary Raise (4:30 - 6:45) — A new scenario requires calculating different pay raises based on current salary thresholds.
Calculation methods (6:45 - 8:15) — The raise calculation can be written using addition or simplified using multiplication.
Handling edge cases (8:15 - 9:45) — Careful definition of conditions is necessary to avoid excluding boundary values like 50,000.
Second CASE for Bonus (9:45 - 12:45) — A second, independent CASE statement is used to calculate a bonus based on Department ID.
Summary and Use Cases (12:45 - 13:45) — CASE statements are powerful for labeling, categorization, and conditional calculations.

SQL Cheat Sheet

CASE WHEN <condition> THEN <result> END — Adds conditional logic (if/else) to a SELECT statement
```
CASE WHEN age <= 30 THEN 'young' END
```
WHEN <condition> THEN <result> — Defines a specific condition and the output if true
```
WHEN age BETWEEN 31 AND 50 THEN 'old'
```
END AS <alias_name> — Terminates the CASE block and names the resulting column
```
CASE WHEN age > 50 THEN 'door' END AS age_bracket
```
BETWEEN <val1> AND <val2> — Checks if a value falls inclusively within a range
```
WHEN age BETWEEN 31 AND 50 THEN 'old'
```
THEN salary * 1.05 — Performs calculations based on the met condition
```
WHEN salary < 50000 THEN salary * 1.05
```
THEN salary + (salary * 0.05) — Alternative syntax for calculating percentage increase
```
WHEN salary < 50000 THEN salary + (salary * 0.05)
```

Comparison Table

CASE Use	Syntax Goal	Example Result
Labeling/Categorization	Assigns a descriptive string.	THEN 'young'
Conditional Calculation	Applies arithmetic based on criteria.	THEN salary * 1.07
Raise Calculation (Multiplication)	Shorter syntax for new total.	salary * 1.05
Raise Calculation (Addition)	Explicitly shows added amount.	salary + (salary * 0.05)

Common Pitfalls

Mistake: Forgetting to close the CASE statement. Avoid: Always include the END keyword before aliasing the column.
Mistake: Not aliasing the resulting CASE column. Avoid: Use AS column_name immediately after the END keyword.
Mistake: Missing boundary conditions (e.g., exactly 50,000). Avoid: Ensure all possible values are covered by WHEN or an ELSE clause.

FAQs

What happens if a value doesn't meet any WHEN condition? The resulting column value for that row will be NULL. To prevent this, use an ELSE clause to define a default result.
Can I use an ELSE clause? Yes, ELSE defines the default result if all preceding WHEN conditions are false. This is useful for catching all remaining cases.
Why did some employees not get a raise in the salary example? The defined conditions (< 50000 and > 50000) excluded those making exactly 50,000. Conditions must be carefully defined to cover all boundaries.
Can I use CASE for calculations instead of just labeling? Yes, the THEN clause can contain arithmetic operations, allowing you to calculate new values like salary increases or bonuses.

Common pitfalls

Mistake: Forgetting to close the CASE statement. Avoid: Always include the END keyword before aliasing the column.
Mistake: Not aliasing the resulting CASE column. Avoid: Use AS column_name immediately after the END keyword.
Mistake: Missing boundary conditions (e.g., exactly 50,000). Avoid: Ensure all possible values are covered by WHEN or an ELSE clause.

Hello everybody. In this lesson, we're going to be taking a look at case statements in mySQL. A case statement allows you to add logic in your select statement, sort of like an if else statement and almost all other programming languages or even things like Excel. Let's see how this actually works. So, let's bring this down and let's take this employee demographics table and let's take the first name and let's take the last name, and let's add a case statement. How we need to do this is we have to say case, so that's going to signify that we're starting a case statement. And then, I'm going to go over here and say tab. So, this is where our logic comes into play. So, I'm going to say when the age, let's say, is less than or equal to 30, then. So, I'm saying if the age is less than or equal to 30, then what's going to happen? We'll just keep it really simple for now. We'll just say that this person is young. And then if we want to end the case statement, we'll come down here and say end. So, this is a complete case statement. Let's go ahead and run it. And let's take a look at the output. So, we have the first name, we have the last name, and then we have this case statement right here. And if their age is less than or equal to 30, they're young. And let's actually add the age right here just so we can visually see that as well. So, we have the age. So, this person is the only person who's under or equal to the age of 30, that's April. So, she has a label of young. The great thing about case statements is you can add multiple when statements. So, we can come down here and say when, and then we can do something like when age, and maybe we'll say between. So, I don't know if in previous lessons we've looked at between, but between just says between this number and this number. So, we'll say between 31 and 50. If they're between 31 and 50, oh, good night. That person is old. So, we're going to have it just like this. We're going to run it. And now we have a lot of people who are old. These are all people between the ages of 31 and 50. But we still have more people outside the age of 50 or older than 50. So, we could do when the age, and now we can say greater than or equal to 50, and we're going to say then, and then we're going to say on death's door. Uh, because good night. If you're over 50, my parents are going to love me for this one. So, let's go ahead and run this. And then, if we look at this, we have on death's door right there. Now, this is huge. This is massive. So, let's actually name this. And we'll just say as at the end of end, so right after end, we'll say as age bracket. And let's run this. And this looks a lot better. So, now we have this age bracket, just signifying kind of where people are at. And most people are quite old. Poor Jerry. Uh, you know, can't catch a break, that guy. Now, let's go down and let's take a look at a different table. So, let's select everything. We'll do from employee underscore salary. Now that we have our employee salary table, here is the scenario that we are given. The Pawnee council sent out a memo of their bonus and pay increase for end of year. And we need to follow it and determine people's end of year salary, or the salary going into the new year, and if they got a bonus, how much was it? So, the first thing we need to do is we need to get the pay increase and bonus. And their pay increases look like this. So, if they made less than 50,000, then that equals a 5% raise, very generous. And if they made greater than 50,000, that equals a 7% raise, very, very generous. Lastly, if they work in the finance department, that equals a 10% bonus, just cash that goes into their bank account. Very, very generous, but only the finance department gets it. So, these are the guidelines that the Pawnee council sent out, and it is our job to determine and figure out those pay increases as well as the bonuses. So, let's come right down here. We're going to have our salary employee. I actually want to be able to see these. Let me pull this up just a touch. There we go. So, we want to be able to write this out. So, first thing we should do is just select the columns that we need, first name, last name, probably salary as well. And now, what we can do is determine this first one, which is if they make less than 50,000, they get a 5% raise. So, let's say case, and we'll also add end in here. And we're going to say when their salary is less than 50,000, what's going to happen? Then we say then salary, so we're taking their initial salary, and we're saying plus, then we're going to do salary times 0.05. And if we run this, should work. Let's pull this up really quickly. So, April Ludgate, she made under 50,000, so she got a raise, and her new salary is 26,250. We can actually call that, we'll say as pay underscore, actually, let's do new underscore salary, because that's her new salary. And let's run this. So, the new salary is 26,250. And E Dryer is now making 21,000. Now, this calculation, you can do it different ways. We can do it exactly like this, or we can just do times 1.05. Should be the exact same thing, just however you would like to write it out. It's just, you know, adding it or multiplying it by this. So, let's take this. And now we're going to say when it is greater than 50,000, so let's say greater than 50,000, they get a 1.07. So, this is the 7% increase. This is a 5% increase. This is a 7% increase. And let's run this. And let's put this up here. So, now if they made greater, so 50,000 to 75,000, they got a 7% increase. Now, unfortunately, we did not make the rules. The Pawnee council did. And the people who made exactly 50,000, unfortunately, were not part of those brackets. And that just wasn't up to us. We couldn't control that. So, unfortunately, Tom Haverford and Jerry Gergich just didn't get raises this year. And that's not our fault, okay? That's not our fault. Now, the next thing that we need to do is determine the bonuses. Now, let's come right back up here really quickly. And let's just copy this. Because what we need to determine is how we know that somebody is in the finance department. Because if they're in the finance department, that means they get a 10% bonus. That's really important. Now, it's not in the employee demographics. We don't have anything about the department. But if we look in the salary, and we run this, we do have the department ID. Now, let's open up and let's pull this up right here. We'll look at the parks department. And in the parks department, here we go. The finance is department ID of 6. So, if we're looking at the salary, there's only one person who's in department ID equal to 6. So, what we can do is another case statement. We can say comma, we'll do case and end. And we'll do another one. We're going to say when dept id, so when the department ID is equal to 6, then we're going to give them a bonus. So, we're going to say salary times 0.10. And we'll call this as bonus. Let's go ahead and run this. And let's pull it up. So, he gets a $7,000 bonus this year, that's Ben Wyatt, because he was part of the finance department that just did an exceptional job this year, apparently, according to the Pawnee council. So, that is how case statements work. They're really powerful, really useful. I honestly use them quite often. And they're just a way to really add some logic and some, you know, labeling or even do calculations like we did right here with the salary. In the next lesson, we're going to be taking a look at subqueries in mySQL.

Frequently asked questions

What happens if a value doesn't meet any WHEN condition?

The resulting column value for that row will be NULL. To prevent this, use an ELSE clause to define a default result.

Can I use an ELSE clause?

Yes, ELSE defines the default result if all preceding WHEN conditions are false. This is useful for catching all remaining cases.

Why did some employees not get a raise in the salary example?

The defined conditions (< 50000 and > 50000) excluded those making exactly 50,000. Conditions must be carefully defined to cover all boundaries.

Can I use CASE for calculations instead of just labeling?

Yes, the THEN clause can contain arithmetic operations, allowing you to calculate new values like salary increases or bonuses.