COALESCE

  • PChiragS

    SSCarpal Tunnel

    Points: 4965

    Comments posted to this topic are about the item COALESCE

  • TomThomson

    SSC Guru

    Points: 104773

    I vastly prefer this to the previous QOTD. It's straightforward, nice clear and accurate explanation, and doesn't make my brain hurt.

    Tom

  • Lokesh Vij

    SSChampion

    Points: 10836

    An easy one Chirag with nice explanation. Thanks!

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • kapil_kk

    SSC-Insane

    Points: 21316

    Good question chirag 🙂

    thanks

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • sqlnaive

    SSCoach

    Points: 17435

    Good question at the start of week. To check "the catch" i the question helped.

    Thanks Chirag. 🙂

  • seatedElephant

    Say Hey Kid

    Points: 674

    Thanks. Good question.

    Dave Morris :alien:

    "Measure twice, saw once"

  • Dana Medley

    SSCertifiable

    Points: 6764

    Thank you for the great question and clear answer.



    Everything is awesome!

  • Michael Poppers

    SSCrazy

    Points: 2131

    Thanks for the question.

  • This was removed by the editor as SPAM

  • NBSteve

    Hall of Fame

    Points: 3411

    For anybody like myself who was still a bit unclear, it may help to think of COALESCE as having 2 separate aspects. It needs to return a value, and it needs to return a type. The rule for returning a value is simple enough... it returns the first non-NULL value. The rule for returning a type, however, is based on the highest data type precedence which means it may come from a different expression than the value comes from.

    To illustrate this, consider these two examples:

    COALESCE(NULL, 1, 'A')

    COALESCE(NULL, 'A', 1)

    The value returned by COALESCE comes from the first non-NULL expression, 1 in the first case, 'A' in the second case. However, the type returned comes from data type precedence, and since int is a higher precedence than varchar, both (try to) return an int type. Of course, trying to return 'A' as an int type throws an error, meaning the first statement will work, and the second will not.

    Great question, thanks!

  • Ken Wymore

    SSCoach

    Points: 16588

    Nice clear question Chirag! Thanks for an easy one to start off the week with.

  • Revenant

    SSC-Forever

    Points: 42467

    Thanks for this nice question, Chirag!

  • sknox

    SSChampion

    Points: 12292

    Good question! I would clarify the answer a little bit, though:

    ... the result returns the data type of the expression with the highest data type precedence.

    should probably be

    ... the result returns the data type of the non-NULL expression with the highest data type precedence.

    This is why rows 1 and 2 pass -- even though zip is an int as defined by the column, its data type is not considered in these rows because it is NULL.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    sknox (6/3/2013)


    Good question! I would clarify the answer a little bit, though:

    ... the result returns the data type of the expression with the highest data type precedence.

    should probably be

    ... the result returns the data type of the non-NULL expression with the highest data type precedence.

    This is why rows 1 and 2 pass -- even though zip is an int as defined by the column, its data type is not considered in these rows because it is NULL.

    No, that is incorrect. The explanation in the question is correct, yours is wrong. The data type is always the same. Has to be, because the data type of the column in the result set is part of the meta data for the result set as a whole, not for individual rows.

    The first row passes because the first non-NULL value is already int, so no conversion needed.

    The second row passes because the first non-NULL value is the string '1'. Converstion to int succeeds.

    The third row fails because the first non-NULL value is the string 'a2', which cannot be converted to int.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • TomThomson

    SSC Guru

    Points: 104773

    sknox (6/3/2013)


    Good question! I would clarify the answer a little bit, though:

    ... the result returns the data type of the expression with the highest data type precedence.

    should probably be

    ... the result returns the data type of the non-NULL expression with the highest data type precedence.

    This is why rows 1 and 2 pass -- even though zip is an int as defined by the column, its data type is not considered in these rows because it is NULL.

    No, it certainly shouldn't. The original explanation is correct. Your version is incorrect.

    The types of any NULL arguments to coalesce and considered along with the types of the non-null arguments when determining what type the result must be.

    The argument zip is considered every time, when determining the highest precedence type, whether it's null or not. If the third row considered had been (null','a2',null) instead of (null,'a2',111) we would have had exactly the same failure on exactly the same row for exactly the same reason as with the values in the question.

    Don't forget that in SQL NULLs have type; a NULL value in an int column or for an int variable has type int, as of course does the constant expression cast(NULL as int).

    Back in January I had a QotD aiming to help people learn how typed nulls and coalesce interact:Coalesce and Conversion[/url], it may help you to look at that.

    Tom

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply