Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

T-SQL Expand / Collapse
Author
Message
Posted Wednesday, January 6, 2010 10:39 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 9, 2011 8:51 AM
Points: 291, Visits: 389
Comments posted to this topic are about the item T-SQL
Post #843310
Posted Wednesday, January 6, 2010 11:09 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:32 PM
Points: 21,755, Visits: 15,459
Thanks for the question



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #843316
Posted Thursday, January 7, 2010 12:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:48 AM
Points: 1,397, Visits: 1,565
This help page lists the logical operators (http://msdn.microsoft.com/en-us/library/ms189773(SQL.90).aspx) including IN.

I don't doubt that an entire clause containing IN is a predicate, but perhaps IN is the operator in the predicate.
Post #843336
Posted Thursday, January 7, 2010 12:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 6:08 AM
Points: 1,079, Visits: 591
Also thaught that a logical operator is correct but a lil explanation will be more apreciated from this side.

What you don't know won't hurt you but what you know will make you plan to know better
Post #843340
Posted Thursday, January 7, 2010 12:39 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, September 15, 2014 5:19 PM
Points: 588, Visits: 965
BOL says that IN is a logical operator. Can you please explain why you considered it as a Predicate? Since most of us answered it "wrong", I think we deserved an explanation, right?
Post #843350
Posted Thursday, January 7, 2010 1:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 3:29 PM
Points: 73, Visits: 520
brdudley (1/7/2010)
This help page lists the logical operators (http://msdn.microsoft.com/en-us/library/ms189773(SQL.90).aspx) including IN.

I don't doubt that an entire clause containing IN is a predicate, but perhaps IN is the operator in the predicate.


I agree - IN is technically the logical operator that exists within an expression, which then creates a predicate.

IN by itself certainly does not return anything at all - neither TRUE, nor FALSE, nor UNKNOWN - it would be a syntax error if that was used on it's own.


<predicate> ::=
{ expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression
| string_expression [ NOT ] LIKE string_expression
[ ESCAPE 'escape_character' ]
| expression [ NOT ] BETWEEN expression AND expression
| expression IS [ NOT ] NULL
| CONTAINS
( { column | * } , '< contains_search_condition >' )
| FREETEXT ( { column | * } , 'freetext_string' )
| expression [ NOT ] IN ( subquery | expression [ ,...n ] )
| expression { = | < > | ! = | > | > = | ! > | < | < = | ! < }
{ ALL | SOME | ANY} ( subquery )
| EXISTS ( subquery ) }


Source:
http://msdn.microsoft.com/en-us/library/ms173545%28SQL.90%29.aspx



--Chris Hamam

Life's a beach, then you DIE (Do It Eternally)
Post #843367
Posted Thursday, January 7, 2010 1:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:05 AM
Points: 2,842, Visits: 3,875
I agree that the given answer is wrong.
IN is an operator but can be used in an expression like "a IN (1,2,3)".
The expression in this case is a predicate, and IN is the operator used in the predicate.


Best Regards,
Chris Büttner
Post #843368
Posted Thursday, January 7, 2010 1:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:32 AM
Points: 3,964, Visits: 5,202
Agree with previous posts.
IN, like all logical operators, must form part of an expression.
A predicate may comprise one or more logical operators (albeit in a join or where)


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #843378
Posted Thursday, January 7, 2010 2:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 9, 2012 7:25 AM
Points: 298, Visits: 107
I agree with the previous posts. A little more justification for the answer please.
Also, I'm sure that I've seen my execution plans change

WHERE colA IN(1)
into
WHERE colA=1

and
WHERE colA IN(1,2,3)
into
WHERE colA=1 OR colA=2 OR colA=3

Though to be honest, I can't replicate that right now. I might have seen it running a trace at some point.

It does highlight how SQL tests a value in 2 columns though:

WHERE 1 in(colA,colB)
turns into
WHERE 1=colA OR 1=colB
Post #843388
Posted Thursday, January 7, 2010 2:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 7, 2013 4:37 AM
Points: 256, Visits: 224

Agree with Christian Buettner ... IN is the operator used in the predicate.
Post #843391
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse