MySQL logic operators: How to use AND, OR together in WHERE clauses in one query?

AND and OR are common logic operators across all programming languages and in MySQL, they mean the same thing. A statement (a.k.a, a condition in any WHERE clauses such as id > 100) can be either true or false. A group / combination of statements can be true or false, depending on their own values (true or false) and the logic operators that have joined them.

AND and OR each can join 2 statements.

AND – both statements must be true

For example, in this MySQL query:

SELECT * FROM student WHERE age > 10 AND grade < 4

All students who are graded less than 4 and older than 10 will be selected. The logic operator AND commands that both of the conditional statements (age > 10 and grade < 4) must be true for the whole statement (age > 10 AND grade < 4) to be true. You can add more conditions and ask them all to be true to narrow down the selection:

SELECT * FROM student WHERE age > 10 AND grade < 4 AND sex = 'female'

Which effectively selects all the female students who are older than 10 and have a grade of lower than 4 because the AND operators mean that all 3 statements must be true for the whole, joined statement (age > 10 AND grade < 4 AND sex = ‘female’) to be true.

OR – at least one statement must be true

Consider this example,

SELECT * FROM student WHERE grade > 4 OR grade < 2

This simply selects all the students who have a grade higher than 4 or lower than 2. All the students who satisfy one or more of these statements are selected. Students who satisfy none of these statements are neglected. You can as well use more than one OR in a series:

SELECT * FROM student WHERE grade > 4 OR age < 9 OR surname = 'Bush'

So that any student who has a grade higher than 4 or is less than 9 years old or has a surname of Bush will be selected.

How to use AND and OR together?

Consider the following example,

SELECT * FROM student WHERE grade > 2 AND grade < 5 OR age > 8 AND age < 12

As AND has a higher priority (effectively so in almost all programming languages) than OR, it will be evaluated before any OR statements. Therefore, this query will select all those students whose grade is higher than 2 and lower than 5 or those whose age is older than 8 and younger than 12.

You can add parenthesis but because AND has a higher priority in parsing the logic statement, it’s the same:

SELECT * FROM student WHERE (grade > 2 AND grade < 5) OR (age > 8 AND age < 12)

However, as parenthesis has the highest priority, if you add them in a different way such as this:

SELECT * FROM student WHERE (grade > 2 AND grade < 5 OR age > 8) AND age < 12

It would give completely different results. Firstly, grade > 2 AND grade < 5 must be true or age > 8 must be true, secondly, age < 12 must be true. For example, these students will satisfy this WHERE clause:

1. grade = 4, age = 7
2. age = 9
3. grade = 3, age = 11

These students don’t satisfy this WHERE clause:

1. age = 14
2. grade = 5, age = 7