CPS 430/542 Lecture notes: Recursion in Datalog and SQL

Coverage: [FCDB] §§10.3-10.6 (pp. 480-502)

Why study multiple query languages? (revisited)

    database system implementation relational algebra
    (query processing SQL
    (and optimization) Datalog AI
    knowledge-based systems, LP (PROLOG)
    data mining, ILP (PROGOL)


  • why did we study multiple query languages
    • SQL is most mainstream language
    • relational algebra helps us make connections to database system implementation (query processing and optimization in view example)
    • Datalog is most expressive; helps us make connections to knowledge-based systems (through PROLOG)
  • there are some queries which cannot be written in SQL or relational algebra
  • extensional (stored) vs. intensional (computed) relations (EDB's vs. IDB's)
  • types of recursion
    • linear: left or right
    • non-linear, e.g., fib(n) = fib(n-1) + fib(n-2);
  • fixpoints ... remember these?
  • least fixpoint, e.g., of f(x) = x2 is 0 (the greatest fixpoint is 1)
  • ancestor example (the hello world of recursion in Datalog)
  • must use caution when mixing negation and recursion


  • ancestor example (the hello world of recursion in Datalog)
  • celebrity problem
  • flights example (stratified negation)
  • even-odd example
    • mutual recursion with negation
    • 2 least fixpoint solutions, and they contradict each other
    • unstratified example

Stratified recursion

  • how to determine if negation is stratified?
  • boils down to checking for cycles in a graph of IDB relations

Recursion in SQL

  • support for recursion based on Datalog
  • in SQL99 which is not part of the core SQL
  • defining IDB relations in SQL: use WITH RECURSIVE
  • only linear recursion permitted
  • only stratified negation and aggregations permitted

Deductive vs. inductive systems

  • deductive (LP; e.g., PROLOG): rules → data
  • inductive (ILP; e.g., PROGOL): data → rules
  • (inverses of each other akin to differentiation and integration in calculus)


    [FCDB] J.D. Ullman and J. Widom. A First Course in Database Systems. Prentice Hall, Upper Saddle River, NJ, Second edition, 2002.

Return Home