PaperlayerView exact revision on arXiv

Source-linked AI summary

A Survey on Employing Large Language Models for Text-to-SQL Tasks

Liang Shi, Zhengju Tang, Nan Zhang, Xiaotong Zhang, Zhi Yang

arXiv:2407.15186v5cs.CL

TL;DR

Text-to-SQL lowers the barrier between non-expert users and relational databases, but LLM-based methods have rapidly diversified across prompts and finetuning. This survey organizes those methods, benchmarks, models, and analyses, and identifies schema linking as the most prominent reported error category while discussing practical constraints and benchmark limitations.

  • Problem

    Specialized SQL knowledge limits non-expert access to relational databases, while current Text-to-SQL research spans rapidly evolving methods, benchmarks, and models requiring systematic synthesis.

  • Method

    The survey develops taxonomies of prompt engineering and finetuning, reviews benchmarks and metrics, analyzes models and experimental results, and discusses challenges and future directions.

  • Results

    Schema-linking errors are the most frequent reported category, averaging 29%-49%, compared with 21%-26% near JOIN and less than 20% for GROUP BY and nested errors.

  • Takeaways & Limitations

    The survey identifies schema linking as the module most in need of improvement and highlights stronger performance from reasoning-enhanced and agent-based systems on Spider 2.0.

  • Takeaways & Limitations

    Existing benchmarks do not fully represent very large real-world databases: Spider has simple schemas, while BIRD remains too small in scale.

Abstract

from arXiv · show

With the development of the Large Language Models (LLMs), a large range of LLM-based Text-to-SQL(Text2SQL) methods have emerged. This survey provides a comprehensive review of LLM-based Text2SQL studies. We first enumerate classic benchmarks and evaluation metrics. For the two mainstream methods, prompt engineering and finetuning, we introduce a comprehensive taxonomy and offer practical insights into each subcategory. We present an overall analysis of the above methods and various models evaluated on well-known datasets and extract some characteristics. Finally, we discuss the challenges and future directions in this field.

1 Introduction

This survey examines how LLMs are being applied to Text-to-SQL, a task that helps non-experts access relational databases without specialized SQL knowledge. It organizes the field around prompt engineering and finetuning, while covering benchmarks, evaluation, models, analysis, and future directions.

  • 1 Introduction: Text-to-SQL converts natural-language questions into SQL queries, bridging non-expert users and relational databases whose use otherwise requires specialized SQL knowledge.The introduction illustrates this process with a natural-language city query translated into a SQL query.
  • 1 Introduction: Earlier Text-to-SQL systems progressed from manually intensive template- and rule-based methods toward Seq2Seq approaches as deep learning advanced.The supplied historical passage identifies templates and rules as initial approaches and Seq2Seq methods as the later mainstream approach.
  • 1 Introduction: LLM-based Text-to-SQL research mainly follows prompt engineering or finetuning as models and training data scale.The survey attributes growing interest to LLM scaling and emergent capabilities.
  • 1 Introduction: The survey comprehensively reviews LLM-based Text-to-SQL methods, benchmarks, evaluation metrics, models, experimental analyses, challenges, and future directions.Its stated coverage includes overview, benchmark and metric summaries, taxonomies of prompt engineering and finetuning, model analysis, and future directions.

2 Overview

The overview contrasts traditional trained architectures with LLM-based approaches that can use prompting and instruction following, and it traces the field’s evolution toward stronger performance. It frames prompt engineering and finetuning as the two main application streams.

  • 2 Overview: LLMs generate text progressively from prompts and exhibit few-shot learning and instruction-following abilities that support prompt-based Text-to-SQL.Few-shot learning uses task demonstrations, while instruction following enables responses to instructions describing unseen tasks.
  • 2 Overview: LLM-based Text-to-SQL methods are categorized into prompt engineering and finetuning, reflecting the two primary streams for applying LLMs to downstream tasks.Prompt engineering includes retrieval augmentation, few-shot learning, and reasoning, while finetuning adapts models through training.
  • 2 Overview: Traditional Text-to-SQL systems require training, whereas LLMs can often perform the task through instructions and supplied information without that requirement.The survey presents this as a novel paradigm distinguishing LLM-based from traditional approaches.
  • 2 Overview: The survey reports that LLM-based methods significantly improved SPIDER test-set execution accuracy and offer transferability across settings without additional training.It attributes the latter capability to instruction following and in-context learning.

3 Benchmark and Evaluation Metrics

The survey organizes Text-to-SQL benchmarks into pre-LLM and LLM-era datasets, and reviews metrics that assess exactness, semantics, and execution efficiency. It also notes that rapidly changing models make detailed benchmarking results quickly outdated.

  • 3.1 Benchmark: Classic benchmarks remain widely used, with Spider 1.0 still described as the leading choice for evaluating Text-to-SQL methods.WikiSQL, Spider 1.0, and KaggleDBQA are identified as longstanding benchmarks, alongside augmented variants.
  • 3.1 Benchmark: LLM-era benchmarks target domain knowledge, perturbations, large noisy databases, and SQL efficiency, extending beyond classic datasets such as Spider 1.0.BIRD contains 12,751 pairs across 95 databases and 37 professional domains, with dirty values, external-knowledge grounding, and efficiency challenges.
  • 3.2 Evaluation Metrics: Exact Set Match compares generated and reference SQL clauses but can underestimate accuracy when equivalent queries have different forms.The limitation arises because multiple SQL expressions can represent the same problem.
  • 3.2 Evaluation Metrics: Execution Accuracy compares query results but can overestimate accuracy because logically different SQL queries may produce identical outputs.Thus, matching execution results does not always establish matching query logic.
  • 3.2 Evaluation Metrics: Valid Efficiency Score incorporates SQL execution efficiency, while Test-suite Accuracy estimates semantic accuracy using high-coverage generated database tests.VES increases with both generated-query correctness and execution efficiency.
  • 3.2 Evaluation Metrics: ESM+ adds rules for joins, DISTINCT, LIMIT, IN, foreign keys, schema checks, and aliases, reducing false positives and false negatives relative to earlier metrics.A study comparing nine LLM-based models reported substantial improvement from ESM+ over EM and TS.

4 Prompt Engineering

The survey divides prompt engineering for Text-to-SQL into preprocessing, inference, and post-processing stages. These stages address input representation, SQL-generation workflows and demonstrations, and subsequent performance and stability improvements.

  • Prompt Engineering: Prompt engineering methods are organized into three stages: preprocessing, inference, and post-processing.The taxonomy separates preparation of inputs, generation of SQL, and improvements after SQL generation.
  • Pre-processing: Preprocessing clarifies question descriptions and database schemas through question representation and selective schema linking.The survey identifies unclear questions and fuzzy schemas as motivations for preprocessing.
  • Inference: Inference generates SQL through workflow design and demonstration usage, including Chain-of-Thought, Least-to-Most, decomposition, autonomous agents, and shot-based prompting.The survey treats workflow patterns and demonstrations as the two logical components of inference.
  • Post-processing: Post-processing is used to enhance the performance and stability of LLM-based Text-to-SQL systems after SQL generation.

4.1 Pre-processing

Pre-processing represents the question with structured layouts, sampled data, and task-related knowledge, then links relevant schema items to reduce distraction and token use.

  • Question Representation: Structural layouts outperform unstructured prompts, while OpenAI and Create Table layouts are reported as either differently ranked or equally matched.C3 reports a significant drop for unstructured layouts; DAIL-SQL favors OpenAI, whereas QDecomp finds the two structural layouts equally matched.
  • Question Representation: Primary and foreign keys are critical for complex multi-table reasoning, with removing foreign keys significantly decreasing performance.The survey also places these keys among important question-representation elements for aligning table relationships.
  • Question Representation: Sample data is beneficial and pluggable, but adding more samples is not always better and depends on available context length.The survey describes sample data as effective when context length permits, while ACT-SQL cautions that more samples can hurt.
  • Schema Linking: Schema linking identifies question-relevant tables and columns, shortening prompts and addressing failures caused by incorrect schema-item identification.It can improve performance and support cross-domain generalization and complex-query synthesis.
  • Schema Linking: Schema linking remains useful when real-world schemas exceed the context window, although its importance declines as context and generation capabilities improve.Distillery reports reduced sensitivity to irrelevant columns as SQL-generation capability improves.
  • Schema Linking: LLM-based schema linking uses dedicated prompting, SQL guidance, or general LLM techniques, while traditional methods use similarity or connectivity.Most surveyed workflows prefer Chain-of-Thought or decomposition reasoning, and both similarity and connectivity remain relevant.

4.2 Inference

Inference methods use structured workflows and demonstrations to generate SQL more reliably than relying on a single direct interaction, balancing accuracy, adaptability, and token cost.

  • Workflows: Workflow designs break complex text-to-SQL generation into intermediate reasoning, sub-questions, classification, SQL generation, and refinement steps.Examples include sequential decomposition with a decomposer agent and multi-stage pipelines such as information determination through active learning.
  • Workflows: Chain-of-Thought generates clause- or keyword-level reasoning in one pass, whereas Least-to-Most reduces the original problem into syntactic and semantic sub-problems.Autonomous agents instead support multi-turn interaction, extended inference, memory, and conditional termination.
  • Workflows: Most surveyed methods use Chain-of-Thought or decomposition as foundational workflows, with customized variants preferred over original CoT.Decomposition can proceed sequentially or in parallel, while alternative workflow patterns remain open for exploration.
  • Demonstrations: Few-shot demonstrations improve task-pattern learning and adaptability over zero-shot prompting, which saves tokens but relies more heavily on pretrained knowledge.The survey classifies inference methods as zero-shot or few-shot according to whether demonstrations are appended.
  • Demonstrations: Demonstration design trades accuracy against token cost, with example selection and query–SQL formats used to control prompt length.DAIL-SQL chooses query-plus-SQL examples to reduce token length, while similar examples can be selected for the current question.

4.3 Post-processing

Post-processing improves generated SQL through self-correction and consistency-based verification, using rules, execution feedback, voting, or multiple agents.

  • Post-processing: Self-correction checks generated SQL with task rules, execution results, or error logs, while surveyed systems mostly emphasize refinement over critic-style judgment.Examples include rechecking table-value spacing and repeating SQL generation after feedback.
  • Post-processing: Self-consistency generates multiple answers and selects the most frequent SQL, offering adaptability and performance gains at the cost of additional LLM interactions.The method commonly uses temperature variation and majority voting.
  • Post-processing: Cross-consistency combines outputs or checks from different LLMs or agents, reducing the bias associated with a single model.Some systems vote across executed SQL results or combine agents with different expertise in iterative loops.
  • Post-processing: Post-processing can combine self-correction and self-consistency sequentially or target recurring error types such as extremum problems.DEA-SQL uses active learning to identify whether generated SQL should be modified, while OpenSearch-SQL checks module alignment.

5 Fine tuning

Fine-tuning adapts base LLMs to text-to-SQL through SQL-generation or workflow-specific objectives, with PEFT favored for efficiency and industrial use motivated by privacy and domain needs.

  • Objectives: Fine-tuning commonly targets SQL generation but can also improve schema linking, decomposition, SQL generation, and correction within multi-agent workflows.MAC-SQL uses selector, decomposer, and refiner agents for these stages.
  • Training Methods: Parameter-efficient fine-tuning is generally preferred over full fine-tuning because it improves training efficiency, lowers costs, and is less prone to catastrophic forgetting.LoRA freezes pretrained weights and adds trainable low-rank matrices; QLoRA extends this approach.
  • Training Data: Fine-tuning datasets usually come from Spider or BIRD, while industrial work may construct domain-specific data such as the financial BULL benchmark.BULL covers funds, stocks, and macroeconomics for financial analysis applications.
  • Current Situation: Industrial text-to-SQL favors fine-tuning for privacy and security, but the research community has explored it less than prompting and open-source model methods lag closed-source alternatives.The survey attributes this imbalance partly to closed-source performance, inexpensive APIs, and fewer algorithmic innovation points in fine-tuning.
  • Current Situation: Open benchmarks lack industrial database characteristics, while LLM-generated training data is identified as a promising direction for improving text-to-SQL systems.The survey also notes that fine-tuning can enhance multiple workflow stages.

6 Model

The survey analyzes closed- and open-source base LLMs for Text-to-SQL, their usage trends, and practical trade-offs between deployment, prompting, and fine-tuning.

  • 6 Model: The surveyed literature uses 12 closed-source and 16 open-source LLMs as base models, counting models across methods and experiments.The survey counts every model associated with an approach or experiment rather than only each method’s best-performing model.
  • 6.1.2 Open-source Models.: After GPT-4 emerged in March 2023, closed-source work favored GPT-series models, while open-source work commonly used Deepseek, Llama, Code Llama, and QWen.Figure 7 tracks model-series usage frequency over time.
  • 6.1.1 Closed-source Models.: Closed-source models offer powerful, ready-to-use code generation, making them suitable for prompt engineering when hardware support is limited.GPT-4 is identified as the most common base model in LLM-based Text-to-SQL work.
  • 6.1.2 Open-source Models.: Open-source models support private deployment and domain fine-tuning, but require users to have suitable hardware.Common families include Llama, Code Llama, Deepseek, and QWen; SQLCoder is specialized for Text-to-SQL.

7 Analysis

The survey compares LLM-based Text-to-SQL methods on Spider 1.0, BIRD, and Spider 2.0 using execution accuracy. Results are strong on established benchmarks but reveal substantial challenges on realistic, complex tasks.

  • 7.3 Results: Most methods exceed 80% pass rates on Spider 1.0, indicating that modern LLMs effectively solve basic Text-to-SQL semantic parsing.Spider 1.0 remains a primary benchmark for comparative evaluation.
  • 7.3 Results: On BIRD, prompting powerful closed- or open-source models and fine-tuning smaller open-source models both achieve competitive state-of-the-art performance.The comparison suggests that fine-tuned compact models can approach the capabilities of much larger models.
  • 7.3 Results: Top-performing methods use inference-time scaling through multi-SQL generation, iterative correction, self-consistency, or multiple prompts for schema linking and SQL generation.These strategies broaden the search space for candidate answers compared with simpler baselines.
  • 7.1 Experimental Setup: The comparison uses execution accuracy from official leaderboards across Spider 1.0, BIRD, and Spider 2.0, with selection rules restricting models and methods for fairness.Figure 8 presents execution accuracy for method–LLM combinations on the three test sets.
  • 7.3 Results: Enhanced schema linking is another recurring characteristic, including broader column inclusion when context permits and inference-time exploration to improve SQL generation.Distillery reports increasing tolerance of irrelevant columns, while MCS-SQL expands schema-linking exploration.
  • 7.3 Results: Spider 2.0’s best reported method reaches only 31.26% execution accuracy, exposing a large gap between academic performance and real-world deployment.Agent-based methods outperform non-agent approaches on this more complex benchmark.

8 Future Direction

Future Text-to-SQL research must address persistent error sources, production constraints, realistic schema complexity, domain adaptation, and benchmark ambiguity. Promising directions include agent-based interaction, better training data, and more practical evaluation datasets.

  • 8.1 Error Analysis Overview: Schema-linking errors occur at 29%-49%, exceeding JOIN errors at 21%-26% and GROUP BY or nested errors below 20%.The survey identifies schema-linking as the module most needing improvement, while join connections and syntax remain substantial challenges.
  • 8.2 Practical Challenges and Directions: Private deployment and fine-tuning can address API privacy concerns, but dirty data, capability compromise, and catastrophic forgetting constrain production gains.The survey points to high-quality training-data extraction as one way to mitigate private fine-tuning problems.
  • 8.2.2 Complex Schema and Insufficient Benchmark: Real-world warehouses can contain 632 tables, over 4000 columns, and 200 views with more than 7400 columns, creating schema-linking, attention, and inference-time difficulties.Agent-based methods that interactively explore database structure are described as a promising response to schema-linking challenges.
  • 8.2.2 Complex Schema and Insufficient Benchmark: Spider and BIRD do not capture warehouse-scale complexity, while Spider 2.0 illustrates a direction toward benchmarks involving systems such as BigQuery and Snowflake.The survey states that BIRD is closer to real-world scenarios than Spider but remains far smaller than the cited Microsoft warehouse.
  • 8.2.3 Domain Knowledge: Domain adaptation is limited because RAG depends on structured knowledge bases and similarity retrieval may add irrelevant information, while fine-tuning risks forgetting and costly retraining.The survey links these constraints to noisy domain documents and the need to repeatedly update embedded knowledge.
  • 8.2.1 Agents: LLM agents offer a direction for modeling human SQL construction through iterative execution and revision.The proposed motivation is grounded in trial-and-error cycles during human SQL construction.
  • 8.2.4 Ambiguity and Semantic Mismatch: Dataset ambiguity and semantic mismatch complicate correctness evaluation, motivating better domain knowledge, higher-quality training data, and refined benchmarks.The survey connects improved domain knowledge with stronger RAG alignment and refined datasets with more robust practical evaluation.

9 Conclusion

The survey reviews LLM-based Text-to-SQL research through benchmarks, evaluation metrics, taxonomies of prompt engineering and fine-tuning, comparative analysis, and future challenges.

  • 9 Conclusion: The survey catalogs benchmarks and evaluation metrics, organizes prompt engineering and fine-tuning methods, analyzes evaluated models and datasets, and discusses challenges and future directions.
Loading 2407.15186v5…