MySQL Operators

Overview

MySQL operators are used to perform operations on data in SQL statements. They include arithmetic, comparison, logical, and bitwise operators.

Operator Categories

• Arithmetic Operators: Math operations
• Comparison Operators: Compare values
• Logical Operators: Boolean logic
• Bitwise Operators: Binary operations
• Assignment Operators: Set values

Arithmetic Operators

Basic Arithmetic

| Operator |-----------------|------------------|-----------------|--------------| | + | Addition | - | Subtraction | * | Multiplication | % | Modulus

Examples

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

Comparison Operators

Comparison Table

| Operator |-----------------|------------------| | = | Equal to | <=> | Null-safe equal | <> or != | Not equal to | > | Greater than | >= | Greater than or equal | < | Less than | <= | Less than or equal

Examples

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

String Comparison

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

Logical Operators

Logical Table

| Operator |-----------------|------------------|-------------------| | NOT or ! | Logical NOT | AND | Logical AND | OR | Logical OR

Examples

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

NULL Handling

-- 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 Operators

Bitwise Table

| Operator |-----------------|------------------| | & | Bitwise AND | | | Bitwise OR | ^ | Bitwise XOR | ~ | Bitwise NOT | << | Left shift | >> | Right shift

Examples

-- 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)

String Operators

Comparison

-- 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

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

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

Pattern Matching Operators

LIKE / LIKE

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

REGEXP / REGEXP

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

Range Operators

BETWEEN / BETWEEN

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

IN / IN

-- 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);

NULL Operators

IS NULL / IS NULL

-- 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 Comparison

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

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

Operator Precedence

Precedence Table

| Priority |----------------|------------------| | 1 | !, ~, - (unary minus) | 2 | ^ | | 3 | *, /, DIV, %, MOD | | 4 | +, - | | 5 | <<, >> | | 6 | & | | 7 | | | | 8 | =, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN | | 9 | BETWEEN, CASE, WHEN, THEN, ELSE | | 10 | NOT | | 11 | &&, AND | | 12 | ||, OR | | 13 | := (assignment)

Examples

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

Assignment Operators

:= Operator

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

Practical Examples

Conditional Logic

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

String Operations

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

Calculations

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

Summary

MySQL operators provide:

• Arithmetic: Math calculations
• Comparison: Value comparison
• Logical: Boolean operations
• Bitwise: Binary operations
• Pattern Matching: LIKE, REGEXP
• Range: BETWEEN, IN
• NULL Handling: IS NULL, <=>
• Precedence: Order of evaluation
• Assignment: Variable assignment

Understanding operator precedence and using parentheses ensures correct query results.

---

Previous: MySQL Functions

Next: Commands Reference