when it comes to writing sql there are many different ways to accomplish the same thing
but in this video i want to share with you a few subtle tips and tricks that can simplify your code
or maybe just an alternative way to accomplish the same thing and while i'll be doing all of
this in snowflake most of this should still apply to other database providers as well number one the
first thing we're going to look at is case when format typically when you see a case one it's
something like this and while this will work one trick that you can do is if you see in this case
here every single when statement is using the same condition it's all based on team equals a
way we can simplify this is by doing something like this we can say in the top case team
and then when bulls when lakers one heat essentially we've removed that team equals
for each of these if we look at them right next to each other again instead of doing it each
time here we just do it once here and the result is the same if we look down here bulls chicago
now we run this one bulls chicago same output it works the same it's just maybe a little bit more
elegant of a way to write it next is going to be using a boolean verse a case 1 statement this is
kind of similar to the last one we looked at but one thing that i've certainly done in the past and
that i think a lot of people will do is if you know something is either true or false you'll
create it as a case when statement this condition is saying when team is bulls then true else false
so true same with lakers false and again this works there's nothing inherently wrong with this
but you could accomplish the same thing without the case when statements you could simplify what
you're writing if we take a look at this option here instead of case when if we know it's just a
true or false you could say team equals whatever you want it to equal team equals the other option
and it's either going to return a true or false for that statement again here we have true false
run this true false just a maybe more simple way to write this without the case one the third one
here is referencing the same column and this is one where it might be specific to cloud
sql providers i'm not sure if you can do this on sql server or postgres or anything like that
just want to call that out this example is when you get a little bit more complex logic
and what i've done here is we have one case statement we're determining the most famous
shooting guard and now let's say we want to nest that condition in another one i've seen this a lot
where people will just copy and paste that logic so that it matches exactly what we had before but
you need to do something else on top of it in this case here we're adding a new column called
has most rings and essentially we're saying when the result of this case is michael jordan then
true otherwise false and again this this works but it seems redundant and then we talk a lot about
drive meaning don't repeat yourself and when you see something like this we're clearly repeating
ourselves so there has to be a better way to do this and the way that you can do this at least on
snowflake is to just reference the name of another column when it's in this case the column where
we're already doing that logic so here if we look at this we have this column called most famous
sg and instead of rewriting the whole case statement we say case when this most famous sg
it'll look at the result of that column equals michael jordan then true else false
and we'll get the same result as we would here and what i wanted to add here was actually combining
the other previous concept that we said with the booleans meaning true or false all we have to
say here is this equals michael jordan and it will give us the same result as if we were to write out
that case when so we we can go something like this to one line just by understanding how it works so
let's take a look here get the same result true true meaning these two options had the same result
just a more simplified way of writing it and using the inherent abilities of snowflake and some of
these sql engines to give you what you want all right next is if verse case and this also might
be one that's specific to snowflake if we open up the documentation it says it's a single level
if then else expression similar to case but only allows a single condition as it says it's very
similar to case so you may see something like this where you say case one one thing then this else
this there's no other condition this will work but the alternative is to use the if condition and
it would look something like this if team equals bucks this is the first condition if this is true
return this otherwise return this let's see maybe next year maybe next year same result and it works
again only if you have an either or type of option the last one here is not necessarily
a trick or another way to do something it's more of just a general piece of advice
and that is being inconsistent with your code in terms of uppercase lowercase spacing at the end
of the day you want everything to look clean you want what you provide and share with your
colleagues or that you're deploying as production code to look good you don't want it to look sloppy
in any sense so when i look at something like this we can see some of these keywords are uppercase
some are starting with a capital t spacing is different you know we have different lines some
of the the column name here is uppercase this is lowercase and similar to the other ones this isn't
going to break anything technically this will run and there's no problem so you could deploy
this and you can go through and it'll work but in my opinion it's just lazy i think you should
take pride in what you're writing and there's a bunch of different ways you could write this
and what's important is that you do decide on something as a team and you're consistent with
it as an example in this case the decision here was to make everything all action words
lower case everything has a certain indentation so it's consistent all case statements have the same
structure so it's case when ends on the same line ends at the end even when it's a small
amount it's the same thing all column names are lowercase we didn't change the logic but
the presentation of it in my opinion is cleaner easier to read and something you can be more
proud of passing on to teammates or pushing out for the public to see so that'll do it for this
video just a couple of tips and tricks on how you can improve your sql thanks for watching