The NULL value always makes some problem to database developer. To handle the null value properly the SQL server provides two functions:
2. COALESCE ()
Both functions act like same.
Before finding the difference between two functions we must understand, what is the functionality of both functions to take care of null values.
Suppose we have a Table names TBL-A
To handle the NULL value with ISNULL()
SELECT ISNULL(StudentName,'NOT FOUND') StudentName
It returns "NOT FOUND" in studentname columns for roll=3
The syntax of the COALESCE is mentioned bellow
COALESCE ( expression [ ,...n ] )
Unlike ISNULL, COALESCE takes multiple columns as expression. To understand it properly I give an example. Please study the example well and you will understand the COALESCE
CREATE TABLE dbo.wages
emp_id tinyint identity,
hourly_wage decimal NULL,
salary decimal NULL,
commission decimal NULL,
num_sales tinyint NULL
(hourly_wage, salary, commission, num_sales)
(10.00, NULL, NULL, NULL),
(20.00, NULL, NULL, NULL),
(30.00, NULL, NULL, NULL),
(40.00, NULL, NULL, NULL),
(NULL, 10000.00, NULL, NULL),
(NULL, 20000.00, NULL, NULL),
(NULL, 30000.00, NULL, NULL),
(NULL, 40000.00, NULL, NULL),
(NULL, NULL, 15000, 3),
(NULL, NULL, 25000, 2),
(NULL, NULL, 20000, 6),
(NULL, NULL, 14000, 4);
SELECT CAST(COALESCE(hourly_wage * 40 * 52,
commission * num_sales) AS money) AS 'Total Salary'
ORDER BY 'Total Salary';
The Output is mentioned bellow
Now what are the differences between them?
SELECT ISNULL(ISNULL(Col1, Col2), Col3)
3. In this SQL the ISNULL can provide some Error
DECLARE @Field1 char(3),
SET @Field2 = 'Some Long String'
SELECT ISNULL(@Field1, @Field2)
SELECT COALESCE(@Field1, @Field2)
--Returns 'Some Long String'
4. Performance wise ISNULL and COALESCE are equal. It is found that the ISNULL is slightly better performer then COALESCE.
In my advice use COALESCES rather than ISNULL.
Hope you like this.
Posted by: MR. JOYDEEP DAS