Oracle SQL connect by level

Question

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?

Answer 1

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