MSSQLTips

moot67:

(

   - Are there other uses for Coalesce vs replacement for ISNULL

 This is my first post so pardon the formatting (or lack thereof).

ISNULL evaluates one item and does a substitution of like type if the value is null.   Coalesce allows multiple items to be compared in one statement.

Here is a use that might be hard to do with ISNULL, where you are combining data from several tables:

 

Situation is where you need to combine information based on matches of one column of data in multiple tables.

Table1: memberid initialjob 123          GS 124       PDR   Table2: memberid intermediatejob 123         AAP 125        AV Table3: memberid lastjob 123        TS 126         CS and you need to have all these tables into TABLEFINAL: memberid initialjob  intermediatejob lastjob 123        GS          AAP                TS 124       PDR          NULL               NULL 125       NULL         AV                  NULL 126       NULL         NULL              CS

 

For this you can use Coaelesce to combine the data even if there is no matching row in one or more of the tables:

DECLARE @t1 TABLE (memberid INT,initialjob VARCHAR(50))
DECLARE @t2 TABLE (memberid INT,intermediatejob VARCHAR(50))
DECLARE @t3 TABLE (memberid INT,lastjob VARCHAR(50))

INSERT INTO @t1
SELECT 123,'GS' UNION
SELECT 124,'PDR'

INSERT INTO @t2
SELECT 123,'AAP' UNION
SELECT 125,'AV'

INSERT INTO @t3
SELECT 123,'TS' UNION
SELECT 126,'CS'

set statistics io on
set statistics time on

select coalesce(t1.memberid,t2.memberid,t3.memberid) 'memberid', initialjob, intermediatejob, lastjob
from @t1 t1
full outer join @t2 t2 on t1.memberid=t2.memberid
full outer join @t3 t3 on t3.memberid=t2.memberid
--order by 1     (If you so desire)

 

 Toni...

toniupstny:

ISNULL evaluates one item and does a substitution of like type if the value is null.   Coalesce allows multiple items to be compared in one statement.

I understand the basic usage but the original article said:

"Let's start with the documented use of coalesce. According to MSDN, coalesce returns the first non-null expression among its arguments."

Which sort of implied that it did something else as well - I just wondered what that might be.

interesting bit here: http://blogs.msdn.com/sqltips/archive/2008/06/26/differences-between-isnull-and-coalesce.aspx 
particularly about the query execution plans.

 I thought this was an excellent article and passed it on to some of my team members, but one statement you made was that Coalesce is more than just a more powerful version of isnull.  Everything in the article seemedto fit with the idea of a more powerful version of isnull.

Sorry about the controversy about the article.  I see a lot of questions about how to pivot data and I also know a lot of people who have never used the coalesce function.  I was just trying to raise awareness of them both.  I should have explained that better in the article.

Again, sorry if I mislead anyone. That was not my intention.

 Thanks,

Ken, I thought the article was fine and brought up some discussion which makes people think (almost always a good thing).  You are braver than most to even write an article.

Toni

I agree. Thanks for starting such a lively discussion, with lots of interesting variations on your original theme.

Yes - definitely food for thought.  The examples of pivoting data have been particularly useful.

Thanks

 

Helpppp please

 hey i guys i keep searching for the best way to search into table with best performance and easy to write techniqe

i tried the Coalesce and it was perfect for me but i found one thing that drive me back and think again about useing it which is Coalesce with Cloumn with null values. for example...

create a table with clumn ID,Name,Title,FLow

insert (1,MM,Null,BB)

insert(2,AA,CC,Null)

SELECT     Name, Title, Flow
                          
FROM         Table
                          
WHERE     Name = COALESCE (@name, name) AND title = COALESCE (@title, title) AND flow = COALESCE (@flow, flow)

 

it didn't return anything

because of the null values.

so what is the best thing we can do to avoid this problem..

thanks in advance

Hi Mohhosny. If I follow you correctly you want to be able to show all the rows even if one of the values contains a null?   If so, this would do it:

 Hope this helps.

Toni

********************************************************

drop table tbla
create table tbla ( ID  int,Name varchar(10),Title varchar(10),FLow varchar(10))

insert into tbla
values (1,'MM',Null,'BB')

insert into tbla
values(2,'AA','CC',Null)

SELECT     Name=COALESCE (name, 'NullName'),
        Title=COALESCE (title, 'NullTitle'), Flow = COALESCE (flow, 'Nullflow')
                         
FROM     tbla

 Thanks Toni for your response..

your idea working if only i want to select but i want to search in table

what i need is something like that

declare @name nchar(50)
declare @title nchar(50)
declare @flow nchar(50)

set @name = 'M'
set @title = null
set @flow = null

  SELECT     Name, Title, Flow                      
FROM     tbla
WHERE     Name like COALESCE ('%' + @name + '%', name) AND title = COALESCE (@title, title)
AND flow = COALESCE (@flow, flow) 

so if i pass all parameters with null it returns all rows.and if i set any parameter it returns all the rows contains that parameter even if some other parameter contains null values like the table you have created.

You can CASE

select *
from mytable
where name like case when @name is null then name else @name end
and email = case when @email is null then email else @email end

Note that you'll likely get a table scan