Sql Server Case Statement when IS NULL

sql

case-statement

sql-server

data-type-consistency

byNikita Barsukov·Oct 6, 2024

To deal with NULL values in SQL Server, use the CASE WHEN IS NULL construct as shown below:

SELECT 
  CASE 
    WHEN YourColumn IS NULL THEN 'DefaultValue' 
    ELSE YourColumn 
  END AS AliasColumn
FROM YourTable;

Here, if YourColumn is NULL, it would return a 'DefaultValue', providing a safety-net against null-related mishaps.

Enhancing the CASE Statement

Harmonizing data types

Assuring data type consistency in CASE constructs is crucial:

SELECT 
  CASE 
    WHEN DateColumn IS NULL THEN DATEADD(day, 10, GETDATE()) -- Null Birthday? Happy belated 10-day anniversary!
    ELSE DateColumn 
  END AS UpdatedDate 
FROM YourTable;

In the above example, type consistency for UpdatedDate is maintained regardless of DateColumn being NULL or not. Let's call it a party against type incompatibility! 🎉

Filling NULL holes with calculations

CASE can brilliantly fill the NULL voids based on certain conditions, like so:

SELECT 
  ID,
  CASE 
    WHEN DateColumn IS NULL THEN DATEADD(day, 5, AnotherTable.DateField) -- Adds 5 days to date from another universe (table)!
    ELSE DateColumn 
  END AS CalculatedDate 
FROM YourTable
JOIN AnotherTable ON YourTable.ForeignKey = AnotherTable.PrimaryKey;

This peapod replaces NULL with a date plucked from AnotherTable and adds days. How refreshing is that!

NULL replacements with functions

ISNULL() might be your go-to function for straightforward NULL replacements:

SELECT 
  ISNULL(DateColumn, 'DefaultValue') AS SafeDateColumn -- NULL guard activated!
FROM YourTable;

This is as concise as it can get for a quick NULL check, particularly useful in date calculations operations.

Advanced CASE handling and optimizations

The IIF simplicity

SQL Server 2012 onwards, IIF() brings a more legible alternative for simple CASE expressions:

SELECT 
  IIF(DateColumn IS NULL, 'DefaultValue', DateColumn) AS SimpleDateColumn -- NULL armor loading...
FROM YourTable;

While it's definitely simpler, remember it's not supported in versions prior to SQL Server 2012.

Avoiding duplicates in multi-table scenarios

When working with multiple tables, the command DISTINCT comes real handy to prevent duplicates:

SELECT DISTINCT
  ID,
  CASE 
    WHEN t1.DateColumn IS NULL THEN DATEADD(day, 10, t2.DateField) -- 10 extra days, because why not!
    ELSE t1.DateColumn 
  END AS UniqueDate
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ForeignKey = t2.PrimaryKey;

This assures a unique result set, maintaining consistency at every stage—data types and formats alike.

Large datasets? Keep this in mind!

When crunching large datasets, always assess the performance. Complex CASE logic in your WHERE clause might bloat computation time. Thus, shrewd use of indexes and temporary tables can optimize performance. Remember, efficiency is key.

The importance of code readability

In complex queries, often CASE is chosen over IIF simply due to its versatility and clarity. Don't compromise readability for brevity—a bunch of readable lines of codes never hurt anyone!

An additional ELSE for those edge cases

Adding an ELSE clause is something that you should consider to deal with non-NULL conditions more effectively:

SELECT 
  CASE 
    WHEN DateColumn IS NULL THEN 'No Date' -- Ghost date!
    ELSE CONVERT(VARCHAR, DateColumn, 101) 
  END AS FormattedDate 
FROM YourTable;