What are NULL Values in SQL Server

SQL NULL Values
If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a Null Value.

NULL values are treated differently from other values.
NULL is used as a placeholder for unknown or inapplicable values.
Note: It is not possible to compare NULL and Blank; they are not equivalent.

Working with NULL Values in SQL Server :
Look at the following "Customers" table:

EMP_ID	F_Name	L_Name	Dept_ID	Address	Salary
1001	Ravi	Kumar	1	Pune	20000
1002	David	Smith	2	NULL	35000
1003	Victory	Venkatesh	1	Bangalore	50000
1004	Tamatam	Reddy	3	NULL	25000
1005	William	Smith	2	Pune	40000
1006	King	Fisher	6	Bangalore	30000

Suppose that the "Address" column in the "Customers" table is optional. This means that if we insert a record with no value for the "Address" column, the "Address" column will be saved with a NULL value.

Testing for NULL values:
It is not possible to test for NULL values with comparison operators, such as =, <, or <>.
We will have to use the IS NULL and IS NOT NULL operators instead.

IS NULL
To select only the records with NULL values in the "Address" column.
We will have to use the IS NULL operator:
SELECT Emp_Id,F_Name,L_Name,Address FROM Customers WHERE Address IS NULL
The result-set will look like this:

EMP_ID	F_Name	L_Name	Dept_ID	Address	Salary
1002	David	Smith	2	NULL	35000
1004	Tamatam	Reddy	3	NULL	25000

NOT NULL
We can select only the records with no NULL values in the "Address" column using the NOT NULL operator:
SELECT Emp_Id,F_Name,L_Name,Address FROM Customers
WHERE Address IS NOT NULL

The result-set will look like this:

EMP_ID	F_Name	L_Name	Dept_ID	Address	Salary
1001	Ravi	Kumar	1	Pune	20000
1003	Victory	Venkatesh	1	Bangalore	50000
1005	William	Smith	2	Pune	40000
1006	King	Fisher	6	Bangalore	30000

--------------------------------------------------------------------------------------------------------