MySQL function syntax: (1) String: CONCAT(str1, str2), SUBSTRING(str, pos, len), REPLACE(str, from, to), UPPER(str), LOWER(str), LENGTH(str). (2) Math: ROUND(num, decimals), CEIL(num), FLOOR(num), ABS(num), MOD(num, divisor), POWER(base, exp). (3) Date: DATE_FORMAT(date, format), DATE_ADD(date, INTERVAL n unit), DATE_SUB(date, INTERVAL n unit), DATEDIFF(date1, date2), NOW(). (4) Aggregate: COUNT(*), SUM(col), AVG(col), MIN(col), MAX(col) with GROUP BY. (5) NULL: COALESCE(val1, val2, ...), IFNULL(val, default).

String: CONCAT(str1, str2) joins, SUBSTRING(str, pos, len) extracts, REPLACE(str, from, to) substitutes

Case: UPPER(str) uppercase, LOWER(str) lowercase, use generated columns for case-insensitive search

Math: ROUND(num, decimals) rounds, CEIL(num) rounds up, FLOOR(num) rounds down, ABS(num) absolute

Date: DATE_FORMAT(date, format) formats, DATE_ADD(date, INTERVAL n unit) adds, DATEDIFF(date1, date2) difference

Aggregate: COUNT(*) counts, SUM(col) sums, AVG(col) averages, MIN(col)/MAX(col) extremes with GROUP BY

NULL: COALESCE(val1, val2, ...) first non-NULL, IFNULL(val, default) replaces NULL

Performance: Functions in WHERE prevent indexes, use range comparisons or generated columns instead

CONCAT(), DATE_FORMAT(), IFNULL(), DATE_ADD(date, INTERVAL n unit), SUBSTRING(str, pos, len)

CONCAT() or ||, TO_CHAR(), COALESCE(), date + INTERVAL 'n unit', SUBSTRING(str FROM pos FOR len)

CONCAT() or +, FORMAT(), ISNULL() or COALESCE(), DATEADD(unit, n, date), SUBSTRING(str, pos, len)

CONCAT() or ||, TO_CHAR(), NVL() or COALESCE(), date + INTERVAL 'n' unit, SUBSTR(str, pos, len)

Core references in this topic include WHERE, =, <, >, <=, >=. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

=

Returns rows where the left and right values are exactly equal.

column = value

Use with exact matches. Do not use = NULL.

<, >, <=, >=

Range comparison operators for less-than, greater-than, and inclusive boundary checks.

salary >= 80000

IS NULL / IS NOT NULL

Tests whether a value is missing. SQL NULL semantics require dedicated NULL predicates.

manager_id IS NULL

Never use = NULL or != NULL.

BETWEEN

Checks whether a value falls inside an inclusive lower/upper range.

order_total BETWEEN 100 AND 500

LIKE

Pattern-matching operator for wildcard string searches.

name LIKE 'Joh%'

ANY / ALL

Compares one value against every or at least one value from a subquery result.

salary > ALL (SELECT salary FROM interns)

DATE

Stores a calendar date without any time-of-day component.

DATE '2026-04-18'