Revision history [back]
 | 1 | initial version |
When using "connect by level" to generate rows, it will create as many rows as the number specified in the "connect by level" clause. If there is a difference in length between the two tables being joined, the result will display null values for the columns of the shorter table for the rows that go beyond its length.
For example, suppose we have two tables, "table1" with four rows and "table2" with two rows. We want to join these tables using "connect by level". The query would be:
SELECT t1.column1, t2.column2 FROM table1 t1 LEFT JOIN table2 t2 ON t2.id = t1.id CONNECT BY LEVEL <= 4;
In this query, we are generating four rows using "connect by level" to match the number of rows in "table1". The result will be:
column1 | column2 --------|-------- value1 | value1 value2 | value2 value3 | null value4 | null
As we can see, for the rows that go beyond the length of "table2", the values in "column2" are null.