Managing & Using MySQL, 2nd Edition

Logical Operators

SQL’s logical operators—AND, OR, and NOT—let you build more dynamic WHERE clauses. The AND and OR operators specifically let you add multiple criteria to a query:

SELECT USER_NAME
FROM USER
WHERE AGE > 18 AND STATUS = 'RESIDENT';

This sample query provides a list of all users who are residents and are old enough to vote. In other words, it finds every resident 18 years or older.

You can build increasingly complex queries and override MySQL’s order of precedence with parentheses. The parentheses tell MySQL which comparisons to evaluate first:

SELECT USER_NAME 
FROM USER
WHERE (AGE > 18 AND STATUS = 'RESIDENT')
OR (AGE > 18 AND STATUS = 'APPLICANT');

In this more complex query, we are looking for anyone currently eligible to vote as well as people who might be eligible in the near future. Finally, you can use the NOT operator to negate an entire expression:

SELECT USER_NAME
FROM USER
WHERE NOT (AGE > 18 AND STATUS = 'RESIDENT');

In this case, negation provides all the users who are not eligible to vote.