Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Wednesday, 6 February 2013

Coalesce and Nullif example




COALESCE:

If expression is NOT NULL then put the left expression here hourly_wage, if NULL then put the right value eg: here zero '0'
 

NULLIF:

It compares the two expression is equal or not if equal put null if not equal then put left expression here hourly_wage


Script:

CREATE  TABLE #wages
(
    emp_id        tinyint   identity,
    hourly_wage   decimal   NULL,
    salary        decimal   NULL,
    );
GO
INSERT dbo.#wages (hourly_wage, salary)
   select  10.00, NULL
union
   select 20.00, NULL
union
   select NULL, 20000.00
union
   select NULL, NULL

SELECT CAST(COALESCE(hourly_wage,0 ) AS int) AS 'coalses Salary Example',
nullif(hourly_wage,salary)  'null Salary Example',* FROM dbo.#wages

drop table #wages