SQL tutorial •
Lesson
12
,
Creating Columns

Using CASE WHEN as IF statements in SQL

You’ve most likely used the <span class="code">IF</span> function in Excel or Google Sheets to define what should happen if certain conditions are fulfilled. In SQL the <span class="code">CASE</span> statement functions the same way. The function is split into a few parts:

  • <span class="code">CASE</span> <span class="code">WHEN</span> This describes the logic you want to test. In this logic, you can use the same functions as you did earlier when filtering the rows: <span class="code">=</span>, <span class="code">!=</span>, <span class="code"><</span>, <span class="code">></span>, <span class="code">LIKE</span>, and <span class="code">NOT</span>
  • <span class="code">THEN</span> This part describes what should happen if the condition is fulfilled
  • <span class="code">ELSE</span> This describes what happens if the condition is not fulfilled
  • <span class="code">END</span> This indicates that the statement is ended

For example, say you’d like to create a new column that checks whether the product is a bread product. This could be done in a query like this:

Notice here that even though the <span class="code">CASE WHEN</span> statement spans over several lines in the query, it’s only creating one new column in the table. Try doing it on your own. You should get a result like this:

You can combine as many <span class="code">WHEN</span> and <span class="code">THEN</span> statements as you like. Just be sure to put them before the <span class="code">ELSE</span>. For example, say that you want the cookie dough from the result to be included in the Bread category, too. You can add that to the query like this:

As you see here, <span class="code">CASE</span> is only written once, and then <span class="code">WHEN</span> and <span class="code">THEN</span> is repeated for each test. Remember that your column names are not supposed to include spaces. The result should look like this:

Exercise 7: Adding a unit to the quantity if the product is bread or cheese

Say your e-commerce business wants to upsell cheese and bread. You want to see how much you would have sold historically if you could add one more unit to the quantity of each order line for bread and cheese products. Of course, this would need to be a more sophisticated analysis to be reliable, but for now, try to write a query that calculates a new total value that adds one more quantity if the product is bread or cheese. If you run your query, it should give a result like this:

In SQL there are often many means to an end, so you’ll usually be able to get the required result by writing different variations of the same query. A simple way to solve the exercise above would be to write a query like this:

Next up:
Introduction to data aggregation