Difference between null and coalesce

ISNULL

ISNULL function is used to replace the NULL value with specified value.
It contains only two arguments.
Same data type not compulsory.

Example -1:

SELECT ISNULL(NULL,'Raki') AS [ISNULL]

Output: Raki

Example -2:

DECLARE @name VARCHAR(10)

DECLARE @marks INT =500

SELECT ISNULL(@name,@marks) AS [ISNULL]

Output: 500

Example-3:

SELECT ISNULL(NULL,NULL,'Raki') AS [ISNULL]

Output:

Msg 174, Level 15, State 1,

The isnull function requires 2 argument(s).

COALESCE

Coalesce function is returns first non null value among arguments.
It contains multiple arguments.
Same data type compulsory for arguments or precedence data type order should follow.

Example-1:

SELECT COALESCE(NULL,NULL,'Raki') as [COALESCE]

Output: Raki

Example-2:

DECLARE @name VARCHAR(5)='Raki'

DECLARE @marks INT =500

SELECT COALESCE(@name,@marks) as [COALESCE]

Output:

Msg 245, Level 16, State 1, Line 3

Conversion failed when converting the varchar value 'Raki' to data type int.

Comments

Coalesce function

Coalesce function returns the first non-null value among the arguments. Syntax: Coalesce (expression [,..n]) Here is example using Coalesce function Example 1 DECLARE @Str1 varchar ( 10 ), @str2 varchar ( 20 ), @Str3 varchar ( 20 ) SET @Str2 = 'Sql' , @Str3 = 'Server' SELECT COALESCE ( @Str1 , @str2 , @Str3 ) As [Coalesce] In above example @Str2 value is ‘Sql’ , @str3 value is ‘Server’ and @str1 values is Null because it not assigned any value . Output: It return’s “Sql” because Coalesce function return’s first non null value. Example 2: Coalesce in select statement. IF OBJECT_ID ( 'Employee' , 'U' ) IS NOT NULL DROP TABLE Employee CREATE TABLE Employee ( ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY , NAME VARCHAR ( 20 ), SALARY INT ) INSERT INTO Employee ( NAME , SALARY ) VALUES ( 'Rakesh' , 5000 ),(NULL, 6000 ),( 'Naresh...

Variables in T-SQL

Variables can be used to store the data in temporally based on data type. Variable name begin with @ symbol. There are two types of variables in T-SQL. 1. Local Variables (It must begin @ symbol). 2. Global Variables (it must begin @@ symbol) this variables also known as system variables. Syntax Declare a Variable DECLARE @Variable-Name DATATYPE EXAMPLE: DECLARE @Name VARCHAR ( 100 ) In before 2008 versions of SQL SERVER we declare a variable and assign a value in 2 lines. DECLARE @Name VARCHAR ( 100 ) SET @Name = 'LeadFirstSQL' In 2008 and Later versions of SQL SERVER we declare a variable and assign a value in single line. DECLARE @Name VARCHAR ( 100 ) = 'LeadFirstSQL' Variables must be declare and used with the same batch. DECLARE @Name VARCHAR ( 100 ) ...

CONCAT FUNCTION IN SQL SERVER 2012

Concat function is used concatnating the values among the different arguments. Syntax: Concat([String1],[String2]..[StringN]); DECLARE @FName VARCHAR ( 50 ), @LName VARCHAR ( 50 ); SET @FName = 'Rakesh' ; SET @LName = 'Kalluri' ; SELECT CONCAT ( @FName , ' ' , @LName ) as [FullName] ; Output: Rakesh Kalluri --Before SQL 2012 Versions we can use like this way DECLARE @FName VARCHAR ( 50 ), @LName VARCHAR ( 50 ); SET @FName = 'Rakesh' ; SET @LName = 'Kalluri' ; SELECT @FName + ' ' + @LName as [FullName] ; Output: Rakesh Kalluri --But Before SQL 2012 Versions there are 2 problems when we contacting . --1.Need to replace NULL Value to Empty.if we directly concat NULL Value the Entire result also NULL. --Before 2012: DECLARE @FName VARCHAR ( 50 ), @LName VARCHAR ( 50 ); SET @FName = 'Rakesh' ; SELECT @FName + ' ' + @LName a...

Tech Learn

Search This Blog