Text-to-Sql

Text-to-SQL is the specific NLP task of converting natural language questions into SQL queries that can be executed against a relational database to retrieve answers — it is the most widely studied form of executable semantic parsing and a cornerstone of natural language interfaces to databases (NLIDB).

Text-to-SQL vs. General SQL Generation

• Text-to-SQL typically refers to the academic/research task with standardized benchmarks, formal evaluation, and systematic approaches.
• The terms are often used interchangeably, but text-to-SQL emphasizes the parsing and translation aspect — understanding the linguistic structure of the question and mapping it to SQL constructs.

The Text-to-SQL Pipeline

1. Question Analysis: Parse the natural language question — identify entities, conditions, aggregations, ordering, and grouping.
2. Schema Linking: Map question terms to database schema elements:
- "employees" → `employees` table
- "salary above 100k" → `WHERE salary > 100000`
- "department" → `departments.name` (via JOIN)
3. SQL Sketch Generation: Determine the SQL structure — SELECT...FROM...WHERE...GROUP BY...ORDER BY...HAVING.
4. SQL Completion: Fill in the sketch with specific tables, columns, values, and operators.
5. Verification: Check that the generated SQL is syntactically valid and semantically reasonable.

Text-to-SQL Benchmarks

• Spider: The most widely used benchmark — 10,181 questions across 200 databases in 138 domains. Tests cross-database generalization.
• WikiSQL: 80,654 questions on 24,241 Wikipedia tables — simpler queries (single table, no JOINs).
• BIRD: A newer benchmark with real-world databases and more challenging questions.
• SParC/CoSQL: Multi-turn conversational text-to-SQL — context-dependent questions in dialogue.

Text-to-SQL Difficulty Levels

• Easy: Single table, simple WHERE clause — "List all employees in marketing."
• Medium: JOIN operations, aggregations — "Average salary by department."
• Hard: Subqueries, GROUP BY + HAVING, multiple JOINs — "Departments where average salary exceeds the company average."
• Extra Hard: Nested subqueries, CTEs, set operations — "Employees who earn more than every employee in their department hired after them."

Modern Text-to-SQL Approaches

• LLM-Based (Current SOTA): Use large language models with schema-aware prompting:
• Provide full schema in the prompt.
• Include few-shot examples of similar queries.
• Use self-correction: execute the query, check for errors, regenerate if needed.
• Achieve 85%+ execution accuracy on Spider.

• Fine-Tuned Models: Specialized models (e.g., based on T5, CodeLlama) fine-tuned on text-to-SQL datasets.
• Schema Encoding: Specialized architectures that encode the database schema structure (tables, columns, foreign keys) alongside the question.

Key Techniques

• Schema Linking: The most critical step — correctly mapping natural language terms to schema elements determines success or failure.
• Self-Consistency: Generate multiple SQL candidates and verify through execution — pick the consistent result.
• Error Correction: Execute the generated SQL, catch errors, and use the error message to regenerate.
• Decomposition: Break complex questions into sub-questions, generate SQL for each, then combine.

Text-to-SQL is a mature and rapidly advancing field — modern LLM-based approaches have made it practical for real-world deployment, bringing natural language database access closer to reality for millions of users.