SQL Operator リファレンス
リファレンス 用 SQL operators: 比較, logical, arithmetic, 設定, および special operators.
Comparison and Logical
Comparison: =, <>, !=, <, >, <=, >=. IS NULL / IS NOT NULL for null checks (never use = NULL). Logical: AND, OR, NOT with three-valued logic (NULL propagation). BETWEEN x AND y is inclusive range. IN (list) matches any value; NOT IN excludes. EXISTS checks subquery non-emptiness. ALL/ANY/SOME compare against subquery results. NULL comparisons yield UNKNOWN, not TRUE or FALSE. Three-valued logic is a common source of bugs.
- IS NULL
- Null check
- BETWEEN
- Inclusive range
- EXISTS
- Subquery non-empty
not = NULLx BETWEEN 1 AND 10correlated checkSet and Special Operators
Set operators combine result sets: UNION (distinct), UNION ALL (with duplicates), INTERSECT (common), EXCEPT/MINUS (difference). String concatenation: || (standard) or CONCAT. Arithmetic: +, -, *, /, % (modulo, dialect-specific). DISTINCT removes duplicates. CASE expression for conditional logic (searched and simple forms). CAST for type conversion. :: (PostgreSQL) for shorthand casting. OVER for window function specification. These operators form the query building blocks.
- UNION/UNION ALL
- Combine result sets
- CASE
- Conditional logic
- CAST
- Type conversion
distinct vs allWHEN/THEN/ELSECAST(x AS INT)