CODEWITHSUNDEEP
sqloracle-database
adarsh
adarsh

Reputation: 63

Oracle SQL connect by level

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?

Upvotes: 4

Views: 19654

Answers (3)

Kishor Kumar
Kishor Kumar

Reputation: 143

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

Upvotes: 0

Arijit
Arijit

Reputation: 1674

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.)

Upvotes: 3

Peter Lang
Peter Lang

Reputation: 55594

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

Upvotes: 5

Related Questions