MySQL AND & OR Operators

This SQL tutorial focuses on the MySQL AND & OR operators, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts explaining how to use the WHERE clause in MySQL. To read additional posts regarding this subject, please use the following links:

• Introduction – MySQL WHERE clause
• Basic comparison operators
• IN operator
• BETWEEN operator
• LIKE operator
• IS NULL operator
• AND & OR operators

MySQL AND & OR Operators

The purpose of the MySQL AND & OR operators is to allow filtering based on multiple conditions. Using these operators, you can combine the result of two (or more) conditions to produce a single result based on them, for example:

MySQL AND Operator

AND requires all conditions to be true.

SELECT *
FROM books
WHERE price > 60
AND
      publisherID = 70

AND indicates that all conditions must be met – the books whose price is higher than 60 and whose publisher number equals 70.

The books who meet both these conditions are: The Hobbit and The Catcher in the Rye (2 books in total).

MySQL OR Operator

OR requires either condition to be true.

SELECT *
FROM books
WHERE price > 60
OR
      publisherID = 70

OR indicates that at least one of the conditions must be met – the books whose price is higher than 60 or whose publisher number equals 70.

• For Fifty Shades of Grey – none of these conditions is met.
• For The Little prince, the publisher number is 70 – one condition is met.
• For The Hobbit and The Catcher in the Rye, price is higher than 60 and the publisher number is 70 – both conditions are met.
• For A Tale Of Two Cities and The Lord of the Rings, price is higher than 60 – one condition is met.

5 books meet at least one of these conditions.

Note:

The WHERE keyword appears only once. After the AND operator or the OR operator, specify the additional condition without specifying the WHERE keyword again.

SELECT *
FROM books
WHERE price > 60
OR
WHERE publisherID = 70
-- (Error)

SELECT * 
FROM books 
WHERE price > 60
OR 
      publisherID = 70
-- Valid statement

Order of Precedence

The MySQL AND operator takes precedence over the MySQL OR operator (just like a multiplication operation takes precedence over an addition operation).

The following MySQL SELECT statement is used for displaying the students who:

(First condition) Live in London.

Or:

(Second condition) Live in Paris and whose age is higher than 30.

SELECT customer_id, first_name, city, age
FROM students
WHERE    city = 'London'
OR
         City = 'Paris' AND age > 30

Changing the Order of Precedence

Changing the order of priorities between the MySQL OR and AND operators requires round brackets  (just like round brackets change the order of priorities between the mathematical addition and multiplication operations).

The following MySQL SELECT statement is used for displaying the students who:

(First condition) Live in London OR Paris

And

(Second condition) their age is higher than 30

SELECT customer_id, first_name, city, age
FROM students
WHERE    (city = 'London' OR City = 'Paris')
AND     age > 30