On Thu, 26 Dec 2013 20:23:33 +0100 big stone <stonebi...@gmail.com> wrote:
> Indeed, '1' CTE can be replaced by the creation of 'N' temporary > views (or tables), and their deletion after the CTE request. > > CTE is : > - a syntaxic simplification : > . the SQL creator don't have to care about those intermediate > views, . these intermediate views don't grow and multiply on your > database schema. Let's enumerate the uses of a CTE: 1. Used once, instead of a subquery 2. Used multiple times, instead of a view or repeated subquery 3. Used in a recursive query Of these, only #3 gains any benefit from a CTE. Unless the (CTE) query is used more than once, it has no advantage over a subquery. Case #2 is rare, and a view serves as well. Only a small fraction of queries use a subquery more than once. The small number of such views will not materially affect the schema namespace. Recursive queries are a unique feature of CTEs. They are not supported in SQLite. If and when they are, CTEs will be required. I for one would like to see recursive updates supported, but I recognize it's somewhat specialized behavior and non-trivial to implement. Support for atomic updates is more important. --jkl --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
