OR Operator

Question

Post reply

OR Operator

jignesh209

SSC Eights!

Points: 959
More actions
April 6, 2012 at 3:28 pm

#258897

Just a little confused with OR operator in SQL Server.
I have the following query,the below query returns all rows from the table Item.
There exists an Item with ItemId=16
When I use 1=0 then it only returns Item with ItemId=16.
DECLARE @itemId INT
SET @itemId = 16
SELECT *
FROM Item i
WHERE ( i.ItemId = @itemId
OR 1 = 1
)
Regards

Viewing 5 posts - 1 through 4 (of 4 total)

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Your OR operator is evaluated against each row.

Assume your @ItemID 16 is present in 3rd row of your table ( table contains only 5 rows, lets assume)

ItemID

22

23

16

42

52

So when the query engine touches the first row, it checks if itemID = 16 OR 1 = 1 . The first condtion fails as firstrow's itemid is 22 , so 22 != 16 , but as u r using OR clause with 1 = 1, it returns true for that row.

So your condition for the row becomes like this 22 = 16 or 1 = 1. If one of the either condition passes, that row is returned!

Hope this clarifies.. As a side note, what do u really want to acheive out of your query?

Chuck Hottle SSCarpal Tunnel Points: 4777 More actions · Answer 2

I'm not sure why you're using the 1 = 1, but here's how I see it. When you use 1 = 0, that never evaluates to true for any rows, so the only row returned is for the correct item ID. When you use 1 = 1, that evaluates to true for all rows so all rows are returned.

jignesh209 SSC Eights! Points: 959 More actions · Answer 3

Thanks Chuck Hottle and ColdCoffee for your quick response

I am debugging a Stored Procedure and came across this SQL Statement.

The actual sql statement is some what like:

DECLARE @itemId INT

SET @itemId = 16

SELECT *

FROM Item i

WHERE

( i.ItemId = @itemId

OR 1 = CASE WHEN @IsProducedItems <> 1 THEN 1

ELSE 0

END

)

CapnHector SSCoach Points: 16143 More actions · Answer 4

jerry209 (4/6/2012)
Thanks Chuck Hottle and ColdCoffee for your quick response
I am debugging a Stored Procedure and came across this SQL Statement.
The actual sql statement is some what like:
DECLARE @itemId INT
SET @itemId = 16
SELECT *
FROM Item i
WHERE
( i.ItemId = @itemId
OR 1 = CASE WHEN @IsProducedItems <> 1 THEN 1
ELSE 0
END
)

the case statement is checking that the variable @IsProducedItems is not equal to 1 and if it is not display the record even if the @itemId does not equal i.ItemId

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]