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.