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

Question

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...

[Operator precedence](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/operator-precedence-transact-sql?view=sql-server-ver16) is important. If you are dealing with `OR`s, then using parathesis to denote the logic you want is important. — Thom A, Jan 20 '23 at 16:09
Also, using SARGable clauses is important. You should really be using date boundaries, rather than using the `YEAR` and `MONTH` operators on the column in the `WHERE`. — Thom A, Jan 20 '23 at 16:09
Big difference between `w AND x AND y OR z` and `w AND (x AND y) OR (z)` and `w AND ((x and y) OR (z))`. `AND` wins. — Aaron Bertrand, Jan 20 '23 at 16:09

score 4 · Accepted Answer · answered Jan 20 '23 at 16:13

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.

score 0 · Answer 2 · answered Jan 20 '23 at 16:17

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)

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

2 Answers2