MySQL: A Detailed Explanation of MySQL Operators and…
Last updated: 2026-08-26
Operators are the foundation of SQL queries—every condition in a WHERE clause relies on them.
This lesson provides a systematic overview of all MySQL operators and their use cases.
graph TB
A[MySQL Operators] --> B[Arithmetic Operators<br/>+ - * / DIV %]
A --> C[Comparison Operators<br/>= != > < BETWEEN LIKE IN]
A --> D[Logical Operators<br/>AND OR NOT XOR]
A --> E[Bitwise operators<br/>& | ^ ~ << >>]
C --> C1[BETWEEN Scope]
C --> C2[IN List]
C --> C3[LIKE Blurred]
C --> C4[IS NULL Null value]
C --> C5[<=> Safety equals]
1. What You'll Learn
- Arithmetic operators (+−×÷)
- Comparison operators (=/>/</BETWEEN/LIKE/IN)
- Logical operators (AND/OR/NOT)
- Bitwise operators (&/|/^)
- Operator Precedence
2. A True Story Behind a Report
(1) Pain Point: Incorrectly Entered Filter Criteria
An operations specialist wants to look up "orders from the past 30 days with an amount greater than 100 and a status other than 'canceled'":
SQL
-- Incorrect Formulation
SELECT * FROM orders
WHERE order_date > '2026-06-03' AND amount > 100 AND status != 'cancelled';
-- Missed: status could be NULL
(2) Solving for Operators
SQL
-- Correct Way: Handle NULL
SELECT * FROM orders
WHERE order_date > DATE_SUB(CURDATE(), INTERVAL 30 DAY)
AND amount > 100
AND (status != 'cancelled' OR status IS NULL);
Benefits: No NULL values are omitted, ensuring accurate query results.
3. Arithmetic Operators
| Operator | Description | Example |
|---|---|---|
+ |
Addition | SELECT 1 + 1; → 2 |
- |
Subtraction | SELECT 5 - 3; → 2 |
* |
Multiplication | SELECT 2 * 3; → 6 |
/ |
Division | SELECT 10 / 3; → 3.3333 |
DIV |
Integer division | SELECT 10 DIV 3; → 3 |
% |
Modulus | SELECT 10 % 3; → 1 |
MOD |
Modulus | SELECT MOD(10, 3); → 1 |
▶ Example: Arithmetic Operations
Output:
TEXT
📖 Display only
-------------+-------+----------+------
product_name | price | quantity | total
-------------+-------+----------+------
Alice | 25.00 | 10 | 25.00
Bob | 50.00 | 15 | 50.00
Charlie | 75.00 | 20 | 75.00
-------------+-------+----------+------
3 rows in set
-------------+----------------+---------------+-----------
product_name | original_price | discount_rate | sale_price
-------------+----------------+---------------+-----------
Alice | 25.00 | 10 | 25.00
Bob | 50.00 | 15 | 50.00
Charlie | 75.00 | 20 | 75.00
-------------+----------------+---------------+-----------
3 rows in set
SQL
-- Calculate the total order amount
SELECT
product_name,
price,
quantity,
price * quantity AS total
FROM order_items;
-- Calculate the discounted price
SELECT
product_name,
original_price,
discount_rate,
original_price * (1 - discount_rate / 100) AS sale_price
FROM products;
Output:
TEXT
📖 Display only
+--------------+-------+----------+-------+
| product_name | price | quantity | total |
+--------------+-------+----------+-------+
| iPhone | 999 | 2 | 1998 |
| MacBook | 1999 | 1 | 1999 |
+--------------+-------+----------+-------+
Output:
TEXT
📖 Display only
Output displayed
4. Comparison Operators
| Operator | Description | Example |
|---|---|---|
= |
equals | WHERE id = 1 |
<> or != |
Not equal to | WHERE status != 'deleted' |
< |
Less than | WHERE age < 18 |
> |
Greater than | WHERE price > 100 |
<= |
Less than or equal to | WHERE score <= 60 |
>= |
Greater than or equal to | WHERE amount >= 1000 |
| Scope | BETWEEN |
WHERE age BETWEEN 18 AND 30 |
IN |
List | WHERE status IN ('active', 'pending') |
LIKE |
Fuzzy match | WHERE name LIKE 'J%' |
IS NULL |
Is null | WHERE email IS NULL |
IS NOT NULL |
Is not null | WHERE email IS NOT NULL |
<=> |
Safe equals | NULL <=> NULL → 1 |
▶ Example: BETWEEN Range Query
SQL
-- Check prices between 100 and 500
SELECT * FROM products WHERE price BETWEEN 100 AND 500;
-- Equivalent to
SELECT * FROM products WHERE price >= 100 AND price <= 500;
-- Query orders within a specific date range
SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-06-30';
Output:
TEXT
📖 Display only
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
▶ Example: IN List Query
SQL
-- Query Orders with a Specific Status
SELECT * FROM orders WHERE status IN ('pending', 'paid', 'shipped');
-- Equivalent to
SELECT * FROM orders WHERE status = 'pending' OR status = 'paid' OR status = 'shipped';
-- Search for employees in a specific department
SELECT * FROM employees WHERE department_id IN (1, 3, 5, 7);
Output:
TEXT
📖 Display only
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
▶ Example: LIKE Fuzzy Matching
SQL
-- % Match any number of characters
SELECT * FROM users WHERE name LIKE 'J%'; -- Starts with J
SELECT * FROM users WHERE name LIKE '%son'; -- Ends with son
SELECT * FROM users WHERE name LIKE '%john%'; -- Contains john
-- _ Match a single character
SELECT * FROM users WHERE phone LIKE '138____1111'; -- Positions 4-7 can be any digit
-- ESCAPE Escape
SELECT * FROM files WHERE name LIKE '%\%%' ESCAPE '\\'; -- Contains %
Output:
TEXT
📖 Display only
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
+-------------+
| NULL = NULL |
+-------------+
| value |
+-------------+
1 row in set
+---------------+
| NULL <=> NULL |
+---------------+
| value |
+---------------+
1 row in set
▶ Example: Comparing NULL Values
SQL
-- NULL cannot be compared with =
SELECT * FROM users WHERE email = NULL; -- Wrong, returns null
SELECT * FROM users WHERE email IS NULL; -- Correct
-- NULL cannot be compared with !=
SELECT * FROM users WHERE email != NULL; -- Wrong
SELECT * FROM users WHERE email IS NOT NULL; -- Correct
-- Safe equals <=>
SELECT NULL = NULL; -- NULL (Uncertain)
SELECT NULL <=> NULL; -- 1 (Determines if equal)
Output:
TEXT
📖 Display only
Output displayed
5. Logical Operators
| Operator | Description | Example |
|---|---|---|
AND |
Logical AND | WHERE a = 1 AND b = 2 |
OR |
Logical OR | WHERE a = 1 OR b = 2 |
NOT |
Logical NOT | WHERE NOT (status = 'deleted') |
XOR |
Exclusive OR | WHERE a = 1 XOR b = 2 |
▶ Example: AND/OR Combination
Output:
TEXT
📖 Display only
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
SQL
-- AND: Both conditions must be met
SELECT * FROM employees
WHERE department = 'Engineering' AND salary > 8000;
-- OR: Meets any one of the conditions
SELECT * FROM employees
WHERE department = 'Sales' OR department = 'Marketing';
-- Combination: Note priority (AND > OR)
SELECT * FROM employees
WHERE (department = 'Sales' OR department = 'Marketing')
AND salary > 5000;
Output:
TEXT
📖 Display only
Output displayed
6. Bitwise Operators
| Operator | Description | Example |
|---|---|---|
& |
Bitwise AND | SELECT 5 & 3; → 1 |
| |
Bitwise OR | SELECT 5 | 3; → 7 |
^ |
Bitwise XOR | SELECT 5 ^ 3; → 6 |
~ |
Bitwise NOT | SELECT ~5; → -6 |
<< |
Left Shift | SELECT 1 << 3; → 8 |
>> |
Right Shift | SELECT 8 >> 2; → 2 |
7. Operator Precedence
From highest to lowest:
| Priority | Operator |
|---|---|
| 1 | ! (NOT) |
| 2 | - (negative sign), ~ (not) |
| 3 | ^ (XOR) |
| 4 | *, /, DIV, %, MOD |
| 5 | +, - |
| 6 | <<, >> |
| 7 | & |
| 8 | | |
| 9 | =, <=>, <>, !=, <, <=, >, >=, LIKE, IN, BETWEEN |
| 10 | AND, && |
| 11 | OR, ||, XOR |
💡 Tip: If you're unsure about the priority, use brackets
() to specify the order of execution.
❓ FAQ
Q What is the difference between
= and <=>?A
= returns NULL when compared to NULL, while <=> returns 1 (indicating equality) when compared to NULL. <=> is a MySQL-specific safe equality operator.Q Which is faster, LIKE or REGEXP?
A LIKE is faster (it can use an index), while REGEXP is more flexible (it uses regular expressions). Use LIKE for simple matches.
Q What is the precedence of AND and OR?
A AND has higher precedence than OR.
WHERE a=1 OR b=2 AND c=3 is equivalent to WHERE a=1 OR (b=2 AND c=3). It is recommended to use parentheses for clarity.Q What is the result of an operation involving NULL?
A Any operation involving NULL results in NULL.
1 + NULL = NULL, 'abc' || NULL = NULL.📖 Summary
- Arithmetic operators are used for numerical calculations:
+−×÷ DIV % - Comparison operators are used for conditional filtering:
= / != / < / > / BETWEEN / LIKE / IN / IS NULL - Logical Operators: Combining Conditions:
AND / OR / NOT / XOR - Bitwise Operators: Operate on binary bits:
& / | / ^ / ~ / << / >> - NULL: The result of an operation involving NULL is NULL; use
IS NULLor<=>for comparisons. - Use parentheses when the priority is uncertain
📝 Exercises
-
Basic Problem (Difficulty ⭐): Write a query to find products with a selling price between 100 and 500, a stock greater than 0, and a name that contains "phone."
-
Advanced Problem (Difficulty ⭐⭐): Write a query to find orders from the past 7 days with an amount greater than 200, a status of 'paid' or 'shipped,' and a non-empty customer email address.
-
Challenge (Difficulty: ⭐⭐⭐): Design a permission system that uses bitwise operations to store and check a user’s read, write, and execute permissions.
