How can I combine ANDs and ORs in my SQL statement

Question

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;

There's an easy way to test if your SQL is valid - try executing it. — Christian, Sep 08 '15 at 18:35

score 3 · Accepted Answer · edited May 23 '17 at 12:02

3

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)

edited May 23 '17 at 12:02

Community

1
1

answered Sep 08 '15 at 18:36

Sergey Kalinichenko

714,442
84
1,110
1,523

Thank you for your response, actually what I want is to first make sure I'm picking the ones that are deleted (In future projects I will have to check first if deleted=0 so I'm picking only the ones that are not deleted) then I check for types. So according to what are you saying about precedence it seems like it doesn't matter where I put the AND deleted=1, at the beginning or end of sentence, correct? – VaTo Sep 08 '15 at 18:38
Is that right @dasblinkenlight what eggyal is saying? I also think they are in a correct order. – VaTo Sep 08 '15 at 18:44
@eggyal By "first query" I meant "first parenthesized query", so there is some re-numbering going on. I edited to make it explicit. – Sergey Kalinichenko Sep 08 '15 at 18:44
1

@SaulOrtega This may be confusing. I edited to explain what I mean by "first" and "second". – Sergey Kalinichenko Sep 08 '15 at 18:45
With the first query it will check FIRST if the row is deleted=1 and then will check for types, because you said it has higher precedence correct? – VaTo Sep 08 '15 at 18:48
@SaulOrtega SQL does not specify in what order the checks are performed. Precedence is meant to decide how non-parenthesized queries are to be interpreted. SQL engine decides on the order internally. It may not even evaluate the expression at all - for example, if there is an index that lets the engine return the rows by searching the index directly. But the end result is that in all rows returned by the first parenthesized query the `deleted` flag would be set to `1`. – Sergey Kalinichenko Sep 08 '15 at 18:54

score 1 · Answer 2 · answered Sep 08 '15 at 18:37

1

What you need is IN operator like

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

answered Sep 08 '15 at 18:37

Rahul

76,197
13
71
125

score 1 · Answer 3 · answered Sep 08 '15 at 18:39

1

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

answered Sep 08 '15 at 18:39

eggyal

122,705
18
212
237

Thanks eggyal, I think they are backwards correct? the first one is for the second and the second for the first query, correct? – VaTo Sep 08 '15 at 18:41
@SaulOrtega: Your #1 and #3 are the same as my #1. Your #2 and my #2 are the same. – eggyal Sep 08 '15 at 18:44

How can I combine ANDs and ORs in my SQL statement

3 Answers3

Linked

Related