Expressive Power of SQL

  • Leonid Libkin
Conference paper
Part of the Lecture Notes in Computer Science book series (LNCS, volume 1973)


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.


Arithmetic Operation Query Language Transitive Closure Expressive Power Relational Algebra 
These keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.


Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.


  1. 1.
    S. Abiteboul, R. Hull and V. Vianu. Foundations of Databases, Addison Wesley, 1995.Google Scholar
  2. 2.
    A. V. Aho and J. D. Ullman. Universality of data retrieval languages. In POPL’79, pages 110–120.Google Scholar
  3. 3.
    D.M. Barrington, N. Immerman, H. Straubing. On uniformity within NC 1. JCSS, 41:274–306, 1990.zbMATHMathSciNetGoogle Scholar
  4. 4.
    P. Buneman, S. Naqvi, V. Tannen, L. Wong. Principles of programming with complex objects and collection types. TCS, 149 (1995), 3–48.zbMATHCrossRefMathSciNetGoogle Scholar
  5. 5.
    J. Celko. SQL for Smarties: Advanced SQL Programming. Morgan Kaufmann, 2000.Google Scholar
  6. 6.
    M. Consens and A. Mendelzon. Low complexity aggregation in GraphLog and Datalog, TCS 116 (1993), 95–116. Extended abstract in ICDT’90.zbMATHCrossRefMathSciNetGoogle Scholar
  7. 7.
    C. J. Date and H. Darwen. A Guide to the SQL Standard. Addison Wesley, 1997.Google Scholar
  8. 8.
    G. Dong, L. Libkin and L. Wong. Local properties of query languages. TCS 239 (2000), 277–308. Extended abstract in ICDT’97.zbMATHCrossRefMathSciNetGoogle Scholar
  9. 9.
    M. Grohe and T. Schwentick. Locality of order-invariant first-order formulas. ACM TOCL, 1 (2000), 112–130.CrossRefMathSciNetGoogle Scholar
  10. 10.
    K. Etessami. Counting quantifiers, successor relations, and logarithmic space, JCSS, 54 (1997), 400–411.zbMATHMathSciNetGoogle Scholar
  11. 11.
    R. Fagin, L. Stockmeyer and M. Vardi, On monadic NP vs monadic co-NP, Information and Computation, 120 (1995), 78–92.zbMATHCrossRefMathSciNetGoogle Scholar
  12. 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. 13.
    H. Gaifman. On local and non-local properties, Proceedings of the Herbrand Symposium, Logic Colloquium’ 81, North Holland, 1982.Google Scholar
  14. 14.
    E. Grädel and Y. Gurevich. Metafinite model theory. Information and Computation 140 (1998), 26–81.zbMATHCrossRefMathSciNetGoogle Scholar
  15. 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
  16. 16.
    L. Hella. Logical hierarchies in PTIME. Information and Computation, 129 (1996), 1–19.zbMATHCrossRefMathSciNetGoogle Scholar
  17. 17.
    L. Hella, L. Libkin and J. Nurmonen. Notions of locality and their logical characterizations over finite models. J. Symb. Logic, 64 (1999), 1751–1773.zbMATHCrossRefMathSciNetGoogle Scholar
  18. 18.
    L. Hella, L. Libkin, J. Nurmonen and L. Wong. Logics with aggregate operators. In LICS’99, pages 35–44.Google Scholar
  19. 19.
    N. Immerman. Descriptive Complexity. Springer Verlag, 1998.Google Scholar
  20. 20.
    A. Klug. Equivalence of relational algebra and relational calculus query languages having aggregate functions. J. ACM 29 (1982), 699–717.zbMATHCrossRefGoogle Scholar
  21. 21.
    L. Libkin. On the forms of locality over finite models. In LICS’97, pages 204–215.Google Scholar
  22. 22.
    L. Libkin. Logics with counting and local properties. ACM TOCL, 1 (2000), 33–59. Extended abstract in LICS’98.CrossRefMathSciNetGoogle Scholar
  23. 23.
    L. Libkin, L. Wong. Query languages for bags and aggregate functions. JCSS 55 (1997), 241–272. Extended abstract in PODS’94.zbMATHMathSciNetGoogle Scholar
  24. 24.
    L. Libkin and L. Wong. On the power of aggregation in relational query languages. In DBPL’97, pages 260–280.Google Scholar
  25. 25.
    P. O’Neil. Database: Principles, Programming, Performance. Morgan Kaufmann, 1994.Google Scholar
  26. 26.
    I. Parberry and G. Schnitger. Parallel computation and threshold functions. JCSS 36 (1988), 278–302.zbMATHMathSciNetGoogle Scholar
  27. 27.
    A. Razborov and S. Rudich. Natural proofs. JCSS 55 (1997), 24–35.zbMATHMathSciNetGoogle Scholar
  28. 28.
    P. Wadler. Comprehending monads. Mathematical Structures in Computer Science 2 (1992), 461–493.zbMATHMathSciNetCrossRefGoogle Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 2001

Authors and Affiliations

  • Leonid Libkin
    • 1
  1. 1.University of Toronto and Bell Laboratories

Personalised recommendations