#MySQL Operators

-- Calculate totals
SELECT 
    price,
    quantity,
    price * quantity AS total
FROM orders;

-- Calculate discounts
SELECT 
    original_price,
    discount_percent,
    original_price * (1 - discount_percent / 100) AS discounted_price
FROM products;

-- Average calculation
SELECT (price1 + price2 + price3) / 3 AS avg_price
FROM price_comparison;

-- Equality
SELECT * FROM users WHERE id = 1;

-- Inequality
SELECT * FROM products WHERE price <> 0;

-- Greater than
SELECT * FROM orders WHERE total > 100;

-- Range queries
SELECT * FROM products 
WHERE price >= 50 AND price <= 100;

-- Case-sensitive comparison (binary)
SELECT * FROM users WHERE BINARY name = 'John';

-- Case-insensitive comparison
SELECT * FROM users WHERE name = 'john';

-- Pattern matching
SELECT * FROM products WHERE name LIKE 'Phone%';

-- AND operator
SELECT * FROM users 
WHERE age >= 18 AND age <= 65;

-- OR operator
SELECT * FROM products 
WHERE category = 'electronics' OR category = 'computers';

-- NOT operator
SELECT * FROM users 
WHERE NOT (status = 'inactive');

-- Combined logic
SELECT * FROM orders 
WHERE (status = 'pending' OR status = 'processing')
  AND created_at > '2024-01-01';

-- AND with NULL
SELECT NULL AND 1;    -- NULL (not 0)
SELECT NULL OR 1;     -- 1

-- Use IS NULL
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- NULL-safe comparison
SELECT * FROM users WHERE email <=> NULL;  -- NULL-safe equality

-- Bitwise AND
SELECT 5 & 3;  -- 1 (0101 & 0011 = 0001)

-- Bitwise OR
SELECT 5 | 3;  -- 7 (0101 | 0011 = 0111)

-- Bitwise XOR
SELECT 5 ^ 3;  -- 6 (0101 ^ 0011 = 0110)

-- Left shift
SELECT 1 << 3;  -- 8 (0001 shifted left 3 = 1000)

-- Case-sensitive comparison
SELECT 'a' = 'A';  -- 0 (false)
SELECT BINARY 'a' = 'A';  -- 1 (true)

-- LIKE pattern matching
SELECT 'MySQL' LIKE 'My%';  -- 1 (true)
SELECT 'MySQL' NOT LIKE 'My%';  -- 0 (false)

-- Concatenation operator (deprecated)
SELECT 'Hello' || 'World';  -- Hello World (if PIPES_AS_CONCAT enabled)

-- CONCAT function (recommended)
SELECT CONCAT('Hello', ' ', 'World');  -- Hello World

-- Match pattern
SELECT * FROM users WHERE name LIKE 'J%';
SELECT * FROM users WHERE email LIKE '%@example.com';

-- Match specific position
SELECT * FROM products WHERE code LIKE 'PROD-%';

-- Regular expression match
SELECT * FROM users WHERE name REGEXP '^J';
SELECT * FROM products WHERE code REGEXP '^[A-Z]{4}-[0-9]{3}$';

-- Not match
SELECT * FROM users WHERE name NOT REGEXP '^[0-9]';

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

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

-- Equivalent to AND
SELECT * FROM products 
WHERE price >= 50 AND price <= 100;

-- Multiple values
SELECT * FROM users 
WHERE country IN ('USA', 'Canada', 'UK');

-- Subquery
SELECT * FROM products 
WHERE category_id IN (SELECT id FROM categories WHERE active = 1);

-- NOT IN
SELECT * FROM products 
WHERE category_id NOT IN (1, 2, 3);

-- Test for NULL
SELECT * FROM users WHERE phone IS NULL;

-- Test for not NULL
SELECT * FROM users WHERE email IS NOT NULL;

-- Combined with other conditions
SELECT * FROM users 
WHERE email IS NOT NULL AND status = 'active';

-- NULL-safe equality
SELECT * FROM users 
WHERE email <=> NULL;

-- NULL-safe inequality
SELECT * FROM users 
WHERE email <=> old_email;  -- Returns 1 if both NULL

-- Precedence matters
-- Without parentheses
1 + 2 * 3  -- 7 (2*3 first, then +1)
-- With parentheses
(1 + 2) * 3  -- 9 (1+2 first, then *3)

-- Complex expression
SELECT 
    price * quantity * discount
FROM products;
-- Evaluates as: price * (quantity * discount)
-- Should be: (price * quantity) * discount
SELECT 
    (price * quantity) * discount
FROM products;

-- Set variable
SELECT @total := price * quantity
FROM products
WHERE id = 1;

-- Multiple assignments
SELECT @a := 1, @b := 2, @c := 3;

-- Conditional assignment
SELECT @status := CASE 
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    ELSE 'C'
END
FROM students;

-- Age categorization
SELECT 
    name,
    age,
    CASE 
        WHEN age < 18 THEN 'Minor'
        WHEN age < 65 THEN 'Adult'
        ELSE 'Senior'
    END AS age_group
FROM users;

-- Price range classification
SELECT 
    product_name,
    price,
    CASE 
        WHEN price < 50 THEN 'Budget'
        WHEN price < 100 THEN 'Standard'
        WHEN price < 200 THEN 'Premium'
        ELSE 'Luxury'
    END AS price_class
FROM products;

-- Pattern matching
SELECT * FROM users 
WHERE name LIKE '%Smith%'
  AND email LIKE '%@company.com';

-- Combined conditions
SELECT * FROM products 
WHERE (category = 'electronics' OR category = 'computers')
  AND price BETWEEN 50 AND 500
  AND active = 1;

-- Complex expressions
SELECT 
    product_name,
    price,
    quantity,
    price * quantity AS subtotal,
    price * quantity * (1 - discount) AS total,
    price * quantity * (1 - discount) + shipping AS final_total
FROM orders
JOIN products ON orders.product_id = products.id;