You are here
Home > Sql Server > Handling NULLs in sql

Handling NULLs in sql

Handling NULLs

Handling NULLs

  • ISNULL() and COALESCE() functions are used to handle NULLs when combining data from different tables.
  • ISNULL() and COALESCE() functions are commonly used functions that return first value that is not NULL among their inputs.

ISNULL():

  • The ISNULL() function return first value that is not null among their input.
  • It is a proprietary T-SQL feature.
  • Syntax:
  •             ISNULL (columnname, replacement_value)
  • It returns column name if it is not null otherwise it returns replacement value if it is null.

COALESCE():

  • The COALESCE() function returns the first non-null value among its parameter.
  • It is defined by the ISO/ANSI SQL standard.
  • It has no limit for number of arguments but they must all have the same data type.
  • Syntax:
  • COALESCE (column1, column2, …., columnN)

Difference between ISNULL and COALESCE:

Aspect

ISNULL COALESCE
Number of argument 2

>2

Standard

No Yes
Might execute subquery more than once No

Yes

Result type

If both inputs are untyped null literals then result type is INT

If all inputs are untyped null literals, you get an error

 

NULLIF():

  • NULLIF() function is used to checked whether both columns have NULL value or not.
  • Syntax:
  • NULLIF (expression1, expression2)
  • It returns the first expression if the two expressions are not equal.
  • Example:
  • Database Table: Demo

     

    Id

    Name Email Phoneno Language
    1 Siya siya@gmail.com NULL

    English

    2

    Piya NULL 224466 Hindi
    3 Tiya NULL 123456

    Gujarati

 

  • Retrieve name from Demo table if they have email.
  • select Id, ISNULL(Email, ‘abc@gmail.com’) as Email from Demo;


    output:

     

    Id

    Email
    1

    siya@gmail.com

    2

    abc@gmail.com
    3

    abc@gmail.com

 

  • Retrieve name and language from Demo table.
  • select Name, Language, NULLIF(Language, ‘ English ‘)as FavLanguage from Demo;

 

 

  • output:

    Name

    Language FavLanguage
    Siya English

    NULL

    Piya

    Hindi Hindi
    Tiya Gujarati

    Gujarati

 

  • COALESCE() Example:
  • select Name, COALESCE(Email, Phoneno)as Detail from Demo;
  • output:

    Name

    Detail
    Siya

    siya@gmail.com

    Piya

    224466
    Tiya

    123456

 

 

 

Keep visiting my site ask2tech.com for upcoming top technical article

Learn more about DBA at TechNet Consultancy

Leave a Reply

Top