sql generation

SQL generation (also known as NL2SQL or text-to-SQL) is the AI task of automatically converting natural language questions into syntactically and semantically correct SQL queries — enabling non-technical users to query databases using plain English instead of writing SQL code.

Why SQL Generation Matters

• SQL is powerful but technical: Writing correct SQL requires understanding of table schemas, JOIN operations, aggregations, subqueries, and database-specific syntax.
• Most data consumers aren't SQL experts: Business analysts, managers, and domain experts have questions about their data but often can't express them in SQL.
• SQL generation democratizes data access — anyone who can describe what they want in natural language can get answers from a database.

How SQL Generation Works

1. Input: Natural language question + database schema (table names, column names, types, relationships).
2. Understanding: The model interprets the user's intent — what data they want, what filters to apply, what aggregations to perform.
3. Schema Linking: Maps natural language terms to specific tables and columns — "revenue" → `sales.total_amount`, "last year" → `WHERE date >= '2025-01-01'`.
4. SQL Construction: Generates a syntactically valid SQL query that expresses the user's intent.
5. Execution: The generated SQL is executed against the database.
6. Answer: Results are returned to the user, optionally with the generated SQL for transparency.

SQL Generation Example

Schema: employees(id, name, dept, salary, hire_date)
         departments(id, name, location)

Question: "What is the average salary in the
engineering department?"

Generated SQL:
SELECT AVG(e.salary)
FROM employees e
JOIN departments d ON e.dept = d.id
WHERE d.name = 'Engineering'

SQL Generation with LLMs

• Modern LLMs (GPT-4, Claude, Codex) achieve 80–90%+ execution accuracy on standard benchmarks when provided with the schema.
• Prompt Engineering: Include the full schema, example queries, and output format instructions in the prompt.
• Schema Representation: Present schemas clearly — table names, column names with types, primary/foreign key relationships, and sample values for disambiguation.

Key Challenges

• Complex Queries: Nested subqueries, CTEs, window functions, correlated subqueries — harder to generate correctly.
• Ambiguity Resolution: "Top customers" — by revenue? by order count? by most recent activity? The model must infer or ask for clarification.
• Schema Complexity: Real databases have hundreds of tables and columns — the model must identify relevant ones.
• Domain Terminology: Business terms may not match column names — "churn rate" doesn't appear in any column.
• Safety: Generated SQL should be read-only (no DELETE, UPDATE, DROP) unless explicitly authorized.

Evaluation Metrics

• Execution Accuracy: Does the generated SQL return the correct result? (Most important metric.)
• Exact Match: Does the generated SQL exactly match the gold standard? (Too strict — many equivalent queries exist.)
• Valid SQL Rate: Is the generated SQL syntactically valid and executable?

SQL generation is one of the most impactful practical applications of LLMs — it transforms natural language into precise database queries, making organizational data accessible to everyone regardless of technical skill.