Oracle SQL connect by level

oracle, sql

Solution

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

Problem

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?

Original source