SQL Case Statement Examples – Replacing If-Else

Although some databases do support the IF ELSE functionality, the syntax can vary. The CASE statement can be used as a replacement of IF and is recognized by almost all databases. This is what makes the CASE statement particularly popular since the code is easily transferable.

We can look at the standard syntax of a sql CASE statement first and then apply it to practical examples.

CASE — Begin case statement
WHEN <condition_1> THEN output_value_1
WHEN <condition_n…> THEN output_value_n
ELSE default_output
END — End of case statement

We can apply as many conditions as necessary to fill out our CASE statement. Similarly, the ELSE functionality is an optional function and does not need to be included. All CASE statements must use END to close out the statement otherwise the sql syntax will likely error out.

Using A CASE Statement In A Query

As mentioned, if you’re familiar with If-Else then this works in a similar way albeit slightly different syntax.

Personally, I prefer to always encapsulate CASE statements with parentheses and add a field name. Parentheses are not necessary and everyone has their own preferences.

Related: Using Parentheses – Order of Operations

In the example above, we can see that we have created new groups for our invoices based on the invoice size. This can be helpful when doing general reporting and analysis.

Using CASE To Translate Codes

Similarly, we can apply the CASE statement to do more generic code changes such as abbreviations. In the following example we translate a state code into the actual state name. This isn’t the best example and would be better suited to store this data in a table. However, it is often done like this.

Using CASE To Default Other Fields When NULL

As a last example, we can use the CASE statement to default a field to another value when blank.

We can see the resulting output fills in any NULL fields with our CityName. Fields like this often result from a clients needs when defining their own set of business rules in their data.

We can see how using a sql case statement proves useful in the above examples. Whether it’s defining certain groupings, translating codes, or just defaulting values – the CASE statement can accomplish this.