In this blog, we’re going to discuss the CASE statement that you can use for handling multiple logical operations. We’ll also discuss the ISNULL SQL function that’ll allow you to return a replacement value in case there are expressions that have NULL value.
The CASE statement and ISNULL function are very much helpful in working with codes that require a lot of logical operations and for quick replacement of either fixed or specified values to an expression.
The CASE statement in SQL is like using the IF ELSE statement. However, it’s most likely similar to the SWITCH statement that we use in DAX. On the other hand, the ISNULL function is for replacing a NULL value with a specific value that you want to use.
Using The CASE Statement In SQL
Let’s assume that we have the ProductName and Sales Amount columns.
Then we want to create an additional column named Description.
We also want to add logical statements that’ll display ‘Cheap’ if the Sales Amount is less than or equal to 5, then ‘Not so cheap’ for between 6 and 100. If the value is greater than a hundred, it will display ‘Expensive’.
In this example, we used the SELECT statement to select the column that we want to view. After that, we have the CASE statement that contains the logical operations we mentioned earlier.
In utilizing the CASE statement, the WHEN and THEN statements are always used to indicate the logical operations that we want to implement in the selected column. The ELSE statement is for setting a default value if the logical operations set were not met.
At the end of the CASE statement, we have END AS and the column name next to it. This will create a new column named Description with values based on the logical operations that we’ve added in the CASE statement.
Utilizing The ISNULL SQL Function
The ISNULL function is for checking if there are any NULL values on a selected column and replacing them with any specified value. Now let’s visualize that we have the following columns with NULL values.
In the example above, all of the NULL values are replaced with ‘Unknown’ values. The following lines of code are used to create that example.
In using the ISNULL SQL function, you should start by selecting the MiddleName column using the SELECT statement.
After that, you should use the ISNULL function to check the MiddleName column that’s specified inside the parentheses and replace any NULL value in it with the ‘Unknown’ value.
The AS function is for creating a new column which in this case is the MiddleName_New.
Take note that the ISNULL function will only replace NULL values if a certain value is not equal to NULL. Then it will return the same value from the column that you’ve specified in the ISNULL function.
Next, I’ll use the CASE statement and ISNULL functions in SSMS for you to see how it actually works.
CASE Statement In SSMS
Let’s begin by selecting the FirstName column from Person.Person. Take note that in this example, it’s not necessary to select a column. This is just for your reference.
After bringing up this column, let’s create a column that will display values whether the first name is Kim or not by using the CASE statement. To do that, simply follow the example below.
In the sample code above, we added a condition using the CASE statement where if the FirstName is equal to ‘Kim,’ it will display ‘Yes’ and if not, then it’ll display ‘No’ instead.
For this example, I also created a new column named ‘IsKim’ using the END AS function. See the example below for the result.
Now let’s have another example using the CASE statement by applying some conditions on the TaxAmt column from the Sales.SalesOrderHeader table.
First, let’s bring the top 100 columns from Sales.SalesOrderHeader using the following line of code and look for the TaxAmt column.
After that, let’s start applying conditions for the TaxAmt column with the CASE statement using the example below.
Similar to the previous example, we started by selecting the column that we want to apply our conditions to – in this case, it’s the TaxAmt column.
Then in the CASE statement, we added conditions where if the TaxAmt is less than or equal to 500, then it will display the value ‘Perfect.’ If the TaxAmt is less than or equal to 2000, then the value ‘Okay’ will be displayed.
Additionally, if the conditions were not met, the ‘Not Good’ value will be displayed. At the end of the statement, I created a new column named TaxClass.
That’s how we utilize the CASE statement in SQL. Take note that you can set as many conditions as you like. Just make sure to use the WHEN and THEN statements as well as the END AS function to generate a new column where the results will be displayed.
ISNULL SQL Function In SSMS
In this example, I’m going to use the same table from the previous example. Then I’ll demonstrate how you can use this function to replace NULL values under the CurrencyRateID column.
After selecting the table from the example above, we can see the NULL values under the CurrencyRateID column. Now I want to replace these NULL values with ‘1.’ To do this, refer to the example below.
To change the NULL values in a specific column, first we need to select the column that has the NULL values. In this case, it’s CurrencyRateID. Then we’ll use the ISNULL function and select the column that will be checked by the ISNULL function, which is the CurrencyRateID.
We also need to declare the replacement value that we want to use – for this example I used ‘1’. Lastly, we’ll add the AS function to create a new column named NewRate.
As a result, all the NULL values were replaced by ‘1’ under the NewRate column, and those that didn’t have a NULL value remained the same. In addition, you can also change a NULL value with a column. You can do this by following the example below.
Using the same syntax from the previous example, we just added the SalesOrderID next to CurrencyRateID as our reference before the ISNULL function. Then we changed the replacement value with SalesOrderID. This statement will simply check the NULL values under CurrencyRateID and will replace them with the values from the SalesOrderID column.
***** Related Links *****
Common SQL Table Expressions
SQL Server And SSMS Download And Installation
SQL Data Extraction Using OFFSET And FETCH
Conclusion
In summary, you’ve learned how to use the CASE statement and ISNULL function along with the proper syntax in SQL. You also learned that you can include as many logical conditions in a CASE statement as you want. Just take note that the CASE statement is similar to SWITCH or IF ELSE statement.
In addition to the ISNULL function, you learned that there are different ways to replace a NULL value in utilizing this function, which is by means of using a fixed value or a column.
All the best,
Hafiz
