Conditional Returns with SQL case / then

Tyler Clark
InstructorTyler Clark
Share this video with your friends

Social Share Links

Send Tweet
Published 4 years ago
Updated 3 years ago

Similar to if / else statements in many programming languages, SQL has the case then statement. Using this statement allows a query to be written dynamically depending on the condition it is checking against.

Instructor: [0:00] The SQL CASE expression is a generic conditional expression. It's similar to the IF ELTs statements you'll find in other programming languages. As you see here, we're doing SELECT first name, and then we say CASE when status is NULL, THEN member ELT status. END from our user's table.

[0:21] Again, just like when using an IF ELTs block in another language, this CASE expression allows us to conditionally return something else, depending on the expression we tell it. For the CASE syntax to be correct, we need CASE WHEN, then our expression. Next is this THEN keyword, the results, and then END here.

[0:43] Just like IF statements in C# or JavaScript, we could continue to add additional WHENs and ELTs to continue checking expressions and changing what actually gets returned. As you can see from our results, down below, most of our users had NULL for their status column.

[0:59] Let's find someone that has a status that is not NULL. As we can see, Lucy is an instructor. Our case will obviously return the current status, and not member, because of our WHERE that's filtering them out.

[1:10] CASE clauses can be used wherever an expression is valid. Let's look at another example where we'll use the CASE condition inside of a WHERE clause. WHERE CASE, when email is not NULL, then start date needs to be greater than January 1st 2019.

[1:29] An expression is required after this WHERE so a CASE clause can live here. All these individuals have emails, which puts us into this THEN, and all of their start dates are greater than our date.

[1:40] Notice we don't have an ELTs like the previous example we looked at, because it's optional. When there isn't an ELTs, then it returns NULL. Our clause is just WHERE NULL, which doesn't return any rows IF any individual's emails were NULL.

egghead
egghead
~ 43 minutes ago

Member comments are a way for members to communicate, interact, and ask questions about a lesson.

The instructor or someone from the community might respond to your question Here are a few basic guidelines to commenting on egghead.io

Be on-Topic

Comments are for discussing a lesson. If you're having a general issue with the website functionality, please contact us at support@egghead.io.

Avoid meta-discussion

  • This was great!
  • This was horrible!
  • I didn't like this because it didn't match my skill level.
  • +1 It will likely be deleted as spam.

Code Problems?

Should be accompanied by code! Codesandbox or Stackblitz provide a way to share code and discuss it in context

Details and Context

Vague question? Vague answer. Any details and context you can provide will lure more interesting answers!

Markdown supported.
Become a member to join the discussionEnroll Today