Operators

This page covers expression operators and commonly used control-flow and conversion functions. Precedence follows common SQL conventions: arithmetic precedes comparison, and comparison precedes logical operations. Use parentheses when in doubt.

Arithmetic

Operator	Meaning
+ - * /	Addition, subtraction, multiplication, and division
DIV	Integer division
% / MOD	Remainder
Unary -	Negation

Comparison

Operator	Meaning
= <> / != < <= > >=	Equality and inequality comparisons
BETWEEN ... AND ... / NOT BETWEEN	Range test
IN (...) / NOT IN	Set membership
IS / IS NOT	Boolean comparison
IS NULL / IS NOT NULL / ISNULL()	NULL test
LIKE / NOT LIKE	Pattern matching. Case sensitivity follows engine configuration.
ILIKE	Case-insensitive pattern matching
COALESCE()	Returns the first non-NULL argument

Comparing NULL with any value by using = never produces TRUE. Use IS NULL to test for NULL.

Logical

Operator	Meaning
AND / &&	AND
OR	OR
XOR	Exclusive OR
NOT / !	NOT

Bitwise

Operator	Meaning
& | ^	Bitwise AND / OR / XOR
<< >>	Left / right shift
~	Bitwise NOT

Assignment

Operator	Meaning
=	Assignment in contexts such as SET. It has the same form as comparison =, and context determines the meaning.

Type conversion

Function / operator	Meaning
CAST(expr AS type)	Explicit conversion
CONVERT(expr, type)	Explicit conversion, commonly used for date-time and decimal values
BINARY	Converts to binary-string semantics
ENCODE / DECODE / SERIAL / SERIAL_FULL, and others	Encoding and serialization operations, subject to version support

Control flow

Form	Meaning
CASE WHEN ... THEN ... ELSE ... END	Multiple branches
IF(cond, a, b)	Selects one of two values based on a condition
IFNULL(a, b)	Returns b when a is NULL
NULLIF(a, b)	Returns NULL when a equals b; otherwise returns a

INTERVAL

Date operations use INTERVAL to express a time interval, together with DATE_ADD / DATE_SUB or literal operations. Units include the common set of DAY, HOUR, MINUTE, SECOND, MONTH, and YEAR.

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);