NULL Values — How to filter out NULLS and how to find NULLS using SQL — Parv The IT Geek

Well SQL has the solution and our saviour is the ‘IS NULL’ and ‘IS NOT NULL’ commands. Using them in a ‘WHERE’ clause will allow us to do the filtering out appropriately.

USE AdventureWorks 
GO 
SELECT COUNT(*) 
FROM [AdventureWorks].[Person].[Address]

The above query bring us a result of 19,614 rows contained within the AddressLine2 where no values have been filtered.

USE AdventureWorks 
GO 
SELECT COUNT(*) 
FROM [AdventureWorks].[Person].[Address] 
WHERE AddressLine2 IS NULL

Using the ‘IS NULL’ clause we find that there are 19,252 rows where AddressLine2 has a Null value.

USE AdventureWorks 
GO 
SELECT COUNT(*) 
FROM [AdventureWorks].[Person].[Address] 
WHERE AddressLine2 IS NOT NULL

Using the ‘IS NOT NULL’ clause we find that there are 362 rows where AddressLine2 has a value which does not contain a Null.

A quick check shows total count of ‘IS Null’ ( 19,252) + total count of ‘IS NOT NULL’(362) equal 19,614 which shows that we have returned the correct figures and have not been tripped up by any kind of data anomalies.

Originally published at https://parvtheitgeek.com on January 22, 2014.