VMware ESXi and vSphere Cluster Management

Use Logical Operators in MySQL

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

Logical operators let you build more useful MySQL filters. They combine or reverse conditions so a WHERE clause can decide which rows belong in the result set.

What logical operators do

A condition is a test applied to a row, such as name = 'Amy' or year > 1990. A comparison operator such as =, >, or < compares values. A logical operator combines conditions or changes their truth value.

The WHERE clause specifies which rows a query should return. When a query contains several conditions, MySQL evaluates the logical expression for each row. Rows whose expression is true are included in the result set.

AND requires every connected condition to be true.
OR requires at least one connected condition to be true.
NOT reverses the result of a condition.

Set up the example table

This lesson uses testtb, a small table with the columns name, surname, and year.

CREATE TABLE testtb (
    name VARCHAR(50),
    surname VARCHAR(50),
    year INT
);

INSERT INTO testtb (name, surname, year) VALUES
    ('Amy', 'Bryant', 1991),
    ('Mark', 'Smith', 1955),
    ('John', 'von Neumann', 1921),
    ('Aaron', 'Rogers', 1995),
    ('Brian', 'Cormier', 1988);

Start with an unfiltered query. This gives you the complete data set to use when checking later results.

SELECT * FROM testtb;

The full result contains these five rows:

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

Using AND

AND returns a match only when all linked conditions are true for the same row. It is useful when a selection must be more specific.

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

This returns only Amy Bryant, 1991. Her name matches 'Amy' and her surname matches 'Bryant'.

A row containing Amy with a different surname would fail the second condition. A row containing Bryant with a different first name would fail the first condition. Because both tests must be true, either kind of row is excluded.

Using OR

OR returns a row when one or more connected conditions are true. It usually produces a broader result than AND.

SELECT *
FROM testtb
WHERE name = 'Amy' OR year = 1995;

This returns Amy Bryant and Aaron Rogers. Amy satisfies the name condition, while Aaron satisfies the year condition. If a row satisfied both conditions, it would still appear only once in the result set; SQL returns rows, not one copy per satisfied condition.

Using NOT

NOT negates a condition. It returns rows for which the enclosed condition is not true.

SELECT *
FROM testtb
WHERE NOT name = 'Mark';

This excludes Mark Smith and returns Amy, John, Aaron, and Brian. The condition name = 'Mark' is true for Mark's row, so NOT makes that row fail the filter.

You can negate other conditions as well:

SELECT * FROM testtb WHERE NOT surname = 'Smith';
SELECT * FROM testtb WHERE NOT year > 1990;

In the second query, rows with years that are not greater than 1990 qualify. Use numeric comparisons for the numeric year column, and quote text values such as names and surnames.

AND, OR, and NOT at a glance

AND — all linked conditions must be true — name = 'Amy' AND surname = 'Bryant'

OR — one or more linked conditions must be true — name = 'Amy' OR year = 1995

NOT — the condition must not be true — NOT name = 'Mark'

Combining and grouping conditions

You can use several logical operators in one WHERE clause. Parentheses explicitly show which conditions should be evaluated together.

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

First, the parenthesized expression selects rows named Amy or Aaron. The AND then requires those selected rows to have a year after 1990. With the sample data, the result is Amy Bryant, 1991, and Aaron Rogers, 1995.

Operator precedence

Operator precedence is the order in which SQL evaluates operators when parentheses are absent. For these logical operators, the usual order is:

1. NOT — evaluated first; use parentheses to make a larger negated expression clear.

2. AND — evaluated before OR.

3. OR — evaluated after AND.

Compare these two queries:

SELECT *
FROM testtb
WHERE name = 'Amy' OR name = 'Aaron' AND year > 1992;

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

In the first query, AND is evaluated first. It means:

name = 'Amy'
OR (name = 'Aaron' AND year > 1992)

That query returns Amy and Aaron. In the second query, the names are grouped first, and then the year test applies to both names. Only Aaron qualifies because 1995 is greater than 1992, while Amy's year is 1991.

Even when you know the precedence rules, parentheses make the intended logic easier to read and reduce mistakes.

Interpreting result sets

To interpret a filtered query, examine each row in the complete testtb data and evaluate the condition for that row:

For AND, reject a row as soon as any required condition is false.
For OR, keep a row as soon as at least one condition is true.
For NOT, reverse the truth value of the condition it applies to.

An empty result is valid. It means no row satisfied the complete condition. For example, this query requires one row to have two different first names at once:

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

No row can satisfy both tests, so MySQL returns no rows.

Troubleshooting logical filters

No rows returned

Check whether AND made the filter more restrictive than intended. Also check spelling, capitalization behavior for your collation, and the exact stored text. Run SELECT * FROM testtb;, then test each condition separately before combining them.

Too many rows returned

You may have used OR where every condition needed to be true. Decide whether the requirements are alternatives or simultaneous requirements. Replace OR with AND when all conditions must match.

Unexpected rows in a mixed expression

Remember that NOT is evaluated before AND, and AND before OR. Add parentheses around the intended groups, then compare the new result set with the unfiltered table.

Text comparison errors

Put text literals in matching single quotes, as in name = 'Amy'. Do not quote numeric comparisons unnecessarily; write year > 1990 for the integer column.

NOT excludes the wrong rows

Read NOT as reversing the condition immediately following it. When negating a larger expression, group that expression explicitly:

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

Exam-relevant notes

WHERE filters rows before the result set is returned.
= is a comparison operator; AND, OR, and NOT are logical operators.
AND narrows results, while OR generally broadens them.
Use parentheses whenever the intended grouping of AND and OR is important.
Always verify a filtered result against the complete table when learning or debugging.

Practice: Modify the examples to find people whose year is less than 1990, whose surname is not 'Smith', and whose name is either Amy or Brian. Predict each result before running the query.

For another example of this topic, see Use Logical Operators 2.