Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Coalesce Vs IsNull query plan

    Can anyone explain why the execution plans of the following 3 queries are different?
    Code:
    DECLARE @t table (
       val char(5)
    )
    
    INSERT INTO @t (val)
      VALUES ('Test1')
           , ('Test2')
    
    --Query 1
    SELECT Coalesce(
             ( SELECT Max(val) 
               FROM   @t t2
               WHERE  t2.val = t1.val )
           , '') As z
    FROM   @t t1
    
    --Query 2
    SELECT IsNull(
             ( SELECT Max(val) 
               FROM   @t t2
               WHERE  t2.val = t1.val )
           , '') As z
    FROM @t t1
    
    --Query 3
    SELECT Coalesce(col, '') As z
    FROM   (
            SELECT ( SELECT Max(val) 
                     FROM   @t t2
                     WHERE  t2.val = t1.val ) As col
            FROM @t t1
           ) As x
    I can't get my head round the first one!

    Cheers,
    G
    George
    Home | Blog

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I can explain why 1 and 2 differ from 3 since they are semantically different. I can't explain why 1 and 2 differ.

    A clue as to how they differ would have been nice but too much to ask I suppose.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh you bugger - I thought you always used AS in your tables aliases?

    I didn't spot these were self joins. As such they are not semantically different.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I do, I do! But this is someone else's code refactored ever so slightly, that's all
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, what's the purpose?

    And besides the fact the you're correlating on something that's not in the result set, how does this have meaning?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    There is no purpose, merely an oddity that was pointed out to me by someone that I can't wrap my head round.

    I just don't understand what the heck is going on!
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you post the plans? Text or image?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Query 1
    Code:
      |--Compute Scalar(DEFINE:([Expr1013]=CASE WHEN [Expr1006] IS NOT NULL THEN [Expr1011] ELSE '' END))
           |--Nested Loops(Inner Join, PASSTHRU:(IsFalseOrNull [Expr1006] IS NOT NULL), OUTER REFERENCES:([t1].[val]))
                |--Nested Loops(Left Outer Join, OUTER REFERENCES:([t1].[val]))
                |    |--Table Scan(OBJECT:(@t AS [t1]))
                |    |--Stream Aggregate(DEFINE:([Expr1006]=MAX(@t.[val] as [t2].[val])))
                |         |--Table Scan(OBJECT:(@t AS [t2]), WHERE:(@t.[val] as [t2].[val]=@t.[val] as [t1].[val]))
                |--Stream Aggregate(DEFINE:([Expr1011]=MAX(@t.[val] as [t2].[val])))
                     |--Table Scan(OBJECT:(@t AS [t2]), WHERE:(@t.[val] as [t2].[val]=@t.[val] as [t1].[val]))
    Query 2
    Code:
      |--Compute Scalar(DEFINE:([Expr1008]=isnull([Expr1006],'     ')))
           |--Nested Loops(Left Outer Join, OUTER REFERENCES:([t1].[val]))
                |--Table Scan(OBJECT:(@t AS [t1]))
                |--Stream Aggregate(DEFINE:([Expr1006]=MAX(@t.[val] as [t2].[val])))
                     |--Table Scan(OBJECT:(@t AS [t2]), WHERE:(@t.[val] as [t2].[val]=@t.[val] as [t1].[val]))
    Query 3
    Code:
      |--Compute Scalar(DEFINE:([Expr1009]=CASE WHEN [Expr1006] IS NOT NULL THEN [Expr1006] ELSE '' END))
           |--Nested Loops(Left Outer Join, OUTER REFERENCES:([t1].[val]))
                |--Table Scan(OBJECT:(@t AS [t1]))
                |--Stream Aggregate(DEFINE:([Expr1006]=MAX(@t.[val] as [t2].[val])))
                     |--Table Scan(OBJECT:(@t AS [t2]), WHERE:(@t.[val] as [t2].[val]=@t.[val] as [t1].[val]))
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Cooo.
    Code:
    So for COALESCE it converts to '', for ISNULL it converts to '     '
    I will guess that is because COALESCE can return a different datatype, so by default it returns VARCHAR.

    I can't explain plan #1 - there does not appear to be good reason for the addition stream aggregate and nested loops join.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •