➗SQL operators

Precedence is the silent bug

AND binds tighter than OR. Mixed logical expressions without parentheses are a code review smell, future edits insert an AND line and accidentally narrow only half of what you meant. Parentheses cost nothing and read as intent.

📋Operator classes you touch every day

Arithmetic and string ops inherit types; comparisons interact with NULL in non-boolean ways.

Class	Operators	Practical note
Arithmetic	+, -, *, /, %	Integer division truncates toward zero on many engines, cast to NUMERIC before dividing money.
Comparison	=, <>, !=, <, >, <=, >=	Prefer `<>` for ANSI inequality; remember `NULL` comparisons yield UNKNOWN, not FALSE.
Logical	AND, OR, NOT	Short-circuit evaluation is not guaranteed in every subexpression, do not rely on it to guard division-by-zero without checking your dialect docs.
String	\|\|, CONCAT()	Know NULL + concat rules on your engine; prefer COALESCE at boundaries when building display strings.

SQL

Arithmetic + comparison

SELECT trial_id,
       dose_mg * body_weight_kg AS dose_adjusted
FROM enrollments
WHERE (dose_mg * body_weight_kg) BETWEEN 50 AND 120;

✓Illustrative result set

trial_id	dose_adjusted
T-01	72.5
T-03	98

SQL

NULL-aware comparisons (three-valued logic)

SELECT patient_id, biomarker_a, biomarker_b
FROM labs
WHERE biomarker_a = biomarker_b;  -- excludes rows where either side is NULL

✓Illustrative result set

patient_id	biomarker_a	biomarker_b
501	12.1	12.1
503	8	8

WHERE status = NULL

= NULL is never TRUE, it is UNKNOWN, and WHERE drops UNKNOWN rows. Your “fix null bugs” query returns empty and confuses everyone.

WHERE status IS NULL

Use IS NULL / IS NOT NULL. For nullable join keys, consider IS NOT DISTINCT FROM on engines that support it.

⚡ Advanced / dialect note

NULL = NULL is not TRUE, it is UNKNOWN. Use IS NULL, IS NOT DISTINCT FROM (PostgreSQL), or COALESCE patterns instead of = NULL.

Code review prompt

For any non-trivial WHERE, ask: “If I De Morgan this expression, does NULL behavior still match the protocol?”: three-valued logic punishes hand-wavy reasoning.

TL;DR

SQL operators combine values: arithmetic (+ - * /), comparison (= <> < >), string concat (|| or CONCAT), and pattern (LIKE). Precedence and NULL rules matter, when in doubt, parenthesize. Mixed types invoke implicit casts that can surprise you and block indexes; prefer explicit CAST or aligned schema types.