WHERE Clause Examples

-- ========================================
-- COMPARISON OPERATORS
-- ========================================

-- Basic equality
SELECT * FROM users WHERE status = 'active';

-- Not equal (two syntaxes)
SELECT * FROM products WHERE category != 'Electronics';
SELECT * FROM products WHERE category <> 'Electronics';

-- Numeric comparisons
SELECT * FROM products WHERE price > 100;
SELECT * FROM orders WHERE total_amount <= 500;
SELECT * FROM users WHERE age >= 18;

-- Date comparisons
SELECT * FROM orders WHERE order_date > '2024-01-01';
SELECT * FROM events WHERE event_date <= NOW();

-- ========================================
-- LOGICAL OPERATORS
-- ========================================

-- AND operator (all conditions must be true)
SELECT * FROM products 
WHERE price > 50 
  AND stock_quantity > 0 
  AND status = 'active';

-- OR operator (any condition can be true)
SELECT * FROM users 
WHERE role = 'admin' 
   OR role = 'moderator';

-- Combining AND and OR (use parentheses!)
SELECT * FROM products 
WHERE (category = 'Electronics' OR category = 'Computers')
  AND price < 1000
  AND stock_quantity > 0;

-- NOT operator
SELECT * FROM users WHERE NOT status = 'banned';
SELECT * FROM products WHERE NOT (price > 100 AND category = 'Luxury');

-- ========================================
-- IN and NOT IN
-- ========================================

-- IN operator (match any value in list)
SELECT * FROM orders 
WHERE status IN ('pending', 'processing', 'shipped');

-- NOT IN
SELECT * FROM products 
WHERE category NOT IN ('Discontinued', 'Test');

-- IN with subquery
SELECT * FROM users 
WHERE user_id IN (
    SELECT DISTINCT user_id 
    FROM orders 
    WHERE total_amount > 1000
);

-- ========================================
-- BETWEEN
-- ========================================

-- Numeric range
SELECT * FROM products 
WHERE price BETWEEN 50 AND 200;  -- Inclusive

-- Date range
SELECT * FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- NOT BETWEEN
SELECT * FROM users 
WHERE age NOT BETWEEN 13 AND 17;

-- ========================================
-- NULL HANDLING
-- ========================================

-- Check for NULL
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM products WHERE description IS NOT NULL;

-- NEVER use = NULL (wrong!)
-- SELECT * FROM users WHERE phone = NULL;  -- This won't work!

-- Coalesce NULL values
SELECT * FROM products 
WHERE COALESCE(discount, 0) > 10;

-- ========================================
-- LIKE and Pattern Matching
-- ========================================

-- Starts with
SELECT * FROM users WHERE username LIKE 'john%';

-- Ends with
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Contains
SELECT * FROM products WHERE name LIKE '%laptop%';

-- Specific pattern (_ for single character)
SELECT * FROM codes WHERE code LIKE 'ABC___';  -- ABC followed by 3 chars

-- Case-insensitive by default
SELECT * FROM users WHERE name LIKE 'john%';  -- Matches 'John', 'JOHN', etc.

-- Escape special characters
SELECT * FROM products WHERE name LIKE '%\_%' ESCAPE '\';  -- Contains underscore

-- NOT LIKE
SELECT * FROM users WHERE email NOT LIKE '%@test.com';

-- ========================================
-- REGULAR EXPRESSIONS
-- ========================================

-- Basic regex
SELECT * FROM users WHERE username REGEXP '^[a-z]+$';  -- Lowercase only

-- Email validation
SELECT * FROM users 
WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

-- Phone number pattern
SELECT * FROM contacts 
WHERE phone REGEXP '^[0-9]{3}-[0-9]{3}-[0-9]{4}$';

-- Word boundaries
SELECT * FROM posts 
WHERE content REGEXP '[[:<:]]MySQL[[:>:]]';  -- Whole word 'MySQL'

-- Case-sensitive regex
SELECT * FROM users 
WHERE BINARY username REGEXP '^[A-Z]';  -- Must start with uppercase

-- ========================================
-- COMPLEX CONDITIONS
-- ========================================

-- Multiple conditions with grouping
SELECT * FROM products 
WHERE 
    (category = 'Electronics' AND price < 500)
    OR (category = 'Books' AND price < 50)
    OR (is_featured = TRUE);

-- Date and status combination
SELECT * FROM orders 
WHERE 
    status IN ('pending', 'processing')
    AND order_date >= DATE_SUB(NOW(), INTERVAL 7 DAY)
    AND total_amount > 100;

-- Complex NULL handling
SELECT * FROM users 
WHERE 
    (email IS NOT NULL AND email != '')
    AND (phone IS NULL OR phone = '')
    AND status = 'active';

-- Using CASE in WHERE
SELECT * FROM products 
WHERE price > 
    CASE category
        WHEN 'Luxury' THEN 1000
        WHEN 'Standard' THEN 100
        ELSE 10
    END;