Common Table Expressions (WITH and WITH RECURSIVE)

Update: Updated note about DB2 version and added a note about Oracle.

Update: Firebird has also implemented CTE.

Going through the presentations from FOSDEM'09 I opened up David Fetter's Trees and More presentation and thought "what could be new about trees?". Man was I wrong!

The presentation shows classical solutions to the problem of trees and hierarchical data in SQL and then moves on to show how this can be solved by the implementation of WITH RECURSIVE in the upcoming PostgreSQL 8.4.

Taking a further look at WITH and WITH RECURSIVE I realised that this is a very powerful tool. Not entirely new, as Depez points out, but still a very usable thing. RECURSIVE is the key part that is new and that could not be solved in a very good way previously.

I have just started to think about what this will mean for our database and so far I've found a case where this is gonna make some difference. Of course I have to make some experiments to verify my theory. :-D

As far as I can tell PostgreSQL is the third RDBMS implementing the WITH and WITH RECURSIVE statement, known as Common Table Expressions or CTE, from the 2003 (WITH) and 2008 (WITH RECURSIVE) SQL-standard. Previously CTE has been implemented by Microsoft, introduced in MS SQL Server 2005, IBM, with DB2 V7, and by Firebird SQL Server, since v2.1.

I thought I could slip under the radar about Oracle but I repent with this note. ;-) Oracle have had WITH since v9.0 and has had CONNECT BY that serves the purpose of WITH RECURSIVE for ages.

I'm not gonna write any examples here since there are a lot of them out there.


(Although I might come back with a little more hands on example after playing around with it. For now I recommend Depez post as it contains a good discussion and example of the feature and what it'll mean for PostgreSQL users.)

Postat av: Bert

Since version 2.1 also available in firebird

2009-03-03 @ 19:16:43
Postat av: roppert

@Bert: Thank you for pointing out the CTE support in Firebird. Post updated accordingly.

2009-03-03 @ 19:41:56
Postat av:

Oracle has the WITH clause since I think 9.0 although not with recursive.

2009-03-03 @ 21:42:30
Postat av: Robert Young

Oracle has its CONNECT BY syntax since nearly the first release. Whether this does the same thing, or something different/better has been an ongoing controversy since...

IBM, realizing the issue, devised the WITH syntax in V7.2 UDB/LUW (about 2001/2). They got it adopted as SQL standard. Oracle then implemented it. SQLServer got it with 2005 (I think, don't do too much with, but I don't think it was around with 2000).

Then the xml zealots (nee, IMS programmers) took over DB2, and so now we have this misbegotten mess called DB2/V9

2009-03-03 @ 22:00:45
Postat av: roppert

@anonymous and Robert Young: Thanks for your notes. Post updated accordingly.

2009-03-03 @ 23:30:31
Postat av: Thomas

SQL Server 2005 was the first version to have the recursive WITH. SQL Server 2000 does not support it.

2009-03-04 @ 00:08:19

Kommentera inlägget här:

Kom ihåg mig?

E-postadress: (publiceras ej)



RSS 2.0