SQL Handling Null Values

Having Null Values in your database is a good thing despite those that might tell you otherwsie. Not only does it save space but it is a definitive “im empty” signature. There are of course proper and improper ways to handle SQL Nulls. Just remember never ever try to compare to a null using “=”.

1. A NULL value represents an UNKNOWN value

2. Under ANSI_NULLS ON, two NULL values will never be equal to each other because the two separate values are unknown. With ANSI_NULLS OFF, two separate NULL values will evaluate to equal values.

Any time a NULL value is present in a column that an aggregation is performed on, such as a COUNT, AVG, or SUM function, those values will be ignored and therefore not included in the functions result.

The COALESCE function returns the first non-NULL value in the field list that it accepts. The COALESCE function is very useful in a join operation or in queries where you are comparing a single value from a list of possible fields to a single value.

Leave a Reply

Your email address will not be published. Required fields are marked *