MySQL AND, OR, NOT Operators

When filtering data with the WHERE clause, sometimes a single condition is not enough. The AND, OR, and NOT operators combine or negate conditions to create more precise filters.

The AND Operator

AND combines two or more conditions. A row is included in the result only if all conditions evaluate to true.

Syntax

SELECT column1, column2
FROM table_name
WHERE condition1 AND condition2;

Example

Using the students table — retrieve students who are older than 19 AND whose last name is 'Smith':

SELECT first_name, last_name, age
FROM students
WHERE age > 19 AND last_name = 'Smith';

Only Bob Smith (age 22) satisfies both conditions.

The OR Operator

OR combines two or more conditions. A row is included if at least one of the conditions evaluates to true.

Syntax

SELECT column1, column2
FROM table_name
WHERE condition1 OR condition2;

Example

Retrieve students who are either 19 years old OR have the last name 'Johnson':

SELECT first_name, last_name, age
FROM students
WHERE age = 19 OR last_name = 'Johnson';

This returns Carol White (age 19) and Alice Johnson, since each satisfies at least one condition.

The NOT Operator

NOT reverses a condition — rows that do not match the condition are returned.

Syntax

SELECT column1, column2
FROM table_name
WHERE NOT condition;

Example

Retrieve all students who are NOT aged 22:

SELECT first_name, age
FROM students
WHERE NOT age = 22;

Returns Alice (20), Carol (19), and David (21) — anyone who is not 22.

Combining AND and OR

Multiple operators can be combined in a single WHERE clause. Parentheses control the order of evaluation — conditions inside parentheses are evaluated first, just like in mathematics.

Without Parentheses (potential confusion)

SELECT first_name, age, last_name
FROM students
WHERE age = 20 OR age = 22 AND last_name = 'Taylor';

MySQL evaluates AND before OR by default. This query means: age = 20 OR (age = 22 AND last_name = 'Taylor').

With Parentheses (clear intent)

SELECT first_name, age, last_name
FROM students
WHERE (age = 20 OR age = 22) AND last_name = 'Taylor';

Now both conditions must be satisfied: the student must be 20 or 22 AND have the last name 'Taylor'. Only Eve Taylor (22) satisfies this.

Using NOT with IN and LIKE

NOT works with other operators too:

-- Exclude students aged 19, 20, or 22
SELECT first_name, age
FROM students
WHERE age NOT IN (19, 20, 22);

-- Find students whose first name does NOT start with 'A'
SELECT first_name
FROM students
WHERE first_name NOT LIKE 'A%';

Operator Precedence

MySQL evaluates operators in this order:

1. NOT (highest priority)
2. AND
3. OR (lowest priority)

Use parentheses to override the default order and make the logic explicit and readable.

Practical Example: Filtering a Products Table

-- Find products that are either cheap (price < 100) or in high stock (stock > 500),
-- but exclude products from the 'Electronics' category
SELECT product_name, price, stock, category
FROM products
WHERE (price < 100 OR stock > 500)
AND NOT category = 'Electronics';

Key Points

• AND requires all conditions to be true for a row to be included.
• OR requires at least one condition to be true.
• NOT excludes rows that match the condition.
• AND has higher precedence than OR — use parentheses to control logic clearly.
• Combine all three operators to build complex, precise filters.