The integers in the supervisor_id are actually pointers to
other rows in the corporate_slaves table. Need to display
an org chart? With only standard SQL available, you'd write a program
in the client language (e.g., C, Lisp, Perl, or Tcl) to do the
following:

query Oracle to find the employee where supervisor_id is
null, call this $big_kahuna_id

Notice that we've used a subquery in the START WITH clause to find out
who is/are the big kahuna(s). For the rest of this example, we'll just
hard-code in the slave_id 1 for brevity.

Though these folks are in the correct order, it is kind of tough to tell
from the preceding report who works for whom. Oracle provides a magic
pseudo-column that is meaningful only when a query includes a CONNECT
BY. The pseudo-column is level:

The general rule in Oracle is that you can have a subquery that returns
a single row anywhere in the select list.

Does this person work for me?

Suppose that you've built an intranet Web service. There are things
that your software should show to an employee's boss (or boss's boss)
that it shouldn't show to a subordinate or peer. Here we try to figure
out if the VP Marketing (#2) has supervisory authority over Jane Nerd
(#7):

Apparently not. Notice that we start with the VP Marketing (#2) and
stipulate level > 1 to be sure that we will never
conclude that someone supervises him or herself. Let's ask if the Big
Boss Man (#1) has authority over Jane Nerd:

Even though Big Boss Man isn't Jane Nerd's direct supervisor, asking
Oracle to compute the relevant subtree yields us the correct result. In
the ArsDigita Community System Intranet module, we decided that this
computation was too important to be left as a query in individual Web
pages. We centralized the question in a PL/SQL procedure:

Family trees

What if the graph is a little more complicated than employee-supervisor?
For example, suppose that you are representing a family tree. Even
without allowing for divorce and remarriage, exotic South African
fertility clinics, etc., we still need more than one pointer for each
node:

specifies the relationship between parent rows and child rows of the
hierarchy. condition can be any condition as described in
"Conditions". However, some part of the condition must use the
PRIOR operator to refer to the parent row. The part of the condition
containing the PRIOR operator must have one of the following
forms:

PL/SQL instead of JOIN

The preceding report is interesting but confusing because it is hard to
tell where the trees meet in marriage. As noted above, you can't do a
JOIN with a CONNECT BY. We demonstrated the workaround of burying the
CONNECT BY in a view. A more general workaround is using PL/SQL:

PL/SQL instead of JOIN and GROUP BY

Suppose that in addition to displaying the family tree in a Web page, we
also want to show a flag when a story about a family member is
available. First we need a way to represent stories:

create table family_stories (
family_story_id integer primary key,
story clob not null,
item_date date,
item_year integer,
access_control varchar(20)
check (access_control in ('public', 'family', 'designated')),
check (item_date is not null or item_year is not null)
);
-- a story might be about more than one person
create table family_story_relative_map (
family_story_id references family_stories,
relative_id references family_relatives,
primary key (relative_id, family_story_id)
);
-- put in a test story
insert into family_stories
(family_story_id, story, item_year, access_control)
values
(1, 'After we were born, our parents stuck the Wedgwood in a cabinet
and bought indestructible china. Philip and his father were sitting at
the breakfast table one morning. Suzanne came downstairs and, without
saying a word, took a cereal bowl from the cupboard, walked over to
Philip and broke the bowl over his head. Their father immediately
started laughing hysterically.', 1971, 'public');
insert into family_story_relative_map
(family_story_id, relative_id)
values
(1, 8);
insert into family_story_relative_map
(family_story_id, relative_id)
values
(1, 9);
insert into family_story_relative_map
(family_story_id, relative_id)
values
(1, 7);

To show the number of stories alongside a family member's listing, we
would typically do an OUTER JOIN and then GROUP BY the columns other
than the count(family_story_id). In order not to disturb
the CONNECT BY, however, we create another PL/SQL function:

Performance and Tuning

Oracle is not getting any help from the Tree Fairy in producing results
from a CONNECT BY. If you don't want tree queries to take O(N^2) time,
you need to build indices that let Oracle very quickly answer questions
of the form "What are all the children of Parent X?"

Interested readers should check out Joe Celko's nested set model for representing trees in SQL. No need to be locked into proprietary SQL dialects and probably a couple of orders of magnitude faster to query!

Related Links

representing an m-ary tree in sql- This method allows for very fast retrieval of descendants and modification of an m-ary tree. no self-referencing or nested select statements are necessary to retrieve some or all descendants. the labelling of nodes is such that it allows very simple and fast querying for DFS order of nodes. it was partially inspired by huffman encoding. (contributed by Anthony D'Auria)

Dead link- The link above to Dartmouth college appears to be dead, but Web Archive kept a copy of the page (contributed by Tom Lebr)