Handling NULL In SQL Server

What Is NULL ?

NULL means no result or unknown

NULL is not equal to NULL itself

A value of NULL is different from an empty or zero value

NULL is the smallest value in the sorting order.

NULL are considered to be equal when the group by is executed. If a column in the Group By clause contains rows with NULL, then these will be grouped into one group.

/*Select All value where COL1 contain NULL*/

SELECT *

FROM Table1 WHERE COL1 IS NULL

/*Select All value where COL1 NOT contain NULL*/

SELECT *

FROM Table1 WHERE COL1 IS NOT NULL

/*If the Value of COL1 is NULL It return 0 */

SELECT ISNULL(COL1, 0)

FROM Table1

/*

If the value of @x is zeero

then NULLIF retrun NULL Division By zero error handled

*/

DECLARE @x INT = 0

SELECT 10/NULLIF(@X,0)

/*

If value of COL1 is null then take the value of COL2

If value of COL1 and COL2 then take the value of COL3

Note : Not possible to use Varchar column and integer column together in COALESCE

*/

SELECT COALESCE(COL1, COL2, COL3)

FROM Table1

Difference Between Count(*), Count(Column)

/*Return Entair Table Row Count */

SELECT

COUNT(*)

FROM Table2

/*Return only the Count of COL1 with NOT NULL */

SELECT

COUNT(COL1)

FROM Table2