CODEWITHSUNDEEP
sqlsql-servernorthwind
doron
doron

Reputation: 11

How can an AND operator and an OR operator output the same data

How could it be that these two queries, resolve in the same output?

SELECT *
FROM [dbo].[Orders]
where 1=1
AND year(OrderDate) = 1998 
AND MONTH(OrderDate) = 5
OR year(OrderDate) = 1997
AND MONTH(OrderDate) = 4 


SELECT *
FROM [dbo].[Orders]
where 1=1
AND (year(OrderDate) = 1998 AND MONTH(OrderDate) = 5)
OR (year(OrderDate) = 1997 AND MONTH(OrderDate) = 4)

I was expecting them to defer, since the second query clearly genrates 2 populations: orders from 05.1998 and 04.1997. The first query somehow does the same...

Upvotes: -1

Views: 73

Answers (2)

derpirscher
derpirscher

Reputation: 17382

If you take into account operator precedence and parenthesis, both your conditions are equivalent to

WHERE (1=1 and year = 1998 and month = 5) or 
      (year = 1997 and month = 4)

But as 1=1 is aways true and true AND x AND y is equivalent to x AND y you can remove the 1=1 thus both your conditions are in fact

WHERE (year = 1998 and month = 5) or
      (year = 1997 and month = 4)

Upvotes: 0

anon
anon

Reputation:

You want to use:

where 1=1
AND 
(
  (
         OrderDate >= '19980501'
     AND OrderDate <  '19980601'
  )
  OR
  (
         OrderDate >= '19970401'
     AND OrderDate <  '19970501'
  )
)

As Larnu suggested, see the Operator Precedence topic to see how AND and OR behave, and how to control the behavior you want by grouping predicates inside parentheses.

Upvotes: 4

Related Questions