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


No comments:

Post a comment