CASE is SQL’s answer to other programming languages IF-ELSE statement.<\/p>\n\n\n\n
If you need to test a condition and return a value, CASE is the expression you need.<\/p>\n\n\n\n
Introduction<\/a>
Syntax<\/a>
CASE with one condition<\/a>
CASE with multiple conditions<\/a>
Limitations and Gotchas<\/a><\/p><\/blockquote>\n\n\n\n
\n\n\n\nIntroduction<\/h2>\n\n\n\n
In this example, I\u2019m going to load a subset of the top-selling Sega Genesis games dataset<\/a> into SQL Server<\/a>.<\/p>\n\n\n\n
<\/figure><\/div>\n\n\n\n
\n\n\n\nSyntax<\/h2>\n\n\n\n
Each \u2018WHEN\u2019<\/strong> tests to see if a condition has been met. If it has, it returns the \u2018RESULT\u2019<\/strong>, if not it moves on to check the next condition.<\/p>\n\n\n\n
If none of the specified conditions is met it returns what we specify after \u2018ELSE\u2019<\/strong>.<\/p>\n\n\n\n
Every CASE<\/strong> expression needs to finish with \u2018END\u2019<\/strong>.<\/p>\n\n\n\n
An optional extra is to add a column name to our result.<\/p>\n\n\n\n
\n\n\n\n\ncase\n when condition1 then result1\n when condition2 then result2\n when condition3 then result3\n else result end\nas columnname;<\/code><\/pre>\n<\/div><\/div>\n\n\n\nThis makes a lot more sense with examples, so lets get started with our Sega Genesis dataset.<\/p>\n\n\n\n
\n\n\n\nCASE with one condition<\/h2>\n\n\n\n
In our first example, we are using CASE to test one condition. We want to create a new column that shows if a game is from the Sonic series<\/a> or not.<\/p>\n\n\n\n
The last step is to create the column name \u2018Sonic Series\u2019<\/p>\n\n\n\n
select \n game,\n year,\n case \n when game like 'sonic%' then 'Y'\n else 'N'\n end \n as 'Sonic Series'\nfrom \n sega_sales\norder by \n 'Sonic Series' desc<\/code><\/pre>\n\n\n\n
\n\n\n\n<\/figure><\/div>\n\n\n\n
\n\n\n\nCASE with multiple conditions<\/h2>\n\n\n\n
In this example we are using CASE to test multiple conditions. We want to create a new column that shows if Global sales are low, mid or high.<\/p>\n\n\n\n
When the games Global sales are greater than or equal to 4.00 the expression returns \u2018high\u2019<\/strong>, if not it moves on to the next step.<\/p>\n\n\n\n
If the games Global sales are less than or equal to 3.99 and greater than 2.00 the expression returns \u2018mid\u2019<\/strong>.<\/p>\n\n\n\n
If the games Global sales do not meet either of these conditions the expression returns \u2018low\u2019<\/strong>.<\/p>\n\n\n\n
The last step is to create the column name \u2018Sales Range\u2019<\/p>\n\n\n\n
select\n game, \n global, \n case \n when global >= 4.00 then 'high' \n when global > 2.00 and global <= 3.99 then 'mid' \n else 'low' \n end\n as 'Sales Range' \nfrom\n sega_sales<\/pre>\n\n\n\n
\n\n\n\n<\/figure><\/div>\n\n\n\n
\n\n\n\nLimitations and Gotchas<\/h2>\n\n\n\n
The CASE Expression is powerful and makes evaluating conditions simple. However, there are some limitations to be aware of:<\/p>\n\n\n\n
- CASE Expressions cannot check for NULLs.<\/li><\/ul>\n\n\n\n
- The expression works sequentially in the order specified so when a condition is satisfied it will stop.<\/li><\/ul>\n\n\n\n
Having said that, the CASE expression is flexible and can be used in ORDER BY<\/strong>, views, aggregates<\/a> with HAVING<\/strong> and to UPDATE data. If you need to perform IF-ELSE conditions in SQL<\/a>, give it a try.<\/p>\n\n\n\n
\n\n\n\nPhoto by Nidhi Tokas Dahiya<\/a> from Pexels<\/p>\n","protected":false},"excerpt":{"rendered":"