Check out the following sample code to understand the basic differences between ISNULL and COALESCE functions in SQL Server. Code is self explanatory and comments are provided for ease of understanding.
/**************************************************************************** ISNULL function replaces NULL with the specified value. COALESCE function returns the first non-NULL value among its arguments. ****************************************************************************/ DECLARE @var VARCHAR(20) SELECT ISNULL(@var, 'Rami Vemula') AS 'ISNULL'; SELECT COALESCE(@var, NULL, 2) AS 'COALESCE'; /**************************************************************************** ISNULL takes only 2 parameters whereas COALESCE takes variable number of parameters. ****************************************************************************/ DECLARE @var1 INT SELECT ISNULL(@var1, 2) AS 'ISNULL1'; SELECT COALESCE(@var1, NULL, 2) AS 'COALESCE1'; /**************************************************************************** ISNULL takes the data type length of the first argument, while COALESCE takes all parameters and uses max preceded parameters's length. ****************************************************************************/ DECLARE @var2 VARCHAR(4) SELECT ISNULL(@var2, 'Rami Vemula') AS 'ISNULL2'; SELECT COALESCE(@var2, 'Rami Vemula') AS 'COALESCE2'; /**************************************************************************** ISNULL uses the first parameter data type. COALESCE uses highest precedence data type. ISNULL and COALESCE throws invalid cast exception when it encounters invalid casts because of precedence. ****************************************************************************/ DECLARE @var3 INT SELECT ISNULL(@var3, 0) AS 'ISNULL3'; -- SELECT ISNULL(@var3, 'Rami Vemula') AS 'ISNULL'; -- Throw Error SELECT COALESCE(@var3, 0, GETDATE()) AS 'COALESCE3'; -- SELECT COALESCE(@var3, 'Rami Vemula', GETDATE()) AS 'COALESCE'; -- Throw Error /**************************************************************************** If the data types of both parameters are not determined in ISNULL, the data type returned is INT. In COALESCE at least one of the NULL values must be a typed NULL or else it will throw error. ****************************************************************************/ DECLARE @var4 INT SELECT ISNULL(NULL, NULL) AS 'ISNULL4'; SELECT COALESCE(CAST(NULL as NVARCHAR), NULL) AS 'COALESCE4'; -- SELECT COALESCE(NULL, NULL) AS 'ISNULL'; -- Throw Error /**************************************************************************** ISNULL is an internal to TSQL Engine. COALESCE is an ANSI standard function. ****************************************************************************/ /**************************************************************************** When it comes to performance both ISNULL and COALESCE are same to a maximum extent. But query execution plan differs for COALESCE because it will get evaluated using a CASE statement. So one needs to be careful with the arguments of COALESCE. ****************************************************************************/
Output –
