I'll use an example table which mimics a BOM (Bill Of Materials) taken from the web. which ends up the CTE and builds it's output, note that in the second example I applied a further where condition to this last query, something that might be non optimal. which is the recursive query, note that it's joined to the base query through it's alias It supports SQL Server, SQLite, Firebird, MariaDB, MySQL, PostgreSQL. UNION ALL which marks the merge described above DB Fiddle is a website that allows you to practice your SQL queries online using. which is the base query and sets the starting point, this query will be merged with results of the recursive query through an union N(.) AS ( which describes the output of the base query, n is just an alias, you can use whatever name you like WITH RECURSIVE which tells us that this CTE will use recursion Ī recursive CTE is basically made of two parts, a base query which gives you the starting point and a recursive query which is the action to be performed in a "loop". This is basically the ability to efficiently and easily query hierarchical data stored in the most intuitive way, the adjacency list, because the nested set (see Celko's articles and book) might be the most efficient, but it misses one important thing, widespread adoption. So, on with 1., Firebird recently added another great feature, common table expressions (CTE) which, to my eye at least, boils down to hierarchical queries. A call to action for other opensource databases A quick glance at the new "common table expression" (aka hierarchical queries) in Firebird 2.1Ģ.
0 Comments
Leave a Reply. |