MySQL WHERE Clause: Complex Conditions Tutorial

Logical Operators in WHERE Clause

MySQL provides logical operators to combine multiple conditions in the WHERE clause.

The main logical operators are AND, OR, and NOT.

• AND: Returns true if both conditions are true.
• OR: Returns true if at least one condition is true.
• NOT: Negates a condition, returning true if the condition is false.

Combining Conditions with AND and OR

You can combine multiple conditions using AND and OR to refine your data filtering.

AND requires all combined conditions to be true, while OR requires at least one condition to be true.

• Example: WHERE age > 18 AND city = 'New York' returns records where both conditions hold.
• Example: WHERE city = 'New York' OR city = 'Los Angeles' returns records from either city.

Using NOT to Negate Conditions

The NOT operator reverses the truth value of a condition.

It is useful to exclude records that meet certain criteria.

• Example: WHERE NOT city = 'Chicago' returns records where city is not Chicago.

Nested Conditions with Parentheses

Parentheses group conditions to control the order of evaluation.

Without parentheses, AND operators have higher precedence than OR operators.

• Example: WHERE (city = 'New York' OR city = 'Los Angeles') AND age > 25 filters records from either city but only if age is over 25.
• Without parentheses, the query logic might not behave as intended.

Practical Examples

Let's look at some practical examples combining these concepts.