NULL Values – How to filter out NULLS and how to find NULLS using SQL

Have you ever wanted to find a NULL value or filter out NULL values within a column?

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.

Leave a comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: