MySQL WHERE Clause

-- 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: 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: 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 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';