CASE

Video Transcription

Welcome back everyone. In this video, we’re going to be taking a look at the case statement in SQL. So the case statement is very, very much like the switch statements in Java, although in Python, they don’t have the language to have a switch statement. But just like a chained if else clause. So in its simple form, it’s going to compare scalar values, so like numbers and things like that, and return a value with its first match. Now, there is also a different form, which evaluates a predicate looking for the first match for that predicate. And the first match is identified when the predicate turns true. And now, there’s a lot of useful functions that we can utilize as part of the case statements, which include is no coalesce if and choose. So we won’t necessarily use all of these, but some of these are quite useful. So is Knowles kind of self explanatory finds the first no value. And then coalesce is going to evaluate arguments in order. So whatever your expression is, and then all of the columns or whatever that you include there, and it’s going to evaluate all of those in order and find the first one that does not have initially evaluate to know.

And so choose, then is going to, which we don’t have, I don’t have a lot of examples of, but choose is going to return an item from a specified index from the list value. So so if I have choose and then choose one, from XYZ, this becomes a little bit more interesting when a value one value to value three, so on and so forth, is not hard coded, but a result of a column or something like that, that becomes a little bit more interesting. So you can at face value choose doesn’t offer you a lot, but it can be very, very powerful. In the in a certain context. We also have if, which is an inline if statement, so if this Boolean expression is true, do the true value otherwise include false, this is very similar to how actual and if statement works in Excel if you’ve ever worked with if statements in Excel, we also have nolloth which is very similar, similar process there. And out of these though, coalesce is the only function that is standard is no if choose are all things that are included in the SQL language as defined by Microsoft SQL Server. So here is an example of a simple case, or a simple case statement. And it’s simple form. So we have all of our so we have our case statement syntax here. So case and so that is the end of it.

So case and so this is basically switch on state province code or if state province code. So case state province code and then when state province code is a k, then crazy fair, when state province code is Kansas, then the place to be when P R US territory when the I US territory. All other cases will be sales territory. So let’s go ahead and run this bit. And we’ll look we’ll explore the the what if part here in just a moment. So we can kind of see already a few of these right? Here’s Alaska crazy fair. And let’s scroll down and find Kansas. There we go. Kansas is the place to be. So this is a really nice, easy way to translate a column into something that is a little bit more user friendly, if that makes things easier for your users. Now what happens when I exclude our else clause? Well you had lots of Knowles Right Knolls, because we only have we only have when state province code matches these four, these four conditions, then we have a value that replaces the state province code. Otherwise, when it doesn’t match any of these, we don’t actually give a value to be put in, we don’t actually give a value to be put in its place, everything would just show up as No, then because the absence of value is no.

So this is the kind of tricky part here we want to be careful there. So if you don’t have an else clause, be aware that a null value will be placed for things that do not match any of your cases, in your cases statement. But this as it is, is in its simple form, meaning that the cases that when a KK SPR, so on and so forth, is a in its simple form. So state province code, it does actually have to search anything. When it’s this, it’s this one, it’s that it’s this, it’s not actually searching in the column. But we can do a different form. So notice, in this syntax here, my state province code is up along with the case. So case, state province code. But we can have something that looks like this same exact functionality. Same exact functionality. But state province code is here. And now instead of actually using the state province code, we’re actually using the sales territory, right? And stead of comparing the states, right. So this is kind of the benefit of using the search form of a case statement, we can actually change which columns we actually compare in each of our cases. So that’s the benefit of the benefit of and the difference between the two, the simple form is going to look and compare the cases for the same column, right?

The same value they so case, XYZ, and it’s going to compare that XYZ to each of the each of the when clauses, right? Otherwise, and the search form, we’re only we’re going to x, we’re going to look at each of our one clauses, whichever one is whichever one is true first, and only and we can actually mix and match, right? We can use different columns in different cases. So that expands the flexibility quite a bit for our cases. So different forums here. So when and where and why would I care about a case statements? So here is a one common need? So a very useful tool to replace values in a column when there are no. And so here’s a case case when the order comment is not null, then keep the comment, right. So if a comment exists, I want to keep the common in. But when there is no comment, meaning the comment is No, instead of showing the user No, I’m actually going to put the the word no comma or the text there. No comment. So let’s actually run this here. And there we go. So now you see the original comments and comments. Okay, so other bits that are a little bit more useful, at least very similar to this style, is something that looks like this.

So we have select the same sort of thing that we have up here, but instead of a case statement, I can substitute the is null function. So it’s going to check to see if, if the comment is no then replace it with no comments. So both of these queries here are pretty much identical in operation and achieve the same results. But just remember that the is known as the built in function in SQL Server and not necessarily NC standard. So depending on the kind of database you are working with, or the kind of software you’re working with, that option may or may not exist. But let’s look at a few other options here. So another case statement, similar to one we have before, but we can continue to add more and more options to our case study, we don’t have to have just one or the other. This is also where the case statement is superior than the built in functions. Because the is no, it’s just one, one, right? We can only do one replacement, not multiple replacements. So here, we can actually have more than one option. So when comments is not an all then comments when internal comments is not an all then internal comments. Otherwise, we replace both instances of No, with no comments, that is equivalent to coalesce.

So it’s going to pick so the case statement is going to execute and, and well replace the value with whichever case becomes true first. So if the first win is true, then that’s not going to do the second when or the or the else. Likewise, if the second check when internal comments is true, then it’s not going to do the else clause, and so on and so forth. Right? Just like what if, if, if else if else else clause with do inside your programming languages. So we can do the same thing with the built in function called coalesce, which will, which will spit out or replace, whichever one is not null first. So if both comments and internal comments are no, then no comments will be actual placed in that column for that row. But if comments or internal comments is not null, then they will be used. So both of these two queries that I have up here on the screen are identical and functionality. And coalesce is a standard operation. So you would see that in a lot of other software databases as well. That is one of the other one of the main common patterns. Another common pattern that I see the case statement being useful, and common n is this situation here.

So slug person that E and PR is salesperson, and then for this particular column, and this is something that I haven’t highlighted yet, but notice that the case statement is a column, right? Not in the front, or where clause. So this is a projection, a projection operation. So case when salesperson is one, then sales department else not in sales. So a lot of times in databases, we’ll find ourselves to help storage space, or a lot of times our IDs and keys and things like that, that uniquely identify a row are numerical. And that doesn’t translate back well to our actual software that’s utilizing the database. So we can actually program in in our queries to replace those automatic numbers with the actual human readable text that that that that number replaces, or that number represents. So in this case, if the is salesperson is one, then they’re in sales, otherwise, they’re not in sales.

And you can see a couple of examples down here that show that replacement happening. This particular case statement is equivalent by the way to using an inline if statement. So, if if the salesperson is equal to one then sales department otherwise not in sales. So that is equivalent to the previous case statement that actually showed and do not write the if the inline f is not necessarily anti standard. So you may not see that and other database management systems that you may use. But that is the case statement and other useful functions that operate in a similar manner. And so remember the case statement that exists typically in your SELECT clause. And you can have multiple case statements in your select. So if you want to have a case statement for that gets applied to different columns, or represents other multiple columns that you want to project out. You can add multiple case statements in there if you so choose. And you can replace those some of those cases. Midsumma simpler ones with these other useful functions that I have listed here but remember coalesse is the only one here that is standard SQL