sql

name	sql
description	SQL/MyBatis rules: JOIN minimization, index-aware predicates, dynamic-SQL safety, contract alignment.

• Shared examples and formatting reference: references/EXAMPLE.md.
• Keep new guidance, snippets, and edits aligned with that file.

---

Scope

Use this rule when:

• editing SQL queries
• changing MyBatis mapper XML or mapper interfaces
• validating dynamic SQL branches and returned row shapes

---

Layering Boundary

• SQL is for data retrieval and persistence only.
• Business logic and utility processing belong in Java (see java skill).
• Do not implement format conversion, string manipulation, conditional branching, calculation, or code mapping inside queries.
• SQL functions are allowed only for simple display-level transformations, not business rules.

---

Core Rules

• Treat the mapper interface and XML as one contract; verify parameter shape and result shape together.
• Review dynamic branches carefully — null, empty, and optional fragments included.
• Preserve aliases, column names, and resultMap behavior when callers depend on them.
• A syntactically valid query is not automatically safe — validate semantics on realistic data.

---

Performance Rules

• Minimize JOINs. Prefer subqueries, EXISTS, or single-table lookups over unnecessary joins.
• SELECT * is forbidden. List only required columns explicitly.
• Place index-friendly predicates first in WHERE.
• Avoid function-wrapped column predicates such as WHERE UPPER(COL) = ....
• Always consider paging or LIMIT for large result sets.
• If OR disables index usage, consider splitting with UNION ALL.
• Design queries to avoid full scans, temporary tables, and file sorting.
• Do not include unnecessary columns in GROUP BY.
• If the same table is accessed repeatedly, consider a CTE or temporary result set.
• Avoid HAVING for row-level filters; non-aggregate conditions belong in WHERE.

---

Refactoring Guardrails

• Preserve result semantics first; performance is secondary.
• Split readability refactors and performance refactors into separate changes when possible.
• Change one aspect at a time so regressions remain traceable.
• Verify on realistic parameter combinations, not only the happy path.
• Confirm before-and-after equivalence on: row count, duplicates, sort order, NULL semantics, date boundaries, outer-join preservation, and aggregate results.

Common traps to avoid:

• function applied to indexed column in WHERE
• broad OR across different indexed columns
• DISTINCT masking join duplication
• LEFT JOIN with right-table filter in WHERE
• large OFFSET pagination
• search-type branching via CASE or IF() on columns
• aggregation after a 1:N join without row-duplication check

---

MyBatis Rules

• In <if test>, check for the empty string before the null check.
• Use dynamic SQL (<if>) instead of CASE/IF() on columns. Branch-per-shape preserves index usage.
• Do not create <sql> snippets unless explicitly requested. If asked, use them only for genuinely repeated common clauses or contract-required separations.

---

Formatting

• Use ALL CAPS for SQL keywords, table names, and column names.
• Each major clause on its own left-aligned line.
• Trailing commas in selected columns, grouped columns, and ordering expressions.
• JOIN ... ON ... AND ... on a single line.
• All WHERE predicates on a single continuous line — no leading AND/OR.
• Always write AS explicitly for both table and column aliases.
• Never reuse the same alias within a single query.
• Use sequential aliases with role prefixes when useful: T1 (tables), S1 (subqueries), G1 (group/aggregate subqueries), X1 (reference/code tables), C1 (CTEs), V1 (views).
• Never use vertical alignment.
• Exactly one space around binary operators and around infix AND / OR.
• Compact standard style — no unnecessary padding.

---

Validation Focus

• parameter binding shape
• result column and alias stability
• dynamic branch behavior
• caller assumptions about row or object shape
• execution plan: full scans, temporary tables, file sorting
• JOIN count and whether any unnecessary JOIN remains
• EXPLAIN output: access type, key used, estimated rows, Extra field
• edge-case parameter sets: empty, skewed, boundary, high-volume