SQL operators
SQL operators are reserved keywords or characters used in a WHERE clause to perform arithmetic, comparison, and logical operations.
They act as the filters that tell the database engine exactly how to evaluate your data rows.
SQL operators fall into three primary categories: Arithmetic, Comparison, and Logical.
Comparison Operators
These are used to compare two values. The result of a comparison is always a boolean value: TRUE, FALSE, or UNKNOWN (if a value is NULL).
| Operator | Meaning | Example Statement |
|---|---|---|
| = | Equal to | WHERE status = 'Active' |
| != or <> | Not equal to | WHERE role != 'Admin' |
| > | Greater than | WHERE stock_count > 100 |
| < | Less than | WHERE price < 19.99 |
| >= | Greater than or equal to | WHERE rating >= 4.5 |
| <= | Less than or equal to | WHERE delivery_days <= 3 |
Logical Operators
Logical operators allow you to combine multiple conditions or reverse the meaning of a condition.
- AND: Returns rows where all combined conditions are true.
WHERE department = 'Sales' AND salary > 50000;
- OR: Returns rows where at least one condition is true.
WHERE city = 'London' OR city = 'Paris';
- NOT: Reverses the outcome of any boolean condition.
WHERE NOT status = 'Terminated';
- IN: Checks if a value matches any item within a provided list.
WHERE country IN ('India', 'UK', 'USA', 'Japan');
- BETWEEN: Filters values within an inclusive range (numbers, text, or dates).
WHERE price BETWEEN 10 AND 50;
- LIKE: Searches for a specific text pattern using wildcards (% for multiple characters, _ for a single character).
WHERE email LIKE '%@gmail.com';
- IS NULL / IS NOT NULL: Safely checks for empty or missing data fields.
WHERE phone_number IS NULL;
Arithmetic Operators
These perform standard mathematical operations directly on numerical columns or values within your SELECT statements.
| Operator | Meaning | Example Statement |
|---|---|---|
| + | Addition | SELECT base_pay + bonus AS total_pay |
| - | Subtraction | SELECT price - discount AS final_price |
| * | Multiplication | SELECT item_price * quantity AS subtotal |
| / | Division | SELECT annual_salary / 12 AS monthly_salary |
| % or MOD | Modulo (Returns remainder) | WHERE employee_id % 2 = 0 (Finds even IDs) |
Operator Precedence (Order of Operations)
When you combine different operators in a single query, SQL evaluates them in a specific order:
Parentheses () (Always calculated first to override default ordering)
Arithmetic operators (*, /, then +, -)
Comparison operators (=, >, <, etc.)
NOT
AND
OR
Why Parentheses Matter:
-- This might give unintended results because AND runs before OR
WHERE category = 'Electronics' OR category = 'Toys' AND price < 20;
-- This forces SQL to find Electronics or Toys first, THEN filters by price
WHERE (category = 'Electronics' OR category = 'Toys') AND price < 20;