SQL 201: How to add IF-ELSE logic to SQL queries

Helen Anderson on August 19, 2019

CASE is SQLs answer to other programming languages IF-ELSE statement. If you need to test a condition and return a value CASE is the expression yo... [Read Full]
markdown guide
 

I think this query could be simplified.

select 
  game,
  global,
  case 
    when global >= 4.00 then 'high'
    when global > 2.00 and global <= 3.99 then 'mid'
    else 'low' end 
  as 'Sales Range'
from 
  sega_sales
SELECT 
  game,
  global,
  CASE 
    WHEN global >= 4.00 THEN 'high'
    WHEN global >  2.00 THEN 'mid'
    ELSE 'low'
  END AS [Sales Range]
FROM
  sega_sales

You don't really need to check if global is equal or less than 3.99 because you're already in another condition.

 

Numbers like 3.995 would become low as well.

 

No. 3.995 is not gte 4.00 so it moves to the next check. It IS greater than 2. So mid

I was pointing at the first snippet, which has two conditions to check for mid.

 

You can check nulls with CASE WHEN (dynamic VALUES is a Postgresism, but the important part works in SQL Server too):

SELECT CASE WHEN x IS NULL THEN 'yes' ELSE 'no' END AS is_null
FROM (VALUES (true), (null)) AS a (x);

 is_null 
─────────
 no
 yes
(2 rows)

It's the CASE x WHEN ... construction that does not work, since nulls aren't equivalent to anything, even each other:

SELECT CASE x WHEN NULL THEN 'yes' ELSE 'no' END AS is_null
FROM (VALUES (true), (null)) AS a (x);

 is_null 
─────────
 no
 no
(2 rows)
 
 

Great article and example! Since you didn't mention when to use this, or more importantly when NOT to use it, I wanted to caution users that just found something they can use EVERYWHERE. Take the above example. If it simply did what SQL is great at doing, return the stored values, it could be used for several different reports. What if u wanted to change those boundaries or labels. As a 'general' rule, I don't use CASE unless it literally changes rows or columns that may be returned in the end result. Remember that SQL isn't a presentation layer formatter, it's to retrieve data for someone to format the way they want.

 
 

But why? SQL is built well to do mathematical operations and return data. Manipulating data is 'typically' better to be done at a higher layer. This article is a great example of how to use it, but a bad example of when (which I do not believe was her intention).

 

I'm sorry if you misunderstood me. I was referring to her article being a bad example of when to use it. However, that was not the intent of the article, so I just wanted readers to realize that this would 'probably' not be a good business case.

I'm not sure what your upset about, I wasn't negative at all and, in fact, complemented the writer on her article.

 
 

You always have the best data examples Helen, from grunge bands to Megadrive games!

 

Need to keep it more exciting than the usual data sets - how many purchase orders? how many kids in each class? 😪

 

I'm working on the interactive SQL lessons for the crash course now. You should see some of the ideas I've had for the data to base them on. Have to keep reminding myself that Linkin Park is classic rock to kids today, never mind Led Zep and Deep Purple.

My kids are voting for using the Marvel movies. Either that or My Little Pony but even I think that's a step too far.

Boring data exercises be gone.

<hastily heads to VS Code to delete all of the crappy purchase order-related SQL questions he'd already prepared...>

 
 

Helen, your format for these posts is amazing! Kudos! I love the navigation between posts!
...and now to read the actual content.

 
 

You can also go
Case condition when 1 then 'value' When 2 then 'other value' when 3 then 'yet another value' else 'default value' end

Also you can use case in where.

code of conduct - report abuse