sql-serverif-statementcase-when

Difference between if and when statements in SQL

If I have a table with a number column and want to create a new column with values of either 'high', 'medium' or 'low' based on the value in the number column can I use an if/else statement to do this? If so how would this be written?

I have this code for creating the column using a when/then statement:

use master

select 
    *, 
    case
       when (number > 40) then 'high'
       when (number between 30 and 40) then 'medium'
       else 'low'
    end as 'newColumn'
from 
    myTable

Basically I'm trying to understand the difference between if/else and when/then statements and why one would be used over the other.

This if/else code doesn't work but I don't know if it's because I'm coding it incorrectly:

use master

select 
    *,
    if (number > 40)
      'high'
    else if (number between 30 and 40)
      'medium'
    else 'low'
from 
    test
Solution

  • IF statement controls the flow and decides what has to be evaluated next, based on the condition, while CASE is a function which returns the desired value.

    The CASE statement is more readable than IF statement when you compare a single expression against a range of unique values and more efficient as well.