Beyond IS NULL and COALESCE: Alternative Methods for NULL Handling in T-SQL

• NULL represents the absence of a defined value in a database column. It's not the same as zero, an empty string, or any other specific value.

"Not equal" Operator Behavior with NULL

• When you use <> or != to compare a column or expression with NULL, the result is not as straightforward as comparing two non-NULL values.
• Neither TRUE nor FALSE: These operators cannot definitively determine if a value is not equal to NULL because NULL signifies the lack of a value for comparison.
• Returns NULL: The expression itself evaluates to NULL. This is because the comparison between a value and the absence of a value is indeterminate.

Example:

SELECT * FROM Customers WHERE City <> 'New York';

• This query retrieves customers whose City column is not equal to 'New York'.
• However, it won't include rows where City is NULL.

Handling NULL in "Not equal" Comparisons

To achieve the desired outcome when dealing with NULL values, you have two main options in T-SQL:

1. IS NULL Operator:

   • SELECT * FROM Customers WHERE City <> 'New York' AND City IS NOT NULL;
     

2. COALESCE Function (Optional):

   • SELECT * FROM Customers WHERE City <> 'New York' OR COALESCE(City, '') = '';
     

     • This query retrieves customers whose City column is either not equal to 'New York' or is empty (assuming an empty string as the default value).

Choosing the Right Approach

The best approach depends on your specific requirements:

• If you only want to exclude rows with NULL in the column, use IS NULL.
• If you want a specific default behavior when encountering NULL, use COALESCE.

Key Points:

• <> and != don't directly handle NULL comparisons.
• Use IS NULL or COALESCE for desired NULL handling in T-SQL.
• Consider your query's intent when choosing the appropriate method.

-- Select customers from 'Customers' table where City is not equal to 'New York'
-- and City is not NULL (exclude NULL values)
SELECT *
FROM Customers
WHERE City <> 'New York'
  AND City IS NOT NULL;

Using COALESCE for Specific Default Behavior:

-- Select customers from 'Customers' table where City is either not equal to
-- 'New York' or is empty (assuming an empty string as the default value)
SELECT *
FROM Customers
WHERE City <> 'New York'
  OR COALESCE(City, '') = '';

Variations with COALESCE:

• You can use any value you want as the default in COALESCE. For example, to use "Unknown" as the default:

SELECT *
FROM Customers
WHERE City <> 'New York'
  OR COALESCE(City, 'Unknown') = 'Unknown';

• You can combine COALESCE with other operators for more complex logic:

-- Select customers where City is either not equal to 'New York' or
-- starts with 'Los' (assuming NULL values don't start with 'Los')
SELECT *
FROM Customers
WHERE City <> 'New York'
  OR COALESCE(City, '') LIKE 'Los%';

• The CASE WHEN statement allows you to define conditional logic for handling different scenarios within your query.
• You can create a case for NULL values and return a specific result for that case.

SELECT *
FROM Customers
WHERE CASE WHEN City IS NULL THEN 'NULL' -- Replace 'NULL' with desired output for NULL
             WHEN City <> 'New York' THEN City
             ELSE NULL -- Optional: Handle unexpected values (can be omitted)
        END <> 'New York';

This example:

• Checks if City is NULL and returns 'NULL' (you can replace this with your preferred output).
• If City is not NULL, it checks if it's different from 'New York' and returns the actual value.
• Optionally, you can add an ELSE NULL clause to handle unexpected values (though it might be unnecessary in most cases).

EXISTS Subquery (Advanced):

• This method is more complex but allows for more intricate filtering based on relationships between tables.
• You can use an EXISTS subquery to check if there are related rows where a specific condition is met, excluding NULL values in the main query.

Example (assuming a related table Orders with a City column):

SELECT *
FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID AND o.City <> 'New York')
  AND c.City IS NOT NULL;

• Uses an EXISTS subquery to check if there are rows in the Orders table where CustomerID matches the current customer (c.CustomerID) and the City in the Orders table is not equal to 'New York'.
• The main query filters for customers where the subquery returns TRUE (indicating a related order with a non-'New York' city) and City in the Customers table is not NULL.

• For basic null exclusion or assigning a default value, IS NULL and COALESCE are generally preferred due to their simplicity and readability.
• Consider using CASE WHEN if you need more complex logic within the comparison or want to handle different scenarios for NULL and non-NULL values.
• Use EXISTS subqueries cautiously, primarily for advanced filtering based on relationships between tables, as they can be less efficient and harder to understand.