Parsing values in the WHERE clause
-
Comments posted to this topic are about the item Parsing values in the WHERE clause
-
So I was the first one who got it wrong. Good question, anyway - thanks!
-
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. -
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)))
-
Nice question. Thanks for Posting.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
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
-
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
-
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.
-
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/ -
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
-
Interesting question, thanks!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
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] -
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 23 total)
You must be logged in to reply to this topic. Login to reply