Can anyone explain the behavior of the below query:-
select level,t.*
from
( select 'one','two'from dual
union all
select 'one','two'from dual
) t
connect by level<=2
There are 2 rows in the inner query. I was expecting 4 rows of output, but i get 6 rows of output. Why is it so and how does this work?
The query starts with one of your two rows and adds both rows, then it continues with the second row and adds both rows again.
Change your query like this:
select level,t.*
from
( select 'one' from dual
union all
select 'two' from dual
) t
connect by level<=2;
This makes it easier to see what happens:
1 one
2 one
2 two
1 two
2 one
2 two
Read this http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm
When level <= 1, you will get each of the records 1 time.
When level <= 2, then you will get each level 1 time (for level 1) + the number of records in the table
(That means for this condition 2 records having level 1 + 2*2 records having level 2. This is the reason you are getting 6 records.)
CONNECT BY LEVEL gives following number of rows x+x2+x3+x4+...x^n = Sx^n
where n is number of LEVEL and x is number of rows in a table
