Hierarchical query

Hierarchical query

A hierarchical query is a type of SQL query that handles hierarchical model data.

The most common syntax is "CONNECT BY", used by EnterpriseDB [ [http://www.enterprisedb.com/documentation/hierarchical-queries.html Hierarchical Queries] , EnterpriseDB] and Oracle database. [ [http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm Hierarchical Queries] , Oracle]

SELECT select_list FROM table_expression [ WHERE ... ] [ START WITH start_expression ] CONNECT BY { PRIOR parent_expr = child_expr | child_expr = PRIOR parent_expr } [ ORDER SIBLINGS BY column1 [ ASC | DESC ] [, column2 [ ASC | DESC ] ] ... [ GROUP BY ... ] [ HAVING ... ] ...

;For example: SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr FROM emp START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr;

The output from the above query would look like:

level | employee | empno | mgr -------+-------------+-------+------ 1 | KING | 7839
2 | JONES | 7566 | 7839 3 | SCOTT | 7788 | 7566 4 | ADAMS | 7876 | 7788 3 | FORD | 7902 | 7566 4 | SMITH | 7369 | 7902 2 | BLAKE | 7698 | 7839 3 | ALLEN | 7499 | 7698 3 | WARD | 7521 | 7698 3 | MARTIN | 7654 | 7698 3 | TURNER | 7844 | 7698 3 | JAMES | 7900 | 7698 2 | CLARK | 7782 | 7839 3 | MILLER | 7934 | 7782 (14 rows)

Pseudocolumns

* LEVEL
* CONNECT_BY_ISLEAF
* CONNECT_BY_ISCYCLE

unary operators

* CONNECT_BY_ROOT

Functions

* SYS_CONNECT_BY_PATH

References


Wikimedia Foundation. 2010.

Игры ⚽ Нужно решить контрольную?

Look at other dictionaries:

  • Nested set model — The nested set model is a particular technique for representing nested sets (also known as trees or hierarchies) in relational databases. The term was apparently introduced by Joe Celko; others describe the same technique without naming it [1] or …   Wikipedia

  • Select (SQL) — A SQL SELECT statement returns a result set of records from one or more tables. [cite web |url=http://msdn2.microsoft.com/en us/library/ms189499.aspx |title=Transact SQL Syntax Conventions |accessdate= |author=Microsoft ] [cite web… …   Wikipedia

  • Hierarchy — A hierarchy (Greek: hierarchia (ἱεραρχία), from hierarches, leader of sacred rites ) is an arrangement of items (objects, names, values, categories, etc.) in which the items are represented as being above, below, or at the same level as one… …   Wikipedia

  • Database — A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality (for example, the availability of rooms in hotels), in a way that supports… …   Wikipedia

  • Database model — A database model is the theoretical foundation of a database and fundamentally determines in which manner data can be stored, organized, and manipulated in a database system. It thereby defines the infrastructure offered by a particular database… …   Wikipedia

  • Database models — A data model is not just a way of structuring data: it also defines a set of operations that can be performed on the data. The relational model, for example, defines operations such as select, project, and join. Although these operations may not… …   Wikipedia

  • WinFS — (short for Windows Future Storage)[1] is the code name for a cancelled[2] data storage and management system project based on relational databases, developed by Microsoft and first demonstrated in 2003 as an advanced storage subsystem for the… …   Wikipedia

  • information processing — Acquisition, recording, organization, retrieval, display, and dissemination of information. Today the term usually refers to computer based operations. Information processing consists of locating and capturing information, using software to… …   Universalium

  • Database management system — A database management system (DBMS) is a software package with computer programs that control the creation, maintenance, and the use of a database. It allows organizations to conveniently develop databases for various applications by database… …   Wikipedia

  • Relational model — The relational model for database management is a database model based on first order predicate logic, first formulated and proposed in 1969 by Edgar Codd. [ Derivability, Redundancy, and Consistency of Relations Stored in Large Data Banks , E.F …   Wikipedia

Share the article and excerpts

Direct link
… Do a right-click on the link above
and select “Copy Link”