Menu
Home
People
Places
Arts
History
Plants & Animals
Science
Life & Culture
Technology
Reference.org
Hierarchical and recursive queries in SQL
open-in-new
<p>A hierarchical query is a type of <a href="/facts/Select_(SQL)/cYnKkmFo">SQL query</a> that handles <a href="/facts/Hierarchical_model/vLzcNqVo">hierarchical model</a> data. They are special cases of more general recursive <a href="/facts/Fixpoint/vVVddTKc">fixpoint</a> queries, which compute <a href="/facts/Transitive_closure/HEtgYGM4">transitive closures</a>. </p><p>In standard <a href="/facts/SQL%3a1999/lNJB4sgR">SQL:1999</a> hierarchical queries are implemented by way of recursive <i>common table expressions</i> (CTEs). Unlike Oracle's earlier connect-by clause, recursive CTEs were designed with fixpoint semantics from the beginning. Recursive CTEs from the standard were relatively close to the existing implementation in IBM DB2 version 2. Recursive CTEs are also supported by <a href="/facts/Microsoft_SQL_Server/eCdt2JBL">Microsoft SQL Server</a> (since SQL Server 2008 R2), <a href="/facts/Firebird_(database_server)/hKVdDnBR">Firebird 2.1</a>, <a href="/facts/PostgreSQL/LHdXuHfl">PostgreSQL 8.4+</a>, <a href="/facts/SQLite/1PwYzIft">SQLite 3.8.3+</a>, <a href="/facts/IBM_Informix/jDdeW93c">IBM Informix</a> version 11.50+, <a href="/facts/CUBRID/CG12Vv3p">CUBRID</a>, <a href="/facts/MariaDB/QWobVFIq">MariaDB 10.2+</a> and <a href="/facts/MySQL/QEEDNBDP">MySQL 8.0.1+</a>. <a href="https://kb.tableau.com/articles/howto/using-common-table-expressions">Tableau has documentation</a> describing how CTEs can be used. TIBCO Spotfire does not support CTEs, while Oracle 11g Release 2's implementation lacks fixpoint semantics. </p><p>Without common table expressions or connected-by clauses it is possible to achieve hierarchical queries with user-defined recursive functions. </p>