MySQL - Where Or?

Question

I have a MySQL statement where I'm trying to exclude statements depending on where they "belong to" The query goes through, but it still shows the rows that I specifically said where its not equal to?

SELECT id, belongsto, title, madeby, sticky, locked, lastpost, posts
FROM threads
WHERE sticky !=1
AND belongsto !=12 OR sticky !=1 AND belongsto !=13
ORDER BY  `threads`.`date` DESC 
LIMIT 20

You don't really need `OR` at all since you're repeating the same criteria of `sticky !=1` on both. — Hart CO, Jul 30 '13 at 17:36
Removing " `OR sticky !=1`" from the query would be sufficient to fix the issue. The `OR` condition is making it so that a value of 12 will satisfy one side, and a value of 13 will satisfy the other side, so those values don't get "excluded". — spencer7593, Jul 30 '13 at 18:20

score 35 · Accepted Answer · answered Jul 30 '13 at 17:30

35

You need parenthesis around your OR statements to group them together logically.

WHERE sticky !=1
AND belongsto !=12 OR sticky !=1 AND belongsto !=13

should be:

WHERE (sticky !=1 AND belongsto !=12)
OR (sticky !=1 AND belongsto !=13)

or better yet:

 WHERE sticky !=1 AND belongsto NOT IN(12,13)

answered Jul 30 '13 at 17:30

John Conde

217,595
99
455
496

belongsto NOT IN(12,13,14), that is plain awesome :) Thanks. Works perfect. – Necro. Jul 30 '13 at 17:36
3

No parenthesis are "needed". The `AND` operator has a higher precedence than the `OR` operator; the addition of parenthesis (as shown under "should be:") does not change the statement; this does not resolve the issue. The rewrite under "better yet" does change the statement. – spencer7593 Jul 30 '13 at 18:10

score 3 · Answer 2 · answered Jul 30 '13 at 18:04

If the goal is to exclude rows with belongsto values of 12 and 13, then the OR should be replaced with AND.

The assertion (in the selected answer) that parentheses are "missing" and need to be added, is wrong. The addition of parentheses does not change the change the statement. The AND operator already has a higher precedence than the OR operator.)

WHERE sticky !=1
AND belongsto !=12 AND sticky !=1 AND belongsto !=13
                   ^^^

Because it's unnecessary to repeat the same predicate twice, this could be rewritten as:

WHERE sticky !=1
AND belongsto !=12 AND belongsto !=13

Which can also be written as:

WHERE sticky !=1
  AND NOT (belongsto =12 OR belongsto =13)

This can also be rewritten using a NOT IN (12,13) (as demonstrated in the selected answer).

MySQL - Where Or?

2 Answers2