An Interest In:
Web News this Week
- April 18, 2024
- April 17, 2024
- April 16, 2024
- April 15, 2024
- April 14, 2024
- April 13, 2024
- April 12, 2024
SQL 201: How to add IF-ELSE logic to SQL queries
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 you need.
Introduction
Syntax
CASE with one condition
CASE with multiple conditions
Limitations and Gotchas
Introduction
In this example, I'm going to load a subset of the top-selling Sega Genesis games dataset into SQL Server.
Syntax
- Each 'WHEN' line tests to see if a condition has been met, if it has it returns the 'RESULT', if not it moves on to check the next condition.
- If none of the specified conditions is met it returns what we specify after 'ELSE'.
- Every CASE expression needs to finish with 'END'.
- An optional extra is to add a column name to our result.
case when <condition1> then <result1> when <condition2> then <result2> when <condition3> then <result3> else <result> endas <columnname>;
This makes a lot more sense with examples, so lets get started with our Sega Genesis dataset.
CASE with one condition
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 or not.
- When the game name contains the string 'Sonic' the expression returns 'Y', if not it returns 'N'.
- The last step is to create the column name 'Sonic Series'
select game, year, case when game like 'sonic%' then 'Y' else 'N' end as 'Sonic Series'from sega_salesorder by 'Sonic Series' desc
CASE with multiple conditions
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.
- When the games Global sales are greater than or equal to 4.00 the expression returns 'high', if not it moves on to the next step.
- If the games Global sales are less than or equal to 3.99 and greater than 2.00 the expression returns 'mid'.
- If the games Global sales do not meet either of these conditions the expression returns 'low'.
- The last step is to create the column name 'Sales Range'
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
Limitations and Gotchas
The CASE Expression is powerful and makes evaluating conditions simple. However, there are some limitations to be aware of:
- In SQL Server there can only be up to 10 levels of nesting.
- CASE Expressions cannot check for NULLs.
- The expression works sequentially in the order specified so when a condition is satisfied it will stop.
Having said that the CASE expression is flexible and can be used in ORDER BY, views, in aggregates with HAVING and to UPDATE data. If you need to perform IF-ELSE conditions in SQL, give it a try.
Read more:
SQL 101: Relational Database Fundamentals
Helen Anderson Sep 1 '18 2 min read
This post originally appeared on helenanderson.co.nz
Photo by Nidhi Tokas Dahiya from Pexels
Original Link: https://dev.to/helenanders26/sql-201-how-to-add-if-else-logic-to-sql-queries-41j
Dev To
An online community for sharing and discovering great ideas, having debates, and making friendsMore About this Source Visit Dev To