Use Logical Operators in MySQL

Learn how to use AND, OR, and NOT in MySQL WHERE clauses to combine, broaden, or exclude filtering conditions.

Logical operators let you control which rows a MySQL query returns when one condition is not enough. They combine, refine, or negate conditions in a WHERE clause.

A condition is a comparison or expression that can evaluate to true, false, or unknown when NULL is involved. A condition used to decide whether a row belongs in the result is also called a predicate.

This lesson assumes that you know how to write basic SELECT statements and simple WHERE filters. For background, see Query a Database and SQL Commands Syntax.

The SELECT and WHERE pattern

The general form for filtering rows is:

SELECT columns
FROM table_name
WHERE condition;

For the examples in this lesson, use a table named testtb with these columns:

name — a person's first name
surname — a person's surname
year — an integer year

Inspect the data before applying a filter:

SELECT * FROM testtb;

The sample rows are:

Amy, Bryant, 1991
Mark, Smith, 1955
John, von Neumann, 1921
Aaron, Rogers, 1995
Brian, Cormier, 1988

AND: require every condition

AND returns true only when all connected conditions are true. It is useful when a row must satisfy more than one requirement.

SELECT *
FROM testtb
WHERE name = 'Amy' AND surname = 'Bryant';

The result contains only:

Amy, Bryant, 1991

The row must have both the name Amy and the surname Bryant. A row with the correct name but a different surname is excluded. A row with the correct surname but a different name is also excluded. If no single row satisfies every condition at the same time, the query returns no rows.

How to troubleshoot an AND filter

If an AND query returns nothing, test each comparison separately:

SELECT * FROM testtb WHERE name = 'Amy';
SELECT * FROM testtb WHERE surname = 'Bryant';

Then verify that the values occur together in the same row. Finding each value somewhere in the table is not enough for an AND query.

OR: accept at least one condition

OR returns true when one or more connected conditions are true. It is useful when several alternatives should qualify.

SELECT *
FROM testtb
WHERE name = 'Amy' OR name = 'Aaron';

The result contains:

Amy, Bryant, 1991
Aaron, Rogers, 1995

Every returned row matches at least one name condition. If a row happens to match both conditions, it is still returned only once because a result set contains rows, not one copy per matching predicate.

An OR query can return more rows than expected if you were thinking “both conditions.” Use AND when every condition must match, or add more specific conditions.

NOT: reverse a condition

NOT negates a condition. In other words, it changes a true condition to false and a false condition to true.

SELECT *
FROM testtb
WHERE NOT surname = 'Smith';

This returns every sample row except:

Mark, Smith, 1955

The query is selecting rows whose surname does not match Smith. This differs from selecting rows where the surname equals Smith: the positive condition finds the matching rows, while the negated condition finds the nonmatching rows.

For readability, the condition can also be written with parentheses:

SELECT *
FROM testtb
WHERE NOT (surname = 'Smith');

Logical operator behavior summary

AND: all linked conditions must be true for a row to be returned.
OR: at least one linked condition must be true for a row to be returned.
NOT: the condition's truth value is reversed, so matching rows become nonmatching rows and vice versa.

These operators work with many kinds of predicates, including comparisons involving numbers, dates, and strings:

SELECT *
FROM testtb
WHERE year >= 1990 AND name = 'Amy';

Combining AND and OR safely

When AND and OR appear in the same WHERE clause, use parentheses to show which conditions belong together. Parentheses are grouping symbols that make the intended evaluation order explicit.

For example, this query selects rows whose name is either Amy or Aaron, and whose year is at least 1990:

SELECT *
FROM testtb
WHERE (name = 'Amy' OR name = 'Aaron')
  AND year >= 1990;

The parenthesized part is evaluated as one alternative-name condition. The year requirement is then applied to that group. With the sample data, both Amy and Aaron qualify.

Without explicit grouping, a mixed expression may be evaluated differently from what you intended. MySQL generally evaluates AND before OR, but relying on precedence can make a query difficult to read and easy to change incorrectly. Write the grouping you mean:

SELECT *
FROM testtb
WHERE surname = 'Bryant'
  AND (name = 'Amy' OR name = 'Aaron');

This means: the surname must be Bryant, and the name may be Amy or Aaron. Parentheses prevent the OR alternative from escaping the intended surname requirement.

Negating a compound condition

NOT can also negate a parenthesized group:

SELECT *
FROM testtb
WHERE NOT (name = 'Amy' OR name = 'Aaron');

This excludes rows whose name is Amy or Aaron and returns the remaining names. Grouping is especially important when NOT is applied to more than one condition.

NULL and logical filters

NULL means that a value is missing or unknown. A comparison such as surname = 'Smith' does not evaluate to true for a NULL surname; it evaluates to unknown. Because the WHERE clause returns only rows whose predicate is true, a negated comparison does not automatically include those rows.

If a NOT-style filter should also include missing surnames, test for NULL explicitly:

SELECT *
FROM testtb
WHERE NOT (surname = 'Smith')
   OR surname IS NULL;

Use IS NULL and IS NOT NULL for null tests rather than = NULL or <> NULL.

Common mistakes and fixes

AND returns no rows: no individual row satisfies every condition. Check each comparison separately and confirm that the values occur together.
OR returns too many rows: OR accepts either condition, not necessarily both. Replace it with AND when all requirements are mandatory.
A mixed expression gives unexpected results: add parentheses around the conditions that should be evaluated together.
NOT omits NULL rows: comparisons with NULL are unknown. Add an explicit OR column_name IS NULL when missing values should qualify.

Exam-relevant notes

A WHERE clause filters rows before the result is returned.
AND narrows results because every connected predicate must be true.
OR broadens results because one or more predicates may be true.
NOT excludes rows that satisfy its condition, but NULL requires separate handling.
Use parentheses whenever AND and OR are mixed so the intended logic is unambiguous.

After mastering logical operators, continue with Advanced SELECT Statements, Sort Results, and LIMIT Clause.