NULL value is an unknown and missing value. You cannot define a value for NULLs.
As NULL is a missing unknown value, you cannot compare 2 NULL values.
To check if a particular column has NULL Value, you have to use the ‘IS NULL ’ operator.
For Example, suppose you need to get the products which does not have a defined color or which contains a missing unknown value, then you can use IS NULL operator –
SELECT ProductID, Name, Color FROM Production.Product WHERE Color IS NULL;
IS NOT NULL
Similarly, we can also test if a particular field does not contain NULL values using ‘NOT NULL’ operator.
SELECT ProductID, Name, Color FROM Production.Product WHERE Color IS NOT NULL;