MySQL Operators

Types of Operators

SELECT 
    price,
    price * 1.1 AS with_tax
FROM products;

SELECT * FROM orders
WHERE total_amount >= 100;

WHERE price > 50 
AND category = 'Electronics'
OR on_sale = 1;

SELECT * FROM customers
WHERE name LIKE 'John%';

🔹 Arithmetic Operators

Arithmetic operators perform mathematical calculations on numeric values. Use them to calculate totals, apply discounts, compute percentages, or derive new values from existing data. They work in SELECT, WHERE, and other clauses for flexible numeric operations.

-- Basic arithmetic operations
SELECT 
    product_name,
    price,
    quantity,
    price * quantity AS subtotal,  -- Multiplication
    price * quantity * 0.1 AS tax,  -- Calculate 10% tax
    (price * quantity) + (price * quantity * 0.1) AS total  -- Addition
FROM order_items;

-- Using modulo operator
SELECT 
    order_id,
    order_id % 2 AS is_odd  -- 1 for odd, 0 for even
FROM orders;

🔹 Comparison Operators

Comparison operators test relationships between values and return TRUE or FALSE. They're essential in WHERE clauses for filtering data, finding records within ranges, matching exact values, or excluding specific results. Master these for precise data retrieval.

-- Equal to
SELECT * FROM products WHERE price = 99.99;

-- Not equal to (two ways)
SELECT * FROM customers WHERE status != 'inactive';
SELECT * FROM customers WHERE status <> 'inactive';

-- Greater than and less than
SELECT * FROM orders WHERE total_amount > 500;
SELECT * FROM products WHERE stock_quantity < 10;

-- Greater/Less than or equal to
SELECT * FROM employees WHERE salary >= 50000;
SELECT * FROM products WHERE price <= 100;

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

🔹 Logical Operators

Logical operators combine multiple conditions to create complex filters. AND requires all conditions to be true, OR requires at least one, and NOT inverts a condition. Use parentheses to control evaluation order and create sophisticated filtering logic in your queries.

-- AND: All conditions must be true
SELECT * FROM products
WHERE category = 'Electronics' 
    AND price < 500 
    AND stock_quantity > 0;

-- OR: At least one condition must be true
SELECT * FROM customers
WHERE city = 'New York' 
    OR city = 'Los Angeles' 
    OR city = 'Chicago';

-- NOT: Inverts the condition
SELECT * FROM orders
WHERE NOT status = 'cancelled';

-- Combining with parentheses
SELECT * FROM products
WHERE (category = 'Electronics' OR category = 'Computers')
    AND price BETWEEN 100 AND 1000
    AND stock_quantity > 0;

🔹 String Operators

String operators work with text data. LIKE performs pattern matching with wildcards (% for any characters, _ for single character), CONCAT joins strings together, and other operators help you search, manipulate, and format text in your database queries effectively.

-- LIKE with wildcards
SELECT * FROM customers
WHERE name LIKE 'John%';  -- Starts with 'John'

SELECT * FROM products
WHERE description LIKE '%wireless%';  -- Contains 'wireless'

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

-- CONCAT: Join strings
SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name,
    CONCAT('$', price) AS formatted_price
FROM employees;

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

-- REGEXP: Regular expression matching
SELECT * FROM customers
WHERE phone REGEXP '^[0-9]{3}-[0-9]{3}-[0-9]{4}$';

🔹 Special Operators

MySQL provides special operators for specific tasks. IS NULL checks for missing values, IN tests membership in a list, BETWEEN checks ranges inclusively, and EXISTS tests for subquery results. These operators make complex conditions simpler and more readable in your SQL.

-- IS NULL / IS NOT NULL
SELECT * FROM customers WHERE phone IS NULL;
SELECT * FROM orders WHERE delivery_date IS NOT NULL;

-- IN operator
SELECT * FROM products
WHERE product_id IN (1, 5, 10, 15, 20);

-- BETWEEN operator (inclusive)
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

SELECT * FROM products
WHERE price BETWEEN 50 AND 200;

-- EXISTS operator
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id
);

🔹 Operator Precedence

When multiple operators appear in one expression, MySQL evaluates them in a specific order. Arithmetic happens before comparison, comparison before logical. Use parentheses to override default precedence and make your intentions clear, avoiding unexpected results and improving code readability.

-- Without parentheses (may be confusing)
SELECT * FROM products
WHERE category = 'Electronics' OR category = 'Computers' AND price < 500;

-- With parentheses (clear intention)
SELECT * FROM products
WHERE (category = 'Electronics' OR category = 'Computers') 
    AND price < 500;

-- Arithmetic precedence
SELECT 
    price,
    price + 10 * 2 AS without_parens,  -- Result: price + 20
    (price + 10) * 2 AS with_parens    -- Result: (price + 10) * 2
FROM products;

/*
 * Operator Precedence (highest to lowest):
 * 1. Parentheses ()
 * 2. Multiplication *, Division /, Modulo %
 * 3. Addition +, Subtraction -
 * 4. Comparison =, !=, <, >, <=, >=
 * 5. NOT
 * 6. AND
 * 7. OR
 */