ISNULL , COALESCE in SQLSERVER, NVL in ORACLE, IFNULL() in MYSQL

as a developer and writing querys you offtn encounter in a situation where you want to replace a NULL in your result every RDBMS has its own system function to handle this today we will go through these functions in detail

We will start with SQL Server

In sql Server we have to functions one is ISNULL and other COALESCE

ISNULL is a bult in TSQL function develped by Microsoft , it replaces null value with scpecified value

ISNULL(Check expression (Column/variable/value), replacement expression(Column/Variable/value))

The replacement expression should be impilictly convertable to check expression data type, SQl server implicitly converts

replacement value to the data type of Check expression and returns the value if check expression is null

If we look at new column’s defnition it is a char datatype

However if you switch our replacement expression with check expression it throws an error saying

Msg 245, Level 16, State 1, Line 7

Conversion failed when converting the varchar value ‘a’ to data type int.

Which is not possible according to sqlserver

Sql server data type conversion chart

http://msdn.microsoft.com/en-us/library/ms191530.aspx

It can only have two input expressions

Where as if we look at COALESCE it takes multiple expressions and returns first not null value

The one big dfference between ISNULL and COALESCE is the return data type,

When using ISNULL if check expression is evaluated to NULL and return expression is NOT NULL and is different data type that of check expression SQL Server implictly converts return expression to the data type of check expressions

Where as COALESCE well it returns the first not null value but the data type it returns depends on the SQL Server data type precedence

http://msdn.microsoft.com/en-us/library/ms190309.aspx

Example:

SQL server data type precedence

http://msdn.microsoft.com/en-us/library/ms190309.aspx

about COALESCE

http://msdn.microsoft.com/en-us/library/ms190349(v=sql.105).aspx

NVL equavalent to ISNULL in SQLSERVER

NVL in ORACLE

NVL in Oracle works excatly as ISNULL in sql server

NVL (exp1 , exp2)

Exp1 is check expression and Exp2 is a replacement value

if Exp1 is NULL it returns Exp2 else it returns Exp1

Exp1 and Exp2 might not be same datatype just like in sql server but it should be implicitly convertble  to the datatype of Exp1

 

But this implicit conversion is done in a smarter way when compared to isnull

  • If expr1 is character data, then Oracle Database converts expr2 to the datatype of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.
  • If expr1 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

Numeric Precedence

Numeric precedence determines, for operations that support numeric datatypes, the datatype Oracle uses if the arguments to the operation have different datatypes. BINARY_DOUBLE has the highest numeric precedence, followed by BINARY_FLOAT, and finally by NUMBER. Therefore, in any operation on multiple numeric values:

  • If any of the operands is BINARY_DOUBLE, then Oracle attempts to convert all the operands implicitly to BINARY_DOUBLE before performing the operation.
  • If none of the operands is BINARY_DOUBLE but any of the operands is BINARY_FLOAT, then Oracle attempts to convert all the operands implicitly to BINARY_FLOAT before performing the operation.
  • Otherwise, Oracle attempts to convert all the operands to NUMBER before performing the operation.

 

 

Table 2-10

 

 

 

Implicit Type Conversion Matrix

 

Table 2-10 Implicit Type Conversion Matrix

CHAR VARCHAR2 NCHAR NVARCHAR2 DATE DATETIME/INTERVAL NUMBER BINARY_FLOAT BINARY_DOUBLE LONG RAW ROWID CLOB BLOB NCLOB
CHAR X X X X X X X X X X X X X
VARCHAR2 X X X X X X X X X X X X X
NCHAR X X X X X X X X X X X X X
NVARCHAR2 X X X X X X X X X X X X X
DATE X X X X
DATETIME/ INTERVAL X X X X X
NUMBER X X X X X X
BINARY_FLOAT X X X X X X
BINARY_DOUBLE X X X X X X
LONG X X X X X X X X
RAW X X X X X X
ROWID X X X
CLOB X X X X X X
BLOB X
NCLOB X X X X X X

Oracle Implicit Conversion Matrix

IFNULLI() in MY SQL

 

IFNULL() is equavalent to NVL() in oracle and ISNULL() in Microsoft Sql server

 

 

Advertisements

12 thoughts on “ISNULL , COALESCE in SQLSERVER, NVL in ORACLE, IFNULL() in MYSQL

  1. san francisco airport taxi says:

    First off I want to say awesome blog! I had
    a quick question in which I’d like to ask if you do not mind. I was interested to find out how you center yourself and clear your head before writing. I have had a hard time clearing my mind in getting my ideas out. I do enjoy writing but it just seems like the first 10 to 15 minutes are usually lost simply just trying to figure out how to begin. Any suggestions or tips? Kudos!

    • akhil393 says:

      Hi

      It all comes to enthusiasam to share knowledge

      If you like this boring and time consuming you always channel in which you share knowledge … Videos ? Pictures ? Audio?… just be creative that will entertain you

  2. big wheel tricycle says:

    Excellent post. Keep posting such kind of information on your blog.
    Im really impressed by it.
    Hi there, You have performed a great job.

    I will certainly digg it and individually recommend to my friends.
    I’m confident they’ll be benefited from this site.

  3. NBA 2K14 Mediafire says:

    Hi, Neat post. Thewre is аn issue ωith your web site іn
    intenet explorer, сould check this? IE nοnetheless iѕ the markdt leader
    and a large element оff other people will leave out your excellent writing ԁue to this problem.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s