O'Reilly logo

Managing & Using MySQL, 2nd Edition by Hugh E. Williams, Randy Yarger, George Reese, Tim King

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required