Discover different types of MySQL operators. On this page, you will find an overview of all important operators, their usage, and practical examples. Operators are divided into several categories: logical, comparison, arithmetic, and special operators.
The query searches for employees from the "Production" department who have salaries higher than 2000. We used the
logical operator AND, which means both conditions must be met.
SELECT first_name, last_name, salary, department FROM employees WHERE department = 'Production' AND salary > 2000;
The query uses the BETWEEN operator, which searches for a value within a specific range.
SELECT first_name, last_name, hire_year FROM employees WHERE hire_year BETWEEN 2018 AND 2020;
The third example shows the use of the less than or equal to operator and searches for employees with a salary lower than or equal to 1800.
SELECT first_name, last_name, salary FROM employees WHERE salary <= '1800';
Operators in MySQL allow you to perform mathematical calculations, compare values, and execute complex logical operations within your queries. The table below features all mathematical, comparison, logical, and bitwise operators with correct syntax and examples for the simulator.
| Operator | Description | Syntax (HTML) | Example |
|---|---|---|---|
| + | Addition of two or more numeric values. | Expression1 + Expression2 |
SELECT price + 5.00 FROM merchandise; |
| - | Subtraction of one numeric value from another. | Expression1 - Expression2 |
SELECT price - discount FROM promotions; |
| * | Multiplication of numeric values. | Expression1 * Expression2 |
SELECT quantity * price_per_unit FROM orders; |
| / | Division of numeric values. | Expression1 / Expression2 |
SELECT amount / 2 FROM invoices; |
| % | Modulo (returns the remainder of a division). | Expression1 % Expression2 |
SELECT id % 2 FROM employees; |
| = | Checks if two values are equal. | WHERE column = value |
SELECT * FROM customers WHERE city = 'London'; |
| > | Checks if the first value is greater than the second value. | WHERE column > value |
SELECT * FROM merchandise WHERE price > 500; |
| < | Checks if the first value is less than the second value. | WHERE column < value |
SELECT * FROM grades WHERE grade < 70; |
| >= | Checks if the first value is greater than or equal to the second value. | WHERE column >= value |
SELECT * FROM employees WHERE salary >= 2000; |
| <= | Checks if the first value is less than or equal to the second value. | WHERE column <= value |
SELECT * FROM inventory WHERE stock_quantity <= 10; |
| <>, != | Checks if two values are not equal. | WHERE column <> value |
SELECT * FROM employees WHERE department != 'Sales'; |
| <=> | NULL-safe equal to operator (returns true if both values are equal or both are NULL). | WHERE column <=> value |
SELECT * FROM null_table WHERE value <=> NULL; |
| AND, && | Logical AND. Condition is true if both conditions are met. | WHERE c1 AND c2 |
SELECT * FROM employees WHERE salary > 1800 AND department = 'Production'; |
| OR, || | Logical OR. Condition is true if at least one of the conditions is met. | WHERE c1 OR c2 |
SELECT * FROM customers WHERE city = 'London' OR city = 'Manchester'; |
| NOT, ! | Logical negation. Reverses the truth value of a condition. | WHERE NOT condition |
SELECT * FROM promotions WHERE NOT season = 'Summer'; |
| XOR | Logical exclusive OR. True if exactly one of the conditions is true. | WHERE c1 XOR c2 |
SELECT * FROM employees WHERE department = 'Purchasing' XOR salary > 2000; |
| & | Bitwise AND. Compares each bit of two numbers. | Expression1 & Expression2 |
SELECT id & 1 FROM employees; |
| | | Bitwise OR. Combines the bits of two numbers. | Expression1 | Expression2 |
SELECT id | 2 FROM employees; |
| ^ | Bitwise XOR (exclusive OR). | Expression1 ^ Expression2 |
SELECT id ^ 1 FROM employees; |
| ~ | Bitwise NOT. Inverts all bits of a number. | ~Expression |
SELECT ~id FROM employees; |
| << | Bitwise Left Shift. Shifts bits to the left. | Expression << shift |
SELECT id << 2 FROM employees; |
| >> | Bitwise Right Shift. Shifts bits to the right. | Expression >> shift |
SELECT id >> 1 FROM employees; |
| BETWEEN | Checks if a value falls within a specific inclusive range. | WHERE column BETWEEN r1 AND r2 |
SELECT * FROM merchandise WHERE price BETWEEN 50.00 AND 200.00; |
| IN | Checks if a value matches any value within a specified list. | WHERE column IN (v1, v2, ...) |
SELECT * FROM customers WHERE city IN ('Birmingham', 'Bristol', 'Leeds'); |
| LIKE | Searches for a pattern using wildcard characters (uses % for any characters). | WHERE column LIKE pattern |
SELECT * FROM employees WHERE last_name LIKE 'K%'; |
| REGEXP | Matches a value based on a regular expression pattern (advanced text search). | WHERE column REGEXP pattern |
SELECT * FROM news WHERE title REGEXP 'gaming|4K'; |
| IS NULL | Checks if the column value is empty (contains NULL). | WHERE column IS NULL |
SELECT * FROM null_table WHERE note IS NULL; |
| IS NOT NULL | Checks if the column value is not empty (is not NULL). | WHERE column IS NOT NULL |
SELECT * FROM null_table WHERE value IS NOT NULL; |
| DISTINCT | A keyword (acts as an equality operation) used to remove duplicate rows from the output. | SELECT DISTINCT column FROM table; |
SELECT DISTINCT department FROM sorting; |
