CODEWITHSUNDEEP
mysqlsql
VaTo
VaTo

Reputation: 3098

How can I combine ANDs and ORs in my SQL statement

I have this SQL statement: 

SELECT * FROM `table` WHERE type = 3 OR type = 5 OR type = 4 and table.deleted = 1;

I've read that I can use parenthesis to accomplish this but I was wondering if then this would be valid:

SELECT * FROM `table` WHERE (type = 3 OR type = 5 OR type = 4) and table.deleted = 1;

OR

SELECT * FROM `table` WHERE (type = 3 OR type = 5) OR type = 4 and table.deleted = 1;

Upvotes: 1

Views: 1900

Answers (3)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726909

Both of these would be valid, but since AND has higher precedence than OR, they would mean different things:

  • Your first parenthesized query would pick deleted rows with types 3, 4, 5
  • Your second parenthesized query would select all rows with types 3, 5, in addition to deleted rows of type 4; this is the same meaning as in the original query without parentheses.

You can avoid the confusion altogether by using operator IN, like this:

SELECT * FROM `table` WHERE type IN (3, 4, 5) AND table.deleted = 1;

or if you wanted the second meaning

SELECT * FROM `table` WHERE type IN (3, 5) OR (type = 4 AND table.deleted = 1)

Upvotes: 3

eggyal
eggyal

Reputation: 125955

AND has higher precedence than OR, so your first and third filters are equivalent to:

type = 3 OR type = 5 OR (type = 4 and table.deleted = 1)

Your second filter could equivalently be expressed using IN():

type IN (3, 5, 4) and table.deleted = 1

Upvotes: 1

Rahul
Rahul

Reputation: 77896

What you need is IN operator like

SELECT * FROM `table` 
WHERE type IN ( 3, 5, 4) and deleted = 1;

Upvotes: 1

Related Questions