00:00

Help Us Improve

We're sorry this tutorial wasn't helpful. Please tell us what we can do better.

MySQL Operators

Last updated:

An operator is a symbol or keyword that works on values: + adds, > compares, AND combines conditions. You use them in SELECT lists, WHERE clauses, UPDATE assignments and everywhere else an expression is allowed. This page walks through every group with runnable examples.

1. Operator Groups

Groups of MySQL operatorsFive groups: arithmetic (+ - * / DIV %), comparison (= <> < > <= >= <=> BETWEEN IN LIKE IS NULL), logical (AND OR NOT XOR), bitwise (& | ^ ~ << >>) and others (:= assignment, -> and ->> for JSON).Arithmetic+ − * /DIV % (MOD)Comparison= <> < > <= >=<=> BETWEEN INLIKE IS NULLLogicalAND ORNOT XORBitwise& | ^ ~<< >>Other:= (assign)-> ->> (JSON)
Figure 1: MySQL's operators fall into five groups.

Table: items (4 rows)

idnamepriceqtydiscount_pctflagsspecs
1Notebook60.0025NULL5{"pages": 200, "ruled": true}
2Pen15.00120101{"colour": "blue"}
3Backpack1450.007206{"laptop": true, "litres": 25}
4Stapler180.000NULL0{}

2. Arithmetic Operators

OperatorMeaningExampleResult
+Add7 + 29
-Subtract7 - 25
*Multiply7 * 214
/Divide (decimal result)7 / 23.5000
DIVInteger division7 DIV 23
% / MODRemainder7 % 21
query.sql 
SELECT 7 + 2 AS a, 7 / 2 AS b, 7 DIV 2 AS c, 7 % 2 AS d, -7 % 2 AS e, 7 / 0 AS div_by_zero;

Result: 1 row

abcdediv_by_zero
93.500031-1NULL

Division by zero returns NULL (with a warning) in a SELECT. In real queries:

arithmetic.sql 
SELECT name, price, qty,
       price * qty                             AS stock_value,
       price - price * COALESCE(discount_pct, 0) / 100 AS sale_price,
       qty DIV 12                              AS full_dozens,
       qty % 12                                AS loose_items
FROM items;

Result: 4 rows

namepriceqtystock_valuesale_pricefull_dozensloose_items
Notebook60.00251500.0060.00000021
Pen15.001201800.0013.500000100
Backpack1450.00710150.001160.00000007
Stapler180.0000.00180.00000000

3. Comparison Operators

OperatorMeaning
=Equal
<>, !=Not equal
<, >, <=, >=Less / greater (or equal)
<=>NULL-safe equal: NULL <=> NULL is 1
BETWEEN a AND bInclusive range
IN (…)Equals any value in a list
LIKE, REGEXPPattern match
IS [NOT] NULLNULL test
IS [NOT] TRUE/FALSEBoolean test

Comparisons return 1 (true), 0 (false) or NULL (unknown):

query.sql 
SELECT 5 > 3 AS gt, 'abc' = 'ABC' AS ci_equal, NULL = NULL AS eq_null, NULL <=> NULL AS null_safe, 3 BETWEEN 1 AND 3 AS btw, 2 IN (1, 2, 3) AS in_list;

Result: 1 row

gtci_equaleq_nullnull_safebtwin_list
11NULL111
query.sql 
SELECT name, price FROM items WHERE price BETWEEN 50 AND 500 AND name <> 'Pen';

Result: 2 rows

nameprice
Notebook60.00
Stapler180.00
query.sql 
SELECT name, discount_pct FROM items WHERE discount_pct <=> NULL;   -- NULL-safe: finds the NULLs

Result: 2 rows

namediscount_pct
NotebookNULL
StaplerNULL

4. Logical Operators

query.sql 
SELECT TRUE AND NULL AS and_null, FALSE AND NULL AS false_and_null, TRUE OR NULL AS or_null, NOT NULL AS not_null, TRUE XOR FALSE AS xor_result;

Result: 1 row

and_nullfalse_and_nullor_nullnot_nullxor_result
NULL01NULL1
query.sql 
SELECT name FROM items WHERE (price < 100 OR qty = 0) AND NOT name LIKE 'P%';

Result: 2 rows

name
Notebook
Stapler

See AND, OR, NOT and AND with OR.

5. Bitwise Operators

Bitwise operators work on the binary form of integers. A common use is storing several yes/no flags in one number: here bit 1 = on sale, bit 2 = bestseller, bit 4 = eco-friendly:

query.sql 
SELECT 6 & 3 AS bit_and, 6 | 3 AS bit_or, 6 ^ 3 AS bit_xor, 1 << 3 AS shift_left, 16 >> 2 AS shift_right, BIN(6) AS binary_6;

Result: 1 row

bit_andbit_orbit_xorshift_leftshift_rightbinary_6
27584110
flags.sql 
SELECT name, flags, BIN(flags) AS bits,
       (flags & 1) > 0 AS on_sale,
       (flags & 2) > 0 AS bestseller,
       (flags & 4) > 0 AS eco
FROM items;

Result: 4 rows

nameflagsbitson_salebestsellereco
Notebook5101101
Pen11100
Backpack6110011
Stapler00000

6. String Concatenation

In MySQL || means OR (unless the PIPES_AS_CONCAT SQL mode is on), so join strings with CONCAT() or CONCAT_WS():

query.sql 
SELECT 'a' || 'b' AS pipes_is_or, CONCAT('Note', 'book') AS concat, CONCAT_WS(' - ', name, price) AS concat_ws FROM items WHERE id = 1;

Result: 1 row

pipes_is_orconcatconcat_ws
0NotebookNotebook - 60.00

7. Assignment Operators

= assigns in SET and UPDATE … SET; := assigns to a user variable anywhere:

variables.sql 
SET @tax_rate = 18;
SELECT name, price, ROUND(price * (1 + @tax_rate / 100), 2) AS with_tax FROM items WHERE id <= 2;

Result: 2 rows

namepricewith_tax
Notebook60.0070.80
Pen15.0017.70
assign.sql 
UPDATE items SET qty = qty + 50 WHERE id = 4;
SELECT name, qty FROM items WHERE id = 4;

Result: 1 row

nameqty
Stapler50

8. JSON Operators -> and ->>

col->'$.path' extracts a JSON value; ->> also removes the quotes:

query.sql 
SELECT name, specs->'$.colour' AS with_quotes, specs->>'$.colour' AS unquoted, specs->>'$.pages' AS pages FROM items WHERE id IN (1, 2);

Result: 2 rows

namewith_quotesunquotedpages
NotebookNULLNULL200
Pen"blue"blueNULL
query.sql 
SELECT name FROM items WHERE specs->'$.laptop' = TRUE;

Result: 1 row

name
Backpack

9. Operator Precedence

Priority (high → low)Operators
1!
2- (unary minus), ~
3^
4*, /, DIV, %
5-, +
6<<, >>
7&
8|
9Comparisons: =, <=>, >=, LIKE, IN, IS …
10BETWEEN, CASE
11NOT
12AND
13XOR
14OR
15 (lowest)= (assignment), :=
query.sql 
SELECT 2 + 3 * 4 AS no_parens, (2 + 3) * 4 AS with_parens, 1 + 2 = 3 AS comparison_last;

Result: 1 row

no_parenswith_parenscomparison_last
14201
When in doubt, add parentheses. They cost nothing and make the intent obvious.

10. Automatic Type Conversion

MySQL converts types to make an operation work, which can surprise you:

query.sql 
SELECT '10' + 5 AS string_plus_number, '3 apples' + 2 AS leading_number, 'abc' = 0 AS text_vs_zero, '2026-03-14' + 0 AS date_text_plus_zero;

Result: 1 row

string_plus_numberleading_numbertext_vs_zerodate_text_plus_zero
15512026
Comparing a string column with a number makes MySQL convert every value, which can give wrong matches and prevents index use. Compare values of the same type.

11. Summary

  • Arithmetic: + − * / DIV %; division by zero gives NULL.
  • Comparison: = <> < > <= >= <=> BETWEEN IN LIKE IS NULL; results are 1, 0 or NULL.
  • Logical: AND, OR, NOT, XOR; bitwise: & | ^ ~ << >>.
  • Use CONCAT for strings (|| is OR); -> and ->> read JSON.
  • Know the precedence, and use parentheses.