Inner Joins and Hierarchical Queries
- From: "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 14 Dec 2006 11:04:10 -0800
For many of us the first hierarchichal query we ever wrote was on the lines of
1 SELECT EMPNO, ENAME, LEVEL FROM EMP
2 CONNECT BY PRIOR EMPNO = MGR
3* START WITH EMPNO = 7839
SQL> /
EMPNO ENAME LEVEL
---------- ---------- ----------
7839 KING 1
7566 JONES 2
7788 SCOTT 3
7876 ADAMS 4
7902 FORD 3
7369 SMITH 4
7698 BLAKE 2
7499 ALLEN 3
7521 WARD 3
7654 MARTIN 3
7844 TURNER 3
7900 JAMES 3
7782 CLARK 2
7934 MILLER 3
------------------------------------------------------------------------------------------------
Now joining to an inline view
SELECT EMPNO, ENAME, LEVEL, V
FROM EMP, (SELECT 'A' AS V FROM DUAL)
CONNECT BY PRIOR EMPNO = MGR
START WITH EMPNO = 7839
EMPNO ENAME LEVEL V
---------- ---------- ---------- -
7839 KING 1 A
7566 JONES 2 A
7788 SCOTT 3 A
7876 ADAMS 4 A
7902 FORD 3 A
7369 SMITH 4 A
7698 BLAKE 2 A
7499 ALLEN 3 A
7521 WARD 3 A
7654 MARTIN 3 A
7844 TURNER 3 A
7900 JAMES 3 A
7782 CLARK 2 A
7934 MILLER 3 A
The results are as expected.
Now if I run
SELECT EMPNO, ENAME, LEVEL, V
FROM EMP, (SELECT 'A' AS V FROM DUAL
UNION SELECT 'B' FROM DUAL)
CONNECT BY PRIOR EMPNO = MGR
START WITH EMPNO = 7839
Order by LEVEL, ENAME
/
EMPNO ENAME LEVEL V
---------- ---------- ---------- -
7839 KING 1 A
7839 KING 1 B
7698 BLAKE 2 A
7698 BLAKE 2 B
7698 BLAKE 2 A
7698 BLAKE 2 B
7782 CLARK 2 B
7782 CLARK 2 A
7782 CLARK 2 B
7782 CLARK 2 A
7566 JONES 2 B
7566 JONES 2 A
7566 JONES 2 B
7566 JONES 2 A
7499 ALLEN 3 A
7499 ALLEN 3 B
7499 ALLEN 3 A
7499 ALLEN 3 B
7499 ALLEN 3 B
7499 ALLEN 3 A
7499 ALLEN 3 A
7499 ALLEN 3 B
7902 FORD 3 B
7902 FORD 3 A
7902 FORD 3 B
7902 FORD 3 A
7902 FORD 3 B
7902 FORD 3 B
7902 FORD 3 A
7902 FORD 3 A
7900 JAMES 3 A
7900 JAMES 3 B
7900 JAMES 3 B
7900 JAMES 3 A
7900 JAMES 3 A
7900 JAMES 3 B
7900 JAMES 3 B
7900 JAMES 3 A
7654 MARTIN 3 A
7654 MARTIN 3 B
7654 MARTIN 3 B
7654 MARTIN 3 A
7654 MARTIN 3 A
7654 MARTIN 3 B
7654 MARTIN 3 A
7654 MARTIN 3 B
7934 MILLER 3 B
7934 MILLER 3 A
7934 MILLER 3 B
7934 MILLER 3 A
7934 MILLER 3 B
7934 MILLER 3 A
7934 MILLER 3 B
7934 MILLER 3 A
7788 SCOTT 3 B
7788 SCOTT 3 A
7788 SCOTT 3 B
7788 SCOTT 3 A
7788 SCOTT 3 B
7788 SCOTT 3 A
7788 SCOTT 3 A
7788 SCOTT 3 B
7844 TURNER 3 A
7844 TURNER 3 B
7844 TURNER 3 B
7844 TURNER 3 A
7844 TURNER 3 B
7844 TURNER 3 A
7844 TURNER 3 B
7844 TURNER 3 A
7521 WARD 3 B
7521 WARD 3 A
7521 WARD 3 B
7521 WARD 3 A
7521 WARD 3 B
7521 WARD 3 A
7521 WARD 3 A
7521 WARD 3 B
7876 ADAMS 4 A
7876 ADAMS 4 B
7876 ADAMS 4 A
7876 ADAMS 4 B
7876 ADAMS 4 A
7876 ADAMS 4 B
7876 ADAMS 4 A
7876 ADAMS 4 B
7876 ADAMS 4 A
7876 ADAMS 4 B
7876 ADAMS 4 A
7876 ADAMS 4 B
7876 ADAMS 4 A
7876 ADAMS 4 B
7876 ADAMS 4 A
7876 ADAMS 4 B
7369 SMITH 4 A
7369 SMITH 4 B
7369 SMITH 4 A
7369 SMITH 4 B
7369 SMITH 4 A
7369 SMITH 4 B
7369 SMITH 4 A
7369 SMITH 4 B
7369 SMITH 4 A
7369 SMITH 4 B
7369 SMITH 4 A
7369 SMITH 4 B
7369 SMITH 4 A
7369 SMITH 4 B
7369 SMITH 4 A
7369 SMITH 4 B
110 rows selected.
Not what I was expecting. I thought the query would return 28 rows 14 rows
from emp * 2 rows from the inline view.
Instead for each row in emp it is returning power(2,level). Is this
expected?
The database version is 10.2.0.2.0 .
Ian MacGregor
Stanford Linear Accelerator Center
ian@xxxxxxxxxxxxxxxxx
--
http://www.freelists.org/webpage/oracle-l
- Follow-Ups:
- RE: Inner Joins and Hierarchical Queries
- From: Mark W. Farnham
Other related posts:
- » Inner Joins and Hierarchical Queries
- » Re: Inner Joins and Hierarchical Queries
- » RE: Inner Joins and Hierarchical Queries
- » RE: Inner Joins and Hierarchical Queries
- » RE: Inner Joins and Hierarchical Queries
- RE: Inner Joins and Hierarchical Queries
- From: Mark W. Farnham