MySQL WHERE Clause Conditions Operators: Tutorial, Examples, FAQs & Interview Tips

The WHERE Clause

The WHERE clause filters rows returned by SELECT, or rows affected by UPDATE and DELETE. Only rows where the condition evaluates to TRUE are included. Without a WHERE clause, all rows are affected.

Comparison Operators

-- Equal
SELECT * FROM products WHERE category = 'Electronics';

-- Not equal (both forms work)
SELECT * FROM products WHERE category != 'Electronics';
SELECT * FROM products WHERE category <> 'Electronics';

-- Greater than / Less than
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price < 50;

-- Greater than or equal / Less than or equal
SELECT * FROM products WHERE price >= 29.99;
SELECT * FROM products WHERE stock <= 10;

-- BETWEEN (inclusive on both ends)
SELECT * FROM products WHERE price BETWEEN 20 AND 100;

-- NOT BETWEEN
SELECT * FROM products WHERE price NOT BETWEEN 20 AND 100;

IN, LIKE, and NULL Checks

IN, NOT IN, LIKE, IS NULL

-- IN: match any value in a list
SELECT * FROM products WHERE category IN ('Electronics', 'Office');

-- NOT IN: exclude values in a list
SELECT * FROM customers WHERE customer_id NOT IN (1, 2, 3);

-- LIKE: pattern matching
-- % matches any sequence of characters (including empty)
-- _ matches exactly one character
SELECT * FROM customers WHERE email LIKE '%@example.com';
SELECT * FROM products  WHERE name  LIKE 'Laptop%';
SELECT * FROM customers WHERE first_name LIKE '_ob';   -- Bob, Rob, etc.

-- NOT LIKE
SELECT * FROM products WHERE name NOT LIKE '%Mouse%';

-- IS NULL: check for NULL values
SELECT * FROM customers WHERE phone IS NULL;

-- IS NOT NULL: check for non-NULL values
SELECT * FROM customers WHERE phone IS NOT NULL;

-- LIKE is case-insensitive by default with utf8mb4_unicode_ci collation
-- Use BINARY for case-sensitive LIKE
SELECT * FROM customers WHERE BINARY email LIKE 'Alice%';

AND, OR, NOT, and Operator Precedence

Combine multiple conditions with AND, OR, and NOT. AND has higher precedence than OR - always use parentheses to make complex conditions explicit and avoid bugs.

AND, OR, NOT, and Parentheses

-- AND: both conditions must be true
SELECT * FROM products
WHERE category = 'Electronics' AND price < 100;

-- OR: at least one condition must be true
SELECT * FROM products
WHERE category = 'Electronics' OR category = 'Office';

-- NOT: negate a condition
SELECT * FROM customers WHERE NOT active = 0;
-- equivalent to:
SELECT * FROM customers WHERE active != 0;

-- Combining AND and OR - use parentheses to control precedence
-- Without parentheses: AND binds tighter than OR
-- This means: (category='Electronics' AND price < 50) OR stock > 100
SELECT * FROM products
WHERE category = 'Electronics' AND price < 50
   OR stock > 100;

-- With parentheses: explicit grouping
SELECT * FROM products
WHERE (category = 'Electronics' OR category = 'Office')
  AND price < 100
  AND stock > 0;

-- Complex real-world example
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE active = 1
  AND (email LIKE '%@gmail.com' OR email LIKE '%@yahoo.com')
  AND phone IS NOT NULL;

WHERE with UPDATE and DELETE

-- WHERE in UPDATE: only update matching rows
UPDATE products
SET price = price * 0.9
WHERE category = 'Electronics' AND stock > 50;

-- WHERE in DELETE: only delete matching rows
DELETE FROM customers
WHERE active = 0 AND created_at < '2023-01-01';

-- DANGER: UPDATE or DELETE without WHERE affects ALL rows!
-- Always double-check your WHERE clause before running
-- Tip: run as SELECT first to preview affected rows
SELECT * FROM customers WHERE active = 0 AND created_at < '2023-01-01';

Previous Next

MySQL WHERE Clause Conditions Operators: Tutorial, Examples, FAQs & Interview Tips

The WHERE Clause

IN, LIKE, and NULL Checks

AND, OR, NOT, and Operator Precedence

Level Up Your My sql Skills

Popular Tutorials

Ready to Level Up Your Skills?