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