{"id":73162,"date":"2015-06-16T11:31:35","date_gmt":"2015-06-16T11:31:35","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/anatomy-of-a-select-statement-part-2-the-hierarchical-query-clause\/"},"modified":"2021-07-14T13:07:24","modified_gmt":"2021-07-14T13:07:24","slug":"anatomy-of-a-select-statement-part-2-the-hierarchical-query-clause","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/anatomy-of-a-select-statement-part-2-the-hierarchical-query-clause\/","title":{"rendered":"Anatomy Of A SELECT Statement \u2013 Part 2: The Hierarchical Query Clause"},"content":{"rendered":"

My wife and I just welcomed our first child to the world. No, there\u2019s no need to congratulate me; as Chris Rock once said, it\u2019s no big deal, even cockroaches have babies. However, it does mean that I\u2019ve often been up at 3 a.m. rocking my son, mumbling nursery rhymes to him.\u00a0 Which, of course, always has me thinking of hierarchical queries. Of course.<\/p>\n

But first, a definition.\u00a0 A hierarchical query is a type of recursive query that returns a resultset that displays the natural relationship of data.\u00a0 Or, if you like pretty mental pictures, hierarchical queries return datasets in which each record is (potentially) holding hands with the record before it.<\/p>\n

Using a vanilla select query, you can find out from the EMP table all the staff for whom King is their manager. However, you will then need to run a second select statement to find out all the staff who report to them.<\/em> Using a hierarchical query, we can find out, in one move, who reports to King, who reports to them and who reports to them.<\/em> King is Jones\u2019s manager; Jones is Scott\u2019s manager; Scott is Adams\u2019 manager\u2026<\/p>\n

\"Database<\/p>\n

Which brings us back to 3 a.m with me singing blearily to my baby: the toe bone\u2019s connected to foot bone; the foot bone\u2019s connected to the ankle bone; the ankle bone\u2019s connected to the shin bone\u2026<\/em><\/p>\n

Hierarchical data.\u00a0 It\u2019s everywhere, even in nursery rhymes.<\/p>\n

Syntax and Keywords<\/h5>\n

To query hierarchical data, you will need to refer to a family of keywords and build a hierarchical query clause. \u00a0\u00a0The hierarchical query clause comes, in a SELECT statement, right after the where clause.<\/p>\n

\"SELECT<\/p>\n

As I said, there are a few keywords we’ll need to get acquainted with in order to understand hierarchical querying. The best way to find out what they are is to zoom in on the hierarchical query clause and study its syntax.<\/p>\n

\"Hierarchical<\/p>\n

CONNECT BY<\/strong> tells Oracle what the relationship between the parent row and the child row should be. To do this we use a simple condition of the type which we might typically use in a WHERE clause; the only difference here is that we must also use the PRIOR<\/strong> keyword to indicate the parent.<\/p>\n

In our earlier example from the EMP table, because we wanted to show managers followed by their employees, the manager row would be the parent, with the employee being the child. This means that whatever was the employee id in the previous \u2013 prior \u00ad- <\/em>\u00a0row would be the manager id in the next.<\/p>\n

CONNECT BY PRIOR empno = mgr<\/pre>\n
START WITH<\/strong> tells Oracle what is the root<\/em> record of our hierarchy. It does this by also employing a condition.<\/p>\n
In our earlier example, we wanted to start off with the big kahuna, the man with no manager.<\/p>\n
START WITH mgr IS NULL<\/pre>\n
So if we throw it all together, we end up with:<\/p>\n
SELECT\u00a0 ename \"Employee\",\u00a0 empno \"Employee ID\",\u00a0 mgr \"Manager ID\"\r\nFROM emp\r\nCONNECT BY PRIOR empno = mgr\r\nSTART WITH mgr IS NULL;<\/pre>\n
We’ve asked our query to start with the big boss, and that’s quite clear. However, from the third record things become a little less clear, as it is not immediately obvious if Scott reports to Jones or directly to King. The LEVEL<\/strong> pseudocolumn returns a number indicating what level of the hierarchy the record is.<\/p>\n
Another way to show a node’s position in the hierarchy is to use the SYS_CONNECT_BY_PATH<\/strong> function. This function returns the full path from the root node to the current node, and has the following syntax:<\/p>\n
SYS_CONNECT_BY_PATH (column , delimiter)<\/pre>\n
SELECT\u00a0 ename \"Employee\", \u00a0LEVEL, SYS_CONNECT_BY_PATH (ename, '\/') \"Path\"\r\nFROM emp\r\nCONNECT BY PRIOR empno = mgr\r\nSTART WITH mgr IS NULL;<\/pre>\n
\"Employee<\/p>\n
Blake’s a busy boy. He has five direct reports \u2013 Allen, Ward, Martin, Turner and James. If we wanted to sort that list alphabetically, we would not be able to use the normal ORDER BY clause, as that would override the hierarchical order created by the CONNECT BY and we’ll end up with Adams on top and King somewhere in the middle. (GROUP BY overrides the hierarchy too.) To sort within the bounds of our CONNECT BY we must use a different clause:<\/p>\n
ORDER SIBLINGS BY<\/pre>\n
NOCYCLE<\/h5>\n
So far we’ve been working with nice, tame data. Our lines are straight, and our hierarchical tree is uncomplicated. However, the real world can be messy like a plate of spaghetti. Imagine if King, instead of being the overall boss, had to report to a board headed by Jones. That would mean that while King is Jones’ boss, Jones is also King’s boss. If we update the EMP table to reflect this, and rerun our query, we get the following error:<\/p>\n
ORA-01436: CONNECT BY loop in user data<\/pre>\n
To tell Oracle to proceed in spite of loops in our data, we need to modify our CONNECT BY statement to CONNECT BY NOCYCLE<\/em>.<\/p>\n
And, if we wish to identify records where any such loops have been resolved, we would need to include the CONNECT_BY_ISCYCLE<\/strong> pseudocolumn in our query.\u00a0 CONNECT_BY_ISCYCLE returns\u00a0 1 where it identifies a loop; otherwise it returns 0.<\/p>\n
SELECT\u00a0 ename \"Employee\", empno, mgr,\u00a0 CONNECT_BY_ISCYCLE, SYS_CONNECT_BY_PATH (ename, '\/') \"Path\"\r\nFROM emp\r\nCONNECT BY NOCYCLE PRIOR empno = mgr\r\nSTART WITH ename = 'KING'\r\nORDER SIBLINGS BY ename;\r\n<\/pre>\n
Conclusion<\/h5>\n
So that’s it.<\/p>\n
Except that it’s not.\u00a0 Since 11gR2 we have been able to query hierarchical data using recursive subquery factoring clauses or, in other words, the WITH clause.\u00a0 This brings the ANSI standard to Oracle.<\/p>\n
I’ll tell you how it all works in the next part of this article.<\/p>\n","protected":false},"excerpt":{"rendered":"
My wife and I just welcomed our first child to the world. No, there\u2019s no need to congratulate me; as Chris Rock once said, it\u2019s no big deal, even cockroaches have babies. However, it does mean that I\u2019ve often been up at 3 a.m. rocking my son, mumbling nursery rhymes to him.\u00a0 Which, of course, always has me thinking of…<\/p>\n","protected":false},"author":221907,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48416,48497,48520],"coauthors":[],"class_list":["post-73162","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-hierarchical-data","tag-select-statements","tag-syntax"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73162","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221907"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73162"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73162\/revisions"}],"predecessor-version":[{"id":91655,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73162\/revisions\/91655"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73162"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73162"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73162"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73162"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}