Expressive Power of SQL
It is a folk result in database theory that SQL cannot express recursive queries such as reachability; in fact, a new construct was added to SQL3 to overcome this limitation. However, the evidence for this claim is usually given in the form of a reference to a proof that relational algebra cannot express such queries. SQL, on the other hand, in all its implementations has three features that fundamentally distinguish it from relational algebra: namely, grouping, arithmetic operations, and aggregation.
In the past few years, most questions about the additional power provided by these features have been answered. This paper surveys those results, and presents new simple and self-contained proofs of the main results on the expressive power of SQL. Somewhat surprisingly, tiny differences in the language definition affect the results in a dramatic way: under some very natural assumptions, it can be proved that SQL cannot define recursive queries, no matter what aggregate functions and arithmetic operations are allowed. But relaxing these assumptions just a tiny bit makes the problem of proving expressivity bounds for SQL as hard as some long-standing open problems in complexity theory.
KeywordsArithmetic Operation Query Language Transitive Closure Expressive Power Relational Algebra
Unable to display preview. Download preview PDF.
- 1.S. Abiteboul, R. Hull and V. Vianu. Foundations of Databases, Addison Wesley, 1995.Google Scholar
- 2.A. V. Aho and J. D. Ullman. Universality of data retrieval languages. In POPL’79, pages 110–120.Google Scholar
- 5.J. Celko. SQL for Smarties: Advanced SQL Programming. Morgan Kaufmann, 2000.Google Scholar
- 7.C. J. Date and H. Darwen. A Guide to the SQL Standard. Addison Wesley, 1997.Google Scholar
- 12.S. Finkelstein, N. Mattos, I.S. Mumick, and H. Pirahesh. Expressing recursive queries in SQL. ANSI Document X3H2-96-075r1, 1996.Google Scholar
- 13.H. Gaifman. On local and non-local properties, Proceedings of the Herbrand Symposium, Logic Colloquium’ 81, North Holland, 1982.Google Scholar
- 15.W. Hanf. Model-theoretic methods in the study of elementary logic. In J.W. Addison et al, eds, The Theory of Models, North Holland, 1965, pages 132–145.Google Scholar
- 18.L. Hella, L. Libkin, J. Nurmonen and L. Wong. Logics with aggregate operators. In LICS’99, pages 35–44.Google Scholar
- 19.N. Immerman. Descriptive Complexity. Springer Verlag, 1998.Google Scholar
- 21.L. Libkin. On the forms of locality over finite models. In LICS’97, pages 204–215.Google Scholar
- 24.L. Libkin and L. Wong. On the power of aggregation in relational query languages. In DBPL’97, pages 260–280.Google Scholar
- 25.P. O’Neil. Database: Principles, Programming, Performance. Morgan Kaufmann, 1994.Google Scholar