jungho-git/sql

SQL

• 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
• reviewing DDL, constraints, indexes, or query plans tied to a code change

SQL changes should preserve data semantics before optimizing shape.

---

Layering

• SQL is for retrieval and persistence.
• Business logic and utility processing belong in Java or application code.
• Do not implement business format conversion, code mapping, conditional branching, or calculations in queries.
• SQL functions are acceptable only for simple display-level transformations.

---

Contract Rules

• Treat mapper interface and XML as one contract.
• Verify parameter shape, result shape, aliases, and resultMap behavior together.
• Preserve column names and aliases that callers depend on.
• Review dynamic branches for null, empty, optional, and list inputs.
• Validate realistic parameter combinations, not only the happy path.

---

Performance Rules

• Minimize joins; prefer EXISTS, subqueries, or direct lookups when clearer and equivalent.
• Do not use SELECT *.
• Keep predicates index-friendly.
• Avoid function-wrapped indexed columns in WHERE.
• Consider paging or LIMIT for large result sets.
• Consider UNION ALL when broad OR conditions disable indexes.
• Avoid HAVING for row-level filters.
• Confirm row count, duplicates, sort order, null semantics, date boundaries, outer joins, and aggregate behavior after changes.

---

MyBatis Rules

• In <if test>, check empty string before null when both are relevant.
• Use dynamic SQL branches instead of CASE or IF() on indexed columns.
• Do not create <sql> snippets unless the user asks or real repeated common clauses require them.

---

Formatting

• Use ALL CAPS for SQL keywords and local convention for identifiers unless the project already standardizes caps.
• Put each major clause on its own line.
• List selected columns explicitly.
• Write AS for table and column aliases.
• Never reuse aliases in a single query.
• Never vertically align columns, aliases, or operators.
• Use exactly one space around binary operators and infix AND/OR.

---

Validation Focus

• parameter binding shape
• result aliases and object shape
• dynamic branch behavior
• caller assumptions
• execution plan access type, key, rows, and extra fields
• unnecessary joins and full scans
• edge parameter sets: empty, skewed, boundary, high-volume