COALESCE

  • Comments posted to this topic are about the item COALESCE

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

    Tom

  • 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

  • Good question chirag 🙂

    thanks

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

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

    Thanks Chirag. 🙂

  • Thanks. Good question.

    Dave Morris :alien:

    "Measure twice, saw once"

  • Thank you for the great question and clear answer.



    Everything is awesome!

  • Thanks for the question.

  • This was removed by the editor as SPAM

  • 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!

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

  • Thanks for this nice question, Chirag!

  • 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.

  • 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/

  • 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