Evaluation of Large Language Models for Natural Language to SQL Query Generation: A Comparative Study Using Exact Match and Execution Accuracy
TL;DR
The study empirically demonstrates comparative behaviours of the current LLMs, and suggests the use of execution-aware evaluation in the studies and deployment of Text-to-SQL.
Abstract
Decisions are being made based on data, and the demands for easy-to-use interfaces that enable users to simply type in a question to a relational database without having to be a Structured Query Language (SQL) expert have increased. Although the development of Large Language Models (LLMs) has seen remarkable advancements in the past few years, and has led to the emergence of LLMs that have significantly improved the generation of SQL from natural language (Text-to-SQL)[1], there is a relative lack of systematic comparisons between the latest commercial LLMs that has been tested against a series of graded SQL complexity levels with both syntactic and execution-based metrics. This research aims to assess and contrast ChatGPT, Google Gemini, DeepSeek, and Microsoft Copilot for their accuracy and efficacy in creating SQL queries from natural language queries. In a quantitative experimental design, 100 natural language questions were given using a purpose-built benchmark, with six types of queries that included simple retrieval, filtering, joins, aggregation, GROUP BY and HAVING, and nested subqueries. The results of the models were assessed using the manually written and validated ground truth query, with the use of normalised Exact Match Accuracy (EMA) and Execution Accuracy (EXA). The best overall results (EMA 97%, EXA 97%) were obtained by ChatGPT. The Copilot scored EMA 56% and EXA 96%, DeepSeek EMA 55% and EXA 93% and Gemini the lowest with a score of EMA 36% and EXA 87%. The large margin between EMA and EXA for the models (+51 points, +40 points, +38 points) demonstrates the significant shortcomings of exact-match scoring when it comes to assessing real-world skills for producing valid but structurally different SQL. The study empirically demonstrates comparative behaviours of the current LLMs, and suggests the use of execution-aware evaluation in the studies and deployment of Text-to-SQL.