ISNULL(), NVL(), IFNULL() and COALESCE() Functions

ISNULL(), NVL(), IFNULL() and COALESCE() Functions

ALL THESE FUNCTION ARE USED TO TREAT NULL VALUES in database with some valid value or simply 0(ZERO) as according to conditions....

 

 

So let see how to use them all ..........

TABLE STRUCTURE:TEST TABLE

TESTID    TESTNAME

1                         PIYUSH

2                         RANA

   3

  4

  5              SCOTT 

SQL Server / MS Access

SELECT ISNULL(TESTNAME,0))
FROM TEST

Oracle

Oracle does not have an ISNULL() function. However, we can use the NVL() function to achieve the same result:

SELECT ISNULL(TESTNAME,0)) FROM TESTor we can use the COALESCE() function, like this:

MySQL

MySQL does have an ISNULL() function.

 However, it works a little bit different from Microsoft's ISNULL() function.

In MySQL we can use the IFNULL() function, like this:

SELECT IFNULL(TESTNAME,0))
FROM TEST

or we can use the COALESCE() function, like this:

SELECT COALESCE(TESTNAME,0))
FROM TEST

ALL THE ABOVE QUERY WILL SHOW THE SAME RESULT AS:

TESTID    TESTNAME

1                         PIYUSH

2                         RANA

   3                 0

  4                  0

  5              SCOTT 

 

Hence instead of 0 you can show any data according to need.

Isn't it easy and usefull...so ENJOY.......................