MySQL AND Condition
The AND operator combines conditions so that a row must satisfy all of them. It is how you narrow a search: hotels in Goa and rated 4+ and under ₹7,000.
1. Syntax and Meaning
WHERE condition1 AND condition2 [AND condition3 ...]Table: hotels (7 rows)
| hotel_id | name | city | rating | price | pool | open_since |
|---|---|---|---|---|---|---|
| 1 | Sea Breeze | Goa | 4.5 | 6200 | 1 | 2012 |
| 2 | Palm Court | Goa | 3.8 | 3900 | 1 | 2018 |
| 3 | Hill View | Shimla | 4.2 | 5400 | 0 | 2009 |
| 4 | Lagoon Bay | Goa | 4.1 | 8800 | 1 | 2021 |
| 5 | City Inn | Jaipur | 3.5 | 2500 | 0 | 2015 |
| 6 | Fort Palace | Jaipur | 4.7 | 12000 | 1 | 2005 |
| 7 | Snow Peak | Shimla | NULL | 4700 | 0 | 2023 |
2. Two Conditions
SELECT name, city, rating FROM hotels WHERE city = 'Goa' AND rating >= 4;Result: 2 rows
| name | city | rating |
|---|---|---|
| Sea Breeze | Goa | 4.5 |
| Lagoon Bay | Goa | 4.1 |
3. Three or More Conditions
SELECT name, city, rating, price
FROM hotels
WHERE city = 'Goa'
AND rating >= 4
AND price < 7000;Result: 1 row
| name | city | rating | price |
|---|---|---|---|
| Sea Breeze | Goa | 4.5 | 6200 |
SELECT name FROM hotels WHERE pool = TRUE AND open_since >= 2010 AND rating IS NOT NULL;Result: 3 rows
| name |
|---|
| Sea Breeze |
| Palm Court |
| Lagoon Bay |
4. AND Truth Table (with NULL)
| A | B | A AND B |
|---|---|---|
| TRUE | TRUE | TRUE |
| TRUE | FALSE | FALSE |
| FALSE | NULL | FALSE |
| TRUE | NULL | NULL |
| NULL | NULL | NULL |
SELECT (1=1) AND (2=2) AS t_t, (1=1) AND (1=2) AS t_f, (1=2) AND NULL AS f_null, (1=1) AND NULL AS t_null;Result: 1 row
| t_t | t_f | f_null | t_null |
|---|---|---|---|
| 1 | 0 | 0 | NULL |
Snow Peak's rating is NULL, so city = 'Shimla' AND rating > 4 is NULL for it, and it isn't returned:
SELECT name, rating FROM hotels WHERE city = 'Shimla' AND rating > 4;Result: 1 row
| name | rating |
|---|---|
| Hill View | 4.2 |
5. AND for Ranges
Two conditions on the same column make a range. BETWEEN is a shortcut for the inclusive form:
SELECT name, price FROM hotels WHERE price >= 4000 AND price <= 6500;Result: 3 rows
| name | price |
|---|---|
| Sea Breeze | 6200 |
| Hill View | 5400 |
| Snow Peak | 4700 |
SELECT name, price FROM hotels WHERE price BETWEEN 4000 AND 6500;Result: 3 rows
| name | price |
|---|---|
| Sea Breeze | 6200 |
| Hill View | 5400 |
| Snow Peak | 4700 |
6. AND in UPDATE and DELETE
UPDATE hotels SET price = price - 500
WHERE city = 'Jaipur' AND pool = FALSE;
SELECT name, price FROM hotels WHERE city = 'Jaipur';Result: 2 rows
| name | price |
|---|---|
| City Inn | 2000 |
| Fort Palace | 12000 |
7. && as a Synonym
MySQL accepts && for AND, but it is deprecated and non-standard. Write AND.
SELECT name FROM hotels WHERE city = 'Goa' && pool = TRUE;Result: 3 rows
| name |
|---|
| Sea Breeze |
| Palm Court |
| Lagoon Bay |
8. AND and Indexes
For several equality/range conditions joined by AND, a composite index covering them works best. Put equality columns first, then the range column:
CREATE INDEX idx_city_rating ON hotels (city, rating);
EXPLAIN SELECT name FROM hotels WHERE city = 'Goa' AND rating >= 4;Result: 1 row
| type | key | key_len | rows | Extra |
|---|---|---|---|---|
| range | idx_city_rating | 66 | 2 | Using index condition |
9. Practice Exercises
Exercise 1: Hotels with a pool, rated above 4, opened after 2010.
SELECT name FROM hotels WHERE pool = TRUE AND rating > 4 AND open_since > 2010;Result: 2 rows
| name |
|---|
| Sea Breeze |
| Lagoon Bay |
Exercise 2: Jaipur hotels between ₹2,000 and ₹5,000.
SELECT name, price FROM hotels WHERE city = 'Jaipur' AND price BETWEEN 2000 AND 5000;Result: 1 row
| name | price |
|---|---|
| City Inn | 2000 |
10. Summary
- AND returns rows where every condition is TRUE.
- FALSE AND anything is FALSE; TRUE AND NULL is NULL (row excluded).
- Use AND for multi-filter searches and ranges (or BETWEEN).
- A composite index on the AND-ed columns speeds queries up; write AND rather than &&.