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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
IF OBJECT_ID('SalesHistory')>0 DROP TABLE SalesHistory; GO CREATE TABLE [dbo].[SalesHistory] ( [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [Product] [varchar](10) NULL, [SaleDate] [datetime] NULL, [SalePrice] [money] NULL ) GO DECLARE @i SMALLINT, @Product VARCHAR(10) SET @i = 1 WHILE (@i <=100) BEGIN SET @Product = CASE WHEN @i%2 = 0 THEN 'Computer' ELSE NULL END INSERT INTO SalesHistory(Product, SaleDate, SalePrice) VALUES (@Product, DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57)) INSERT INTO SalesHistory(Product, SaleDate, SalePrice) VALUES('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13)) INSERT INTO SalesHistory(Product, SaleDate, SalePrice) VALUES('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29)) SET @i = @i + 1 END GO SELECT * FROM SalesHistory WHERE Product IS NULL /*returns all the null values which is NULL*/ SELECT * FROM SalesHistory WHERE Product = NULL /*returns no rows, since the NULL value cannot be equal to anyother //value.*/ SELECT * FROM SalesHistory WHERE Product IS NOT NULL SELECT ISNULL(Product, 'Missing') FROM SalesHistory /*For every null value in Product it is replaced by ‘Missing’*/ SELECT Product, COUNT(*) AS ProductCount, SUM(SalePrice) AS ProductSales FROM SalesHistory GROUP BY Product |
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.
|
1 2 |
SELECT COALESCE(Product, ProductDescription, Product, 'Missing Info') FROM SalesHistory |
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.