How to Check or Handle NULL Values in Where Clause in SQL Server

NULL is an interesting value in any relational Database. Sometimes its same (for Grouping), some times not (for equality). In many cases we need to identify records those contains NULL value. Some times we need to discard records if any specific column contains NULL values. So as a developer handling NULL values in WHERE clause is common. Here I will discuss how we can “Handle NULL values in SQL WHERE clause“.

To do the example follow the below table:
Handling Null Values in SQL WHere Clause

Using NULL in WHERE clause incorrectly:

SELECT * FROM tbl_Stock
WHERE QuarentineQuantity=NULL

SELECT * FROM tbl_Stock
WHERE QuarentineQuantity=0

SELECT * FROM tbl_Stock
WHERE QuarentineQuantity<1

Query Output:
Null records filtered out

Observations:
1. If a column contains NULL value then why we can not use “Column Name=NULL”.
2. If we left a column blank then for numeric values why those are not equal to “0”.
3. If we left a column blank then for numeric values why those are not “<1".

Reason:
We knew that if a record satisfy the where condition then it will be selected. NULL is not a specific value its an unknown value (sometimes we can refer garbage value). When you will check NULL with an unknown value then the result will be unknown. In logical terms the value will be false. Since the equality comparison return false in where condition so obviously those records will be filtered out from the query result.

Resolution:
To resolve the above issues you must have to use the “IS NULL” or “IS NOT NULL” operators in order to check for a NULL value.

Example SQL: Checking NULL values in WHERE clause:

SELECT * FROM tbl_Stock
WHERE QuarentineQuantity IS NULL

SELECT * FROM tbl_Stock
WHERE QuarentineQuantity=0 OR QuarentineQuantity IS NULL 

SELECT * FROM tbl_Stock
WHERE ISNULL(QuarentineQuantity,0)=0
-- SQL Server ISNULL Function replaces NULL values
-- Will discuss on ISNULL Function in next articles

SELECT * FROM tbl_Stock
WHERE QuarentineQuantity IS NOT NULL

Sequential Output:
Using ISNULL function where clause

Wait for more articles on SQL Server NULL values.

Leave a Reply

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

AlphaOmega Captcha Classica  –  Enter Security Code