AND Operator in MySQL
Introduction
Multiple conditions must be true simultaneously for accurate filtering.
The AND operator in MySQL combines conditions where ALL must be true.
It is widely used for:
- Complex WHERE clause filtering
- Precise data selection
- Business rule validation
What is the AND Operator
AND returns TRUE only when all conditions are TRUE.
If any condition is FALSE, the entire expression is FALSE.
Basic Syntax
sql
SELECT column_name
FROM table_name
WHERE condition1 AND condition2;
Example
sql
SELECT * FROM Students
WHERE age > 18 AND marks > 75;
Returns students who are both over 18 AND scored above 75.
AND with Multiple Conditions
All conditions must be true.
sql
SELECT * FROM Students
WHERE age > 18 AND marks > 75 AND city = 'Delhi';
Only students matching ALL three criteria.
AND with Different Data Types
Works with numbers, strings, and dates.
sql
SELECT * FROM Students
WHERE marks BETWEEN 80 AND 95 AND city = 'Mumbai';
AND Precedence Over OR
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'
AND with Parentheses
Control evaluation order explicitly.
sql
SELECT * FROM Students
WHERE (age > 18 OR age < 16) AND marks > 80;
AND with ORDER BY
Filter first, then sort.
sql
SELECT * FROM Students
WHERE marks > 85 AND age < 25
ORDER BY marks DESC;
AND with GROUP BY
Filter before grouping.
sql
SELECT city, AVG(marks)
FROM Students
WHERE marks > 60 AND age > 18
GROUP BY city;
AND 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.marks > 80 AND c.duration > 6;
AND with Subqueries
Complex condition combinations.
sql
SELECT * FROM Students
WHERE marks > 80 AND city IN (
SELECT city FROM TopCities
);
AND Short-Circuit Evaluation
MySQL stops evaluating after the first FALSE.
sql
SELECT * FROM Students
WHERE 1=0 AND expensive_function(); -- expensive_function() never called
Execution Order with AND
Evaluated after FROM and before GROUP BY.
FROM → WHERE (with AND) → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
Performance Considerations
- Place the most restrictive conditions first
- Index all AND condition columns
- Use parentheses for clarity
- Short-circuit helps performance
Important Notes
- AND has higher precedence than OR
- NULL AND TRUE = NULL
- FALSE AND anything = FALSE
- Parentheses override precedence
- Works with all comparison operators
Example Scenario
Find active high-performing students in specific cities:
sql
SELECT name, marks, city
FROM Students
WHERE status = 'Active'
AND marks >= 90
AND age BETWEEN 18 AND 25
AND city IN ('Delhi', 'Mumbai', 'Bangalore')
ORDER BY marks DESC
LIMIT 10;
Common Mistakes
- Forgetting parentheses with OR combinations
- Non-indexed columns in AND conditions
- NULL handling (NULL AND TRUE = NULL)
- Wrong condition order affecting performance
- Overly complex AND chains without readability
Key Points to Remember
- AND requires ALL conditions to be TRUE
- Higher precedence than OR (use parentheses)
- Short-circuits on the first FALSE condition
- Essential for precise multi-condition filtering
- Index AND columns for performance
- NULL AND anything = NULL