Hey there! Today, we’re gonna chat about something pretty cool – the CASE statement and ISNULL function in SQL. These two pals can help us out with a bunch of logic stuff in our code, and they’re super handy when we need to swap out values (either fixed or specified ones) in an expression.
The CASE statement in SQL is kinda like using the IF ELSE statement, but it’s more like the SWITCH statement that we use in DAX. Now, the ISNULL function – that’s for switching out a NULL value with a specific value of your choice.
Using the CASE Statement in SQL
So, imagine we have the ProductName and Sales Amount columns. Now, we want to add another column named Description. And we want this new column to display ‘Cheap’ if the Sales Amount is less than or equal to 5, ‘Not so cheap’ if it’s between 6 and 100, and ‘Expensive’ if it’s more than a hundred.
We use the SELECT statement to pick the column we want to look at. Then we use the CASE statement to lay out the logic we just talked about. With the CASE statement, we’ll always use the WHEN and THEN statements to specify our logic. If none of our conditions are met, the ELSE statement sets a default value. And at the end of our CASE statement, we use END AS and put the column name next to it. This creates a new column, named Description, with values based on the logic we added.
Using the ISNULL SQL Function
The ISNULL function checks if there are any NULL values in a selected column and replaces them with a value of our choice. Let’s say we have some columns with NULL values. We can use ISNULL to replace all of those NULLs with ‘Unknown’.
You start with the SELECT statement to pick the MiddleName column. Then you use ISNULL to check the MiddleName column and replace any NULL value in it with ‘Unknown’. The AS function creates a new column, which we’ll call MiddleName_New.
Remember, ISNULL only replaces NULL values if a certain value is not equal to NULL. It’ll then return the same value from the column that you specified in the ISNULL function.
CASE Statement and ISNULL Function in SSMS
Next up, let’s use the CASE statement and ISNULL functions in SSMS to see them in action.
Using the CASE Statement in SSMS
We’ll start by selecting the FirstName column from Person.Person. We’re going to create a column to display if the first name is ‘Kim’ or not using the CASE statement.
We use the CASE statement to add a condition – if the FirstName is ‘Kim’, it displays ‘Yes’, if not, it displays ‘No’. We also create a new column named ‘IsKim’ using the END AS function.
Let’s look at another example using the CASE statement on the TaxAmt column from Sales.SalesOrderHeader. We apply conditions to the TaxAmt column with the CASE statement, setting values based on if the TaxAmt is less or equal to 500, less or equal to 2000, or if neither condition is met. At the end of our statement, we create a new column named TaxClass.
Using the ISNULL Function in SSMS
In this example, let’s replace NULL values under the CurrencyRateID column. After selecting the table, we can see the NULL values under the CurrencyRateID column. We want to replace these NULL values with ‘1’.
To change the NULL values, we need to select the column that has the NULL values (CurrencyRateID). Then we use ISNULL to select the column that will be checked (also CurrencyRateID). We declare the replacement value (in this case ‘1’), and then we use the AS function to create a new column named NewRate.
Now all the NULL values are replaced by ‘1’ under the NewRate column, and the values that weren’t NULL stay the same. You can also replace a NULL value with a column value.
Wrapping Up
Alright, that’s a wrap! You’ve learned how to use the CASE statement and ISNULL function in SQL. Remember, you can add as many conditions in a CASE statement as you want (just like a SWITCH or IF ELSE statement), and there are a few different ways to replace a NULL value with the ISNULL function.
Keep on coding!
Best,
Hafiz
