OR Operator

OR Operator in MySQL

Introduction
Flexible filtering requires matching ANY of several conditions.
The OR operator in MySQL returns TRUE when AT LEAST ONE condition is TRUE.
It is widely used for:

• Alternative condition matching
• Broad data selection
• User-driven flexible searches

What is the OR Operator
OR returns TRUE if any condition is TRUE.
Only returns FALSE when ALL conditions are FALSE.

Basic Syntax

sql

SELECT column_name 

FROM table_name 

WHERE condition1 OR condition2; 

Example

sql

SELECT * FROM Students 

WHERE city = 'Delhi' OR city = 'Mumbai'; 

Returns students from either Delhi OR Mumbai.

OR with Multiple Conditions
Any one condition can be true.

sql

SELECT * FROM Students 

WHERE city = 'Delhi' OR city = 'Mumbai' OR city = 'Bangalore'; 

OR with Different Data Types
Works across numbers, strings, and dates.

sql

SELECT * FROM Students 

WHERE marks > 90 OR age < 18 OR status = 'Special'; 

OR Precedence Problem
AND has higher precedence than OR.

sql

SELECT * FROM Students 

WHERE age > 18 AND marks > 70 OR city = 'Delhi'; 

Parsed as: (age > 18 AND marks > 70) OR city = 'Delhi' (NOT intended)

OR with Parentheses
Always use parentheses to control order.

sql

SELECT * FROM Students 

WHERE age > 18 OR age < 16 

  AND marks > 80; 

Correct: High marks AND (adult OR minor).

OR with ORDER BY
Filter first, then sort.

sql

SELECT * FROM Students 

WHERE city = 'Delhi' OR marks > 90 

ORDER BY marks DESC; 

OR with GROUP BY
Filter before grouping.

sql

SELECT city, COUNT(*) 

FROM Students 

WHERE marks > 80 OR age < 18 

GROUP BY city; 

OR with JOIN
Multiple conditions across tables.

sql

SELECT s.name, c.course_name 

FROM Students s 

JOIN Courses c ON s.course_id = c.id 

WHERE s.city = 'Delhi' OR c.duration > 12; 

OR with Subqueries
Complex OR conditions.

sql

SELECT * FROM Students 

WHERE city = 'Delhi' OR marks > ( 

    SELECT AVG(marks) FROM Students 

); 

OR Short-Circuit Evaluation
MySQL may short-circuit OR (stops after the first TRUE).

sql

SELECT * FROM Students 

WHERE expensive_function() OR marks > 90;  -- May skip function 

Execution Order with OR
Evaluated after FROM and before GROUP BY.
FROM → WHERE (with OR) → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Performance Considerations

• OR often prevents index usage (use UNION instead)
• Indexes work better with AND conditions
• Large OR lists: consider the IN operator
• Use parentheses for optimiser clarity

Important Notes

• OR has lower precedence than AND
• TRUE OR anything = TRUE
• NULL OR TRUE = TRUE
• NULL OR FALSE = NULL
• Parentheses essential with AND combinations

Example Scenario
Find top students OR students from premium cities:

sql

SELECT name, marks, city 

FROM Students 

WHERE (marks >= 95)

   OR (city IN ('Delhi', 'Mumbai', 'Bangalore') AND marks >= 85)

ORDER BY marks DESC, city

LIMIT 20;

Common Mistakes

• Forgetting parentheses with AND+OR combinations
• Poor performance with many OR conditions
• NULL handling confusion (NULL OR FALSE = NULL)
• Using OR when the IN operator is better
• Optimiser chooses the wrong execution plan

Key Points to Remember

• OR returns TRUE if ANY condition is TRUE
• Lower precedence than AND (use parentheses)
• Poor index usage with multiple OR conditions
• Consider the IN operator for OR lists
• Essential for flexible search conditions
• NULL OR FALSE = NULL (not FALSE)