Operators

SQL operators, by category (and a myth to drop)

Operators combine columns and literals inside expressions — in SELECT, WHERE, SET, HAVING. MySQL's real operators:

Category	Operators	Note
Arithmetic	+ - * / %, DIV	/ is decimal division; DIV is integer division
Comparison	= <> != < > <= >=, <=>	<=> is NULL-safe equality
Logical	AND OR NOT XOR	three-valued with NULL
Set/range/pattern	IN, BETWEEN, LIKE, IS [NOT] NULL	predicates used in WHERE
Bitwise	& | ^ ~ << >>	integer bit operations

Correction — there are no compound-assignment operators in MySQL. +=, -=, *=, /= are T-SQL (SQL Server) syntax. In MySQL you assign with plain = in UPDATE … SET col = col + 1, and for a session variable you use := (or = inside SET): SET @x := 5; — SET @x = 5 also works in a SET statement, but @x += 3 does not exist.

-- increment a column the portable way (works in MySQL, Postgres, SQLite, SQL Server):
UPDATE accounts SET balance = balance + 100 WHERE id = 42;

-- SQL Server ONLY (T-SQL compound assignment) — not MySQL:
-- UPDATE accounts SET balance += 100 WHERE id = 42;

Mental model: three-valued logic (not boolean)

Every predicate evaluates to TRUE, FALSE, or UNKNOWN. WHERE keeps only TRUE rows — UNKNOWN is dropped (same as FALSE for filtering, different for CHECK constraints and unique indexes in subtle ways).

P	Q	P AND Q	P OR Q	NOT P
T	T	T	T	F
T	F	F	T	F
T	U	U	T	F
F	F	F	F	T
F	U	F	U	T
U	U	U	U	U

-- NULL = 5     → UNKNOWN  (row filtered out)
-- NULL <> 5    → UNKNOWN  (row filtered out)  ← "not equal" does NOT keep NULLs
-- NULL <=> 5   → FALSE    (MySQL NULL-safe)
-- NULL <=> NULL → TRUE
-- col IS NULL  → TRUE/FALSE only (never UNKNOWN)

Drill: WHERE department_id <> 3 drops rows where department_id IS NULL. For "not department 3, including unknown dept" use department_id IS DISTINCT FROM 3 (Postgres) or (department_id <> 3 OR department_id IS NULL).

BETWEEN is inclusive on both ends

WHERE score BETWEEN 1 AND 10
-- means: score >= 1 AND score <= 10   (both endpoints included)

-- half-open ranges for dates/timestamps (preferred):
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01'

Inclusive BETWEEN on a timestamp upper bound often accidentally includes the first instant of the next day if you pass a date that coerces to midnight. Prefer half-open [start, end) ranges.

LIKE wildcards and sargability

• % = any string (including empty); _ = exactly one character.
• LIKE 'foo%' can use a B+tree index (prefix range). LIKE '%foo' or LIKE '%foo%' generally cannot — leading wildcard forces a scan (or a trigram/GIN index if you built one).
• LIKE is collation/case-sensitive depending on engine and column collation; ILIKE is Postgres case-insensitive.

IN and three-valued surprises

x IN (1, 2, NULL)     -- TRUE if x is 1 or 2; UNKNOWN if x is 3; UNKNOWN if x is NULL
x NOT IN (1, 2, NULL) -- UNKNOWN for every non-null x that is not 1/2 — WHERE drops the row
                      -- if the list (or subquery) contains NULL, NOT IN often returns no rows

Prefer NOT EXISTS / anti-join over NOT IN when the set can contain NULL.

Takeaways

• MySQL operator families: arithmetic, comparison (incl. NULL-safe <=>), logical, pattern/range, bitwise.
• Predicates are three-valued; WHERE keeps only TRUE. <> does not retain NULLs.
• BETWEEN is inclusive both ends; use half-open ranges for time.
• LIKE 'prefix%' can be sargable; leading % is not (without special indexes).
• += & friends are SQL Server, not MySQL. Use SET col = col + n.
• / vs DIV: decimal vs integer division — a real cross-engine trap.

---

Re-authored for correctness and depth for this guide (T-SQL compound operators correction retained; three-valued truth tables, BETWEEN inclusivity, LIKE sargability, IN/NULL traps added). Per the MySQL operator reference and ISO SQL three-valued logic. See also: Handle NULLs, UPDATE, WHERE Clause.

🎯 STRICT STANDOUT: Why / mental model / when-not / worked / failure / hostile panel — Operators

Why this concept exists (judgment layer)

Operators look trivial until three-valued logic, BETWEEN inclusivity, LIKE sargability, and NOT IN+NULL wipe rows silently. This is Stripe-seat drill territory: correctness under NULL.

Mental model (install this intuition)

Predicates are TRUE/FALSE/UNKNOWN; WHERE keeps only TRUE. NULL comparisons yield UNKNOWN. Set ops and IS NULL are the escapes. MySQL has no += (that is T-SQL); portable update is col = col + n.

Worked example with numbers or traced steps

department_id: 1, 3, NULL
WHERE department_id <> 3  → only row 1 (NULL dropped)
WHERE department_id NOT IN (1, NULL) → empty (UNKNOWN for every candidate)
score BETWEEN 1 AND 10 → score >= 1 AND score <= 10
created_at BETWEEN '2024-01-01' AND '2024-02-01' risks including Feb 1 00:00
Prefer: >= start AND < next_period
LIKE 'foo%' sargable; LIKE '%foo' usually not

When NOT to use / named alternative

Do not use NOT IN against a nullable subquery — use NOT EXISTS / anti-join. Do not use BETWEEN for half-open time ranges. Do not wrap indexed columns in functions for case fold if collation already handles it.

Failure mode & ops fingerprint

Fingerprint: report missing NULL departments; nightly job deletes zero rows with NOT IN (SELECT id …) because subquery had NULL; date BETWEEN double-counts midnight boundary; index unused due to leading-wildcard LIKE.

Hostile-panel drills (defend the decision)

Q1. Why does WHERE col <> 3 drop NULL cols?
Model answer: NULL <> 3 is UNKNOWN; WHERE retains only TRUE rows.

Q2. MySQL update that increments balance portably?
Model answer: UPDATE t SET balance = balance + 100 WHERE id = ? — not balance += 100.

Q3. Is BETWEEN inclusive?
Model answer: Yes on both ends. For timestamps prefer half-open [start, end).

I just read a lesson on **Operators** (Databases) and want to truly understand it. Explain Operators from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Operators** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Operators** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Operators** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.