How to Prompt LLMs for Text-to-SQL: A Study in Zero-shot, Single-domain, and Cross-domain Settings

1 Introduction

• Prior work has used demonstration retrieval, intermediate reasoning, and self-debugging to augment LLM text-to-SQL [Poesia et al., 2022; Shi et al., 2022].The cited passage also names Cheng et al. (2022), Chen et al. (2023), and Pourreza and Rafiei (2023) for intermediate reasoning and self-debugging.Source: §1 · p. 1
• Different prompt strategies make it difficult to compare studies’ main contributions, and findings may change with future prompt-engineering discoveries.Source: §1 · p. 2
• The study compares prompt-construction strategies in zero-shot, single-domain, and cross-domain text-to-SQL settings.It evaluates database prompts in all three settings and demonstration construction in the cross-domain setting.Source: §1 · p. 2
• The paper reports that table relationships and content matter, but LLMs are sensitive to their representation in zero-shot and cross-domain settings.Source: §1 · p. 2
• In-domain demonstrations can reduce sensitivity to database-knowledge representations but do not replace table-content knowledge.Source: §1 · p. 2
• Prompt length significantly affects cross-domain performance, with a preferred length associated with improved performance.Source: §1 · p. 2

Zero-shot Text-to-SQL

• Zero-shot evaluation tests whether an LLM can infer the NLQ-to-SQL relationship from a table without demonstrations.The prompt includes a task instruction, test question, and corresponding database.Source: §2 · p. 2

Single-domain Few-shot Text-to-SQL

• Single-domain few-shot evaluation measures adaptation using a few NLQ-SQL examples from the same database as the test question.The setting is framed for domains where examples are easy to construct and tests performance with minimal in-domain training data.Source: §2 · p. 2

Cross-domain Few-shot Text-to-SQL

• Cross-domain few-shot evaluation tests generalization to a new database using demonstrations from one or more different databases.Source: §2 · p. 2

3 Prompt Construction

• A text-to-SQL prompt combines task instruction, test database, test question, and optional demonstrations; the paper varies database and demonstration construction.Source: §3 · p. 2

3.1 Database Prompt

• Database schema comprises table headers and relationships, while database content is the data stored in tables.Source: §3.1 · p. 2
• Schema prompts range from table-and-column listings to equation-like column lists, with foreign-key notation or CreateTable statements making table relationships explicit.Source: §3.1 · pp. 2–3, Figure 2 · p. 3

3.2 Demonstration Prompt

• Prompt normalization unifies spaces and line breaks and lowercases text except database content; demonstration SQL is parsed and formatted consistently.Figure 4 illustrates normalization for database and SQL prompts.Source: §3.1 · p. 3, §3.2 · p. 4, Figure 4 · p. 4

3.1 Database Prompt

• Database content can clarify the formats of values, such as whether “American student” corresponds to “USA” or “The United States of America.”Source: §3.1 · p. 3
• InsertRow and SelectRow show table rows, whereas SelectCol lists distinct values per column to expose a broader range of content.SelectCol uses a SELECT DISTINCT query with a limit; the figures illustrate alternative content constructions.Source: §3.1 · p. 3, Figure 3 · p. 3

3.2 Demonstration Prompt

• The paper considers cross-domain demonstrations comprising M databases with K NLQ-SQL pairs each, where M × K = N.This generalizes setups using one database with N examples or N databases with one pair each.Source: §3.2 · pp. 3–4

Data & Evaluation

• Experiments use Spider-dev’s 20 databases and 1,034 NLQ-SQL pairs, scoring predictions with execution accuracy against gold SQL.Spider-dev serves as the test set because Spider’s test set is not publicly available.Source: §4 · p. 4
• Cross-domain demonstrations come from Spider’s training set, with CreateTable demonstration databases restricted to schemas under 1,000 tokens.This restriction leaves 130 demonstration databases.Source: §4 · p. 4

Models

• The evaluated models are GPT-3 Codex and ChatGPT.Source: §4 · p. 4

Experiment Setup

• Zero-shot prompts use task instructions, a test database, a question, and three table rows; few-shot prompts add N demonstrations.The three-row setting is described as the optimal number in prior work.Source: §4 · p. 4
• Single-domain examples are randomly drawn from other examples in the same database, excluding examples with the test SQL template.The setup uses leave-one-out splits because some Spider-dev databases have few examples.Source: §4 · p. 4
• Cross-domain runs randomly sample M databases with K pairs each; Codex alone is used because its 8K-token limit exceeds ChatGPT’s 4K limit.The same few-shot examples are used to compare prompt constructions, and results average three runs.Source: §4 · p. 4

5.1 Zero-shot Text-to-SQL

• Adding foreign keys improved zero-shot accuracy from 71.8 to 73.1 for Codex and 69.1 to 71.2 for ChatGPT; SelectRow and SelectCol content helped, unlike InsertRow.The authors recommend Codex with normalized CreateTableSelectCol for zero-shot text-to-SQL.Source: §5.1 · p. 5

5.2 Single-domain Text-to-SQL

• Foreign-key advantages shrink as in-domain examples accumulate, disappearing for Codex and leaving ChatGPT a 0.5% gap at 16 examples.In zero-shot, the advantage was 1.3 for Codex and 2.1 for ChatGPT.Source: §5.2 · p. 6
• Table content remains important with in-domain examples: CreateTable+SelectCol retains an advantage at 16 examples, while content-format disparities become marginal with four.At 16 examples, the gaps were 1.3 for Codex and 1.9 for ChatGPT; with four, the SelectCol 3–InsertRow 3 differences were 0.3 and 0.2, respectively.Source: §5.2 · p. 6

5.3.1 Impact of Demonstration Prompt

• Codex accuracy follows an inverted-U with prompt length, dropping beyond approximately 5,500 tokens; ChatGPT-16K declines beyond 11K tokens.The authors conjecture that LLMs have a preferred prompt length, potentially influenced by model architecture or training data.Source: Figure 7 · p. 7, §5.3.1 · p. 7
• Increasing examples per database initially improves Codex results but plateaus or declines after four; adding databases also eventually reduces performance, with 4 databases and 8 or 16 examples performing worse than 2 or 4.The threshold for decline with more databases was either 4 or 6, depending on the number of examples per database.Source: §5.3.1 · p. 7

5.3.2 Impact of Database Prompt

• With one demonstration database, cross-domain accuracy initially rises as examples increase, then levels off beyond four examples across database prompts.The experiments used one demonstration database because adding demonstration databases could reduce Codex performance.Source: §5.3.2 · p. 7
• Out-of-domain demonstrations do not supply test-database-specific knowledge, so database prompts remain important and Codex remains sensitive to how table content is represented.Codex preferred SelectRow and SelectCol over InsertCol even when demonstration databases mirrored the test database’s construction.Source: §5.3.2 · p. 8

• Similarity-based retrieval and diverse demonstrations have been studied for cross-domain retrieval and compositional generalization, while other work uses intermediate reasoning and self-correction.The cited examples include Rubin et al. (2021), Poesia et al. (2022), Levy et al. (2022), Pourreza and Rafiei (2023), and Chen et al. (2023).Source: §6 · p. 8
• Unlike Rajkumar et al. (2022), who analyze CodeX on Spider in zero-shot and two databases in single-domain settings, this study evaluates more databases across all three settings.The paper describes its motivation as similar but its evaluation as broader.Source: §6 · p. 8
• Prior schema encodings include graph neural networks and schema sequences, with table relationships represented as metadata or used to guide attention.The passage cites Bogin et al. (2019), Chen et al. (2021b), Guo et al. (2019), Lin et al. (2020), Shaw et al. (2020), Wang et al. (2019), Cao et al. (2021), and Li et al. (2023b).Source: §6 · p. 8
• Supervised methods identify question-relevant table content through string matching, whereas this paper follows prior work in providing content without explicitly considering the question.The passage notes that Chang et al. (2023) revealed string matching’s vulnerability to perturbations and cites Rajkumar et al. (2022) and Chen et al. (2023) for providing content without question-specific selection.Source: §6 · p. 8

• The paper compares database and demonstration prompt constructions across zero-shot, single-domain, and cross-domain text-to-SQL.Source: §7 · p. 9
• The investigation identifies critical database knowledge and effective representations for prompting.Source: §7 · p. 9
• Codex has a prompt-length sweet spot in the cross-domain setting.Source: §7 · p. 9
• The authors expect these findings to provide guidance for future research on LLM text-to-SQL.Source: §7 · p. 9

• Experiments use 20 databases from Spider, so findings may not apply to a specific database that differs significantly from Spider databases.Source: Limitation · p. 9
• The study does not investigate prompt constructions with different demonstration-retrieval strategies.Source: Limitation · p. 9
• The study does not investigate prompt constructions with intermediate reasoning steps.Source: Limitation · p. 9

• The authors acknowledge the ACL Ethics Policy and state that they agree with it.Source: Ethics Statement · p. 9
• The experiments use OpenAI Codex and ChatGPT; Codex is free for research purposes and ChatGPT costs around $200.Source: Ethics Statement · p. 9
• The paper’s code is included in the supplementary materials and is to be publicly released to facilitate reproducibility.Source: Ethics Statement · p. 9

A.1 Prompt Examples

• The zero-shot normalized example pairs a SQLite instruction and a high-schooler question with a schema prompt containing tables and three example rows.The example database includes highschooler, friend, and likes tables.Source: §A.1 · p. 11
• The single-domain normalized example places a database prompt and four demonstrations before the test question.Its book database demonstrations include queries for writers in alphabetical order and the number of books.Source: §A.1 · pp. 11–12
• The cross-domain example places two demonstration databases, each with two examples, before the test database and question.The example includes publication and book table schemas and sample rows.Source: §A.1 · p. 12

A.2 Tests of Significance

• Normalization slightly improves Codex results for every tested database prompt construction and ChatGPT results for six of seven, but none of the same-method comparisons is significant at p > 0.05.The reported benefit is reduced variation among databases and shorter overall prompts.Source: §A.2 · p. 13
• Adding table relationships or content is statistically significant in most tested comparisons, with p-values below 0.05; the relationship comparison is not significant only for Codex with normalized prompts.McNemar’s tests compare prompt constructions; Table 3 marks results with p-values below 0.05 as significant.Source: §A.2 · p. 13, Table 3 · p. 14

A.3 Detailed Single-domain Results

• Tables 4 and 5 provide detailed single-domain results for Codex and ChatGPT, with best and second-best results identified for each shot.The paper also points to Figure 5 for an illustration of both models’ performance.Source: §A.3 · p. 13, Table 4 · p. 14, Table 5 · p. 14

A.4 Impact of Demonstration Prompt for ChatGPT-16K

• For a fixed number of examples per database, ChatGPT-16K accuracy initially rises as more databases are added, then falls after a threshold.Figure 8 maps accuracy over combinations of database and example counts.Source: Figure 8 · p. 13, §A.4 · p. 13
• ChatGPT-16K accuracy follows an inverted-U trend as prompt length increases, starting to decline above approximately 11K tokens; performance also tends to decline beyond roughly 70% of maximum context length.Figure 9 relates execution accuracy to prompt length; the paper reports the decline threshold at approximately 11K tokens for ChatGPT-16K.Source: Figure 9 · p. 13, §A.4 · p. 13