How can an AND operator and an OR operator output the same data [duplicate]

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 is important. If you are dealing with ORs, then using parathesis to denote the logic you want is important. — Thom A
– Thom A ♦, Commented Jan 20, 2023 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
– Thom A ♦, Commented Jan 20, 2023 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
– Aaron Bertrand, Commented Jan 20, 2023 at 16:09

Aaron Bertrand · Accepted Answer · 2023-01-20 16:13:23Z

4

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.

answered Jan 20, 2023 at 16:13

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

derpirscher · Accepted Answer · 2023-01-20 16:17:41Z

0

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)

answered Jan 20, 2023 at 16:17

derpirscher

17.7k3 gold badges25 silver badges54 bronze badges

Collectives™ on Stack Overflow

How can an AND operator and an OR operator output the same data [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related