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

January 20, 2023

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…

>Solution :

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.