Parsing values in the WHERE clause

  • Victor Abkin

    SSC-Addicted

    Points: 426

    Comments posted to this topic are about the item Parsing values in the WHERE clause

  • Revenant

    SSC-Forever

    Points: 42467

    So I was the first one who got it wrong. Good question, anyway - thanks!

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Nice question. thank you.

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Ron McCullough

    SSC Guru

    Points: 63877

    Nice question to start the week . .. Thanks

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Ron McCullough

    SSC Guru

    Points: 63877

    From the justification of what the correct answer is:

    Therefore a single value in the WHERE... IN clause can be surrounded by multiple pairs of parenthesis. Multiple values cannot.

    Why then do these work ?

    WHERE Musician_ID in (((1)),4)

    WHERE Musician_ID in (((1)),((4)))

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Lokesh Vij

    SSChampion

    Points: 10836

    Nice question. Thanks for Posting.

    ~ 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

    Nice Question in the start of the week 🙂

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

  • This was removed by the editor as SPAM

  • sule99

    Ten Centuries

    Points: 1021

    bitbucket-25253 (10/28/2012)


    From the justification of what the correct answer is:

    Therefore a single value in the WHERE... IN clause can be surrounded by multiple pairs of parenthesis. Multiple values cannot.

    Why then do these work ?

    WHERE Musician_ID in (((1)),4)

    WHERE Musician_ID in (((1)),((4)))

    Because single value (in your case 1 or 4) can be surrounded by multiple pairs of parenthesis, but list of values (in your case 1,4) cannot.

    For example

    WHERE Musician_ID in (( ((1)),4 )) -> ERROR, two pairs of parenthesis

    ,but

    WHERE Musician_ID in ( (((1))),((4)) ) -> CORRECT one pair of parenthesis

  • Carlo Romagnano

    SSC-Insane

    Points: 21807

    I get it right, but the explanation is completely wrong. The syntax for IN clause is or specify a subquery or a list of EXPRESSIONS. ONE expression can have multiple parentesis.

    You can write also: IN(++++2,++++3,+(+(+(+(+(4))))))

    They are EXPRESSIONS.

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Good question, but the explanation is wrong. SQL Server always parses each query.

    bitbucket-25253 (10/28/2012)


    From the justification of what the correct answer is:

    Therefore a single value in the WHERE... IN clause can be surrounded by multiple pairs of parenthesis. Multiple values cannot.

    Why then do these work ?

    WHERE Musician_ID in (((1)),4)

    WHERE Musician_ID in (((1)),((4)))

    Because the syntax for IN is: IN (expression, expression ...)

    One set of parentheses around a comma-seperated list of expressions.

    Of course, ((((((((4)))))))) is a valid (but rather pointless) expression.

    EDIT: Or I could just have scrolled to the end of the discussion first, and seen that Carlo has already posted something similar.


    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: 104772

    Good question, but as has already been pointed out the explanation is wrong. The process of determining syntactic structure and deciding whether it's valid or not is parsing, not anything else. SQL Server parses all of the SQL as a matter of course, in order to determine whether it is syntactically valid SQL and if it is to determine its precise syntactic structure. It doesn't need to see a comma to parse the text between the brackets of IN, because whatever the text is it has to determine whether it is a single expression or a comma separated list of expressions or neither of these. This can be demonstrated for example by replacing the where clause in query 4 by "WHERE Musician_ID in (2z3)", which will still result in a syntax error (incorrect syntax near z3) despite the absence of any comma; or by replacing the comma by a semicolon, or by any other means of causing the text not to be syntactically valid without having a comma in it.

    edit:typos.

    And while the BoL IN page is a sensible reference for this, the SELECT page is not, and the Expressions page, which isn't mentioned, is essential to understanding what is going on here.

    Tom

  • Koen Verbeeck

    SSC Guru

    Points: 258950

    Interesting question, thanks!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • DugyC

    Hall of Fame

    Points: 3804

    Nice and interesting question to start the week, thanks.

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • Rich Weissler

    Hall of Fame

    Points: 3235

    Brilliant! Thanks for the question! I believe it's a great question that draws out some discussion.

    A big thank you to Carlo and Hugo who provided a more succinct (and apparently correct) explanation.

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

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