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:
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
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".
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.
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
Wait for more articles on SQL Server NULL values.