SQL¶

SQL queries are production code: they shape correctness, latency, and security just as much as application logic. MCP Zen of Languages applies 9 practical SQL principles that catch fragile query patterns before they become outages, regressions, or data quality incidents.

Zen Principles¶

9 principles across 5 categories, drawn from ANSI SQL + production database best practices.

Clarity · 1 principle Correctness · 3 principles Performance · 3 principles Readability · 1 principle Security · 1 principle

Rule ID	Principle	Category	Severity	Dogma
`sql-001`	Never use SELECT *	Performance	6	`ZEN-PROPORTIONATE-COMPLEXITY`, `ZEN-EXPLICIT-INTENT`
`sql-002`	Always include INSERT column lists	Correctness	7	`ZEN-EXPLICIT-INTENT`, `ZEN-VISIBLE-STATE`
`sql-003`	Prefer parameterized SQL over dynamic concatenation	Security	9	`ZEN-STRICT-FENCES`
`sql-004`	Avoid NOLOCK and dirty reads	Correctness	8	`ZEN-EXPLICIT-INTENT`
`sql-005`	Avoid implicit type coercion in JOIN predicates	Performance	6	`ZEN-PROPORTIONATE-COMPLEXITY`, `ZEN-EXPLICIT-INTENT`
`sql-006`	Bound result sets with WHERE/LIMIT/TOP	Performance	5	`ZEN-PROPORTIONATE-COMPLEXITY`
`sql-007`	Use descriptive table aliases	Clarity	4	`ZEN-EXPLICIT-INTENT`, `ZEN-UNAMBIGUOUS-NAME`
`sql-008`	Keep transaction boundaries balanced	Correctness	8	`ZEN-EXPLICIT-INTENT`
`sql-009`	Prefer explicit JOIN syntax over ANSI-89 comma joins	Readability	6	`ZEN-UNAMBIGUOUS-NAME`, `ZEN-EXPLICIT-INTENT`

sql-001 — Never use SELECT *

Enumerate explicit columns to avoid fragile, over-fetching queries.

Universal Dogmas: ZEN-PROPORTIONATE-COMPLEXITY, ZEN-EXPLICIT-INTENT Common Violations:

SELECT * detected

Detectable Patterns:

(?i)select\s+\*\s+from

sql-002 — Always include INSERT column lists

INSERT statements without a column list break on schema changes.

Universal Dogmas: ZEN-EXPLICIT-INTENT, ZEN-VISIBLE-STATE Common Violations:

INSERT INTO table VALUES (...) without explicit columns

Detectable Patterns:

(?i)insert\s+into\s+\w+\s+values\s*\(

sql-003 — Prefer parameterized SQL over dynamic concatenation

Dynamic SQL string concatenation increases SQL injection risk.

Universal Dogmas: ZEN-STRICT-FENCES Common Violations:

EXEC/EXECUTE with concatenated SQL string

Detectable Patterns:

(?i)exec(?:ute)?\s*\(?.*['\"]\s*(?:\+|\|\|)

Recommended Fix

Use bind parameters or prepared statements instead of runtime SQL string assembly.

sql-004 — Avoid NOLOCK and dirty reads

WITH (NOLOCK) can silently read uncommitted and inconsistent data.

Universal Dogmas: ZEN-EXPLICIT-INTENT Common Violations:

NOLOCK table hint used

Detectable Patterns:

(?i)with\s*\(\s*nolock\s*\)

sql-005 — Avoid implicit type coercion in JOIN predicates

Type mismatches in JOIN conditions often force index-unfriendly scans.

Universal Dogmas: ZEN-PROPORTIONATE-COMPLEXITY, ZEN-EXPLICIT-INTENT Common Violations:

JOIN predicate applies CAST/CONVERT in comparison

Detectable Patterns:

(?i)join[\s\S]*?on[\s\S]*?(cast\s*\(|convert\s*\()

sql-006 — Bound result sets with WHERE/LIMIT/TOP

Unbounded selects on large relations are costly and often accidental.

Universal Dogmas: ZEN-PROPORTIONATE-COMPLEXITY Common Violations:

SELECT query without WHERE, LIMIT, or TOP

sql-007 — Use descriptive table aliases

Single-letter or cryptic aliases reduce readability and maintenance speed.

Universal Dogmas: ZEN-EXPLICIT-INTENT, ZEN-UNAMBIGUOUS-NAME Common Violations:

Single-letter table alias detected

sql-008 — Keep transaction boundaries balanced

BEGIN TRANSACTION without COMMIT/ROLLBACK in the same file risks dangling transactions.

Universal Dogmas: ZEN-EXPLICIT-INTENT Common Violations:

BEGIN TRANSACTION without matching COMMIT or ROLLBACK

Detectable Patterns:

(?i)\bbegin\s+tran(?:saction)?\b

sql-009 — Prefer explicit JOIN syntax over ANSI-89 comma joins

Comma-separated FROM joins are deprecated and less explicit than JOIN ... ON.

Universal Dogmas: ZEN-UNAMBIGUOUS-NAME, ZEN-EXPLICIT-INTENT Common Violations:

ANSI-89 comma join syntax detected

Detectable Patterns:

(?i)from\s+[^;]*,\s*[^;]*where

Recommended Fix

Replace comma joins with explicit INNER JOIN/LEFT JOIN and ON predicates.

Detector Catalog¶

Clarity¶

Detector	What It Catches	Rule IDs
SqlAliasClarityDetector	Flag aliases that are shorter than configured readability threshold	`sql-007`

Correctness¶

Detector	What It Catches	Rule IDs
SqlInsertColumnListDetector	Flag INSERT statements that omit explicit target columns	`sql-002`
SqlNolockDetector	Flag NOLOCK table-hint usage	`sql-004`
SqlTransactionBoundaryDetector	Ensure explicit transaction blocks are balanced	`sql-008`

Performance¶

Detector	What It Catches	Rule IDs
SqlSelectStarDetector	Flag `SELECT *` usage	`sql-001`
SqlImplicitJoinCoercionDetector	Detect join predicates that cast either side of the comparison	`sql-005`
SqlUnboundedQueryDetector	Warn on unbounded SELECT queries	`sql-006`

Readability¶

Detector	What It Catches	Rule IDs
SqlAnsi89JoinDetector	Detect deprecated ANSI-89 comma-join syntax	`sql-009`

Security¶

Detector	What It Catches	Rule IDs
SqlDynamicSqlDetector	Flag risky dynamic SQL execution via string concatenation	`sql-003`

Principle → Detector Wiring

%%{init: {"theme": "base", "flowchart": {"useMaxWidth": false, "htmlLabels": true, "nodeSpacing": 40, "rankSpacing": 60}}}%%
graph TD
sql_001["sql-001<br/>Never use SELECT *"]
sql_002["sql-002<br/>Always include INSERT col..."]
sql_003["sql-003<br/>Prefer parameterized SQL ..."]
sql_004["sql-004<br/>Avoid NOLOCK and dirty re..."]
sql_005["sql-005<br/>Avoid implicit type coerc..."]
sql_006["sql-006<br/>Bound result sets with WH..."]
sql_007["sql-007<br/>Use descriptive table ali..."]
sql_008["sql-008<br/>Keep transaction boundari..."]
sql_009["sql-009<br/>Prefer explicit JOIN synt..."]
det_SqlAliasClarityDetector["Sql Alias<br/>Clarity"]
sql_007 --> det_SqlAliasClarityDetector
det_SqlAnsi89JoinDetector["Sql Ansi<br/>89 Join"]
sql_009 --> det_SqlAnsi89JoinDetector
det_SqlDynamicSqlDetector["Sql Dynamic<br/>Sql"]
sql_003 --> det_SqlDynamicSqlDetector
det_SqlImplicitJoinCoercionDetector["Sql Implicit<br/>Join Coercion"]
sql_005 --> det_SqlImplicitJoinCoercionDetector
det_SqlInsertColumnListDetector["Sql Insert<br/>Column List"]
sql_002 --> det_SqlInsertColumnListDetector
det_SqlNolockDetector["Sql Nolock"]
sql_004 --> det_SqlNolockDetector
det_SqlSelectStarDetector["Sql Select<br/>Star"]
sql_001 --> det_SqlSelectStarDetector
det_SqlTransactionBoundaryDetector["Sql Transaction<br/>Boundary"]
sql_008 --> det_SqlTransactionBoundaryDetector
det_SqlUnboundedQueryDetector["Sql Unbounded<br/>Query"]
sql_006 --> det_SqlUnboundedQueryDetector

Detector Class Hierarchy

%%{init: {"theme": "base"}}%%
classDiagram
    direction TB
    class ViolationDetector {
        <<abstract>>
        +detect(context, config)
    }
    class det_01["Sql Alias Clarity"]
    ViolationDetector <|-- det_01
    class det_02["Sql Ansi 89 Join"]
    ViolationDetector <|-- det_02
    class det_03["Sql Dynamic Sql"]
    ViolationDetector <|-- det_03
    class det_04["Sql Implicit Join Coercion"]
    ViolationDetector <|-- det_04
    class det_05["Sql Insert Column List"]
    ViolationDetector <|-- det_05
    class det_06["Sql Nolock"]
    ViolationDetector <|-- det_06
    class det_07["Sql Select Star"]
    ViolationDetector <|-- det_07
    class det_08["Sql Transaction Boundary"]
    ViolationDetector <|-- det_08
    class det_09["Sql Unbounded Query"]
    ViolationDetector <|-- det_09

Analysis Pipeline

%%{init: {"theme": "base", "flowchart": {"useMaxWidth": false, "htmlLabels": true, "nodeSpacing": 50, "rankSpacing": 70}}}%%
flowchart TD
Source(["Source Code"]) --> Parse["Parse & Tokenize"]
Parse --> Metrics["Compute Metrics"]
Metrics --> Pipeline{"9 Detectors"}
Pipeline --> Collect["Aggregate Violations"]
Collect --> Result(["AnalysisResult<br/>9 principles"])

Analysis States

%%{init: {"theme": "base"}}%%
stateDiagram-v2
    [*] --> Ready
    Ready --> Parsing : analyze(code)
    Parsing --> Computing : AST ready
    Computing --> Detecting : metrics ready
    Detecting --> Reporting : 9 detectors run
    Reporting --> [*] : AnalysisResult
    Parsing --> Reporting : parse error (best-effort)

Configuration¶

languages:
  sql:
    enabled: true
    pipeline:
      - type: sql-001
        dialect: postgres
      - type: sql-002
        dialect: postgres
      - type: sql-003
        dialect: postgres
      - type: sql-004
        dialect: postgres
      - type: sql-005
        dialect: postgres
      - type: sql-006
        dialect: postgres
      - type: sql-007
        min_alias_length: 2
        dialect: postgres
      - type: sql-008
        dialect: postgres
      - type: sql-009
        dialect: postgres

SQL¶

Zen Principles¶

Detector Catalog¶

Clarity¶

Correctness¶

Performance¶

Readability¶

Security¶

Configuration¶

See Also¶