SQL WHERE statement with OR or AND

Question

I have this SQL WHERE statement:

WHERE code = @code OR approvedby = @approvedby AND [status] = 'APPROVED'

I want to display the information of APPROVED status either code and approvedby parameter is true AND status must be APPROVED but the problem is PENDING status will also display.

Can anyone explain if I have made a mistake?

Think you need brackets`WHERE (code = @code OR approvedby = @approvedby ) AND [status] = 'APPROVED' ` — Utsav, Apr 08 '17 at 01:51
It's due to order of operations - `AND`s are done first, then `OR`s, so your statement is processed like: `code = @code OR (approvedby = @approvedby AND [status] = 'APPROVED')`, and is returning anything where `code = @code`, since only one half of an `OR` needs to be `true` — Rufus L, Apr 08 '17 at 01:59
Operator precedence for SQL is described [here](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/operator-precedence-transact-sql) — Rufus L, Apr 08 '17 at 02:04
Possible duplicate of [SQL Logic Operator Precedence: And and Or](http://stackoverflow.com/questions/1241142/sql-logic-operator-precedence-and-and-or) — Mark Rotteveel, Apr 08 '17 at 08:27

score 3 · Answer 1 · answered Apr 08 '17 at 01:51

3

I'm guessing you are just missing parentheses:

WHERE (code = @code OR approvedby = @approvedby) AND
      [status] = 'APPROVED'

answered Apr 08 '17 at 01:51

Gordon Linoff

1,242,037
58
646
786

[Yep!](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/operator-precedence-transact-sql) – Rufus L Apr 08 '17 at 02:05

score 3 · Accepted Answer · answered Apr 08 '17 at 01:56

3

You have ambiguous logic in your query, so the computer is probably processing differently to what you expect. Try putting brackets where you need them.

A or B and C - ambiguous
(A or B) and C - A or B can be true, and C is true
A or (B and C) - either A is true, or B and C are true together

You probably want:

WHERE 
(code = @code OR approvedby = @approvedby)
AND
[status] = 'APPROVED'

answered Apr 08 '17 at 01:56

HorusKol

8,375
10
51
92

1

Technically it's not ambiguous - the order of operations are defined such that `AND` comes before `OR`. So the way he has it written is processed like your third item, and anything where `code = @code` will be returned. – Rufus L Apr 08 '17 at 02:02
@RufusL - you're right, it isn't ambiguous to the parser and will always be processed according to Order of Operations. The ambiguity arises from fallible humans who don't process query logic like a reliable parser. – HorusKol Apr 08 '17 at 02:27

SQL WHERE statement with OR or AND

2 Answers2