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.