Operators in MySql
MySQL Operators
MySQL provides several operators to perform operations in queries. Operators are classified into different categories:
1. Arithmetic Operators
Used for mathematical calculations.
| Operator | Description | Example | Result |
|---|---|---|---|
+ | Addition | 5 + 3 | 8 |
- | Subtraction | 10 - 4 | 6 |
* | Multiplication | 7 * 2 | 14 |
/ | Division | 10 / 2 | 5 |
% | Modulus (Remainder) | 10 % 3 | 1 |
✅ Example Query:
SELECT 10 + 5 AS sum, 10 / 2 AS division;
2. Comparison Operators
Used to compare values and return TRUE (1), FALSE (0), or NULL.
| Operator | Description | Example | Result |
|---|---|---|---|
= | Equal to | price = 100 | Rows where price is 100 |
!= or <> | Not equal to | price != 50 | Rows where price is NOT 50 |
> | Greater than | price > 200 | Prices greater than 200 |
< | Less than | price < 150 | Prices less than 150 |
>= | Greater than or equal to | price >= 100 | Prices 100 or more |
<= | Less than or equal to | price <= 300 | Prices 300 or less |
BETWEEN | Range | price BETWEEN 100 AND 500 | Prices between 100 and 500 |
IN | Matches any value in a list | city IN ('New York', 'LA') | Matches New York or LA |
LIKE | Pattern matching | name LIKE 'A%' | Names starting with A |
IS NULL | Checks for NULL values | salary IS NULL | Finds NULL salaries |
✅ Example Query:
SELECT * FROM products WHERE price BETWEEN 100 AND 500;
3. Logical Operators
Used to combine multiple conditions in WHERE clauses.
| Operator | Description | Example |
|---|---|---|
AND | Returns TRUE if both conditions are TRUE | price > 100 AND stock > 10 |
OR | Returns TRUE if at least one condition is TRUE | price > 500 OR stock < 5 |
NOT | Reverses the condition | NOT (price > 1000) |
✅ Example Query:
SELECT * FROM customers WHERE city = 'New York' AND age > 30;
4. Bitwise Operators
Used to perform operations at the binary level.
| Operator | Description | Example | Binary Result |
|---|---|---|---|
& | AND | 5 & 3 | 1 (101 & 011 = 001) |
| ` | ` | OR | `5 |
^ | XOR | 5 ^ 3 | 6 (101 ^ 011 = 110) |
<< | Left Shift | 5 << 1 | 10 (101 << 1 = 1010) |
>> | Right Shift | 5 >> 1 | 2 (101 >> 1 = 10) |
✅ Example Query:
SELECT 5 & 3 AS result; -- Output: 1
5. Assignment Operators
Used to assign values in queries.
| Operator | Description | Example |
|---|---|---|
= | Assign value | SET price = 200 |
:= | Assign value in expressions | SELECT @var := 10; |
✅ Example Query:
SET @x = 100;SELECT @x; -- Output: 100
6. Other Special Operators
| Operator | Description | Example |
|---|---|---|
EXISTS | Checks if a subquery returns results | EXISTS (SELECT * FROM orders WHERE customer_id = 5) |
ANY | Compares a value with any in a subquery | price > ANY (SELECT price FROM products) |
ALL | Compares a value with all in a subquery | price > ALL (SELECT price FROM products) |
✅ Example Query:
SELECT * FROM customers WHERE EXISTS (SELECT * FROM orders WHERE orders.customer_id = customers.id);
Key Takeaways
✅ Arithmetic Operators perform math operations.
✅ Comparison Operators compare values (=, <>, >, <, BETWEEN).
✅ Logical Operators (AND, OR, NOT) combine conditions.
✅ Bitwise Operators work on binary data.
✅ Assignment Operators store values in variables.
✅ Special Operators (EXISTS, IN, LIKE) handle advanced queries.
