CONNECT BY was originally intended (and still is used) for traversing a hierarchy. For example, if your employee table is:
SQL> desc scott.emp
Name Null? Type
----------------------------------------- -------- --------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
Then an EMPNO might also be a MGR (manage) who is themselves an employee and so forth.
So query-wise, you want to be able to:
- start with an employee
- find their manager
- "loop back" and lookup that manager as an *employee*
- find *their* manager
and so forth. We use CONNECT BY for that
SQL> select empno, ename, mgr
2 from scott.emp
3 start with ename = 'SMITH'
4 connect by empno = prior mgr;
EMPNO ENAME MGR
---------- ---------- ----------
7369 SMITH 7902
7902 FORD 7566
7566 JONES 7839
7839 KING
4 rows selected.
So Smith reports to Ford, who reports to Jones, who reports to King.
Someone came up with the simple idea that if we use a CONNECT BY that always "succeeds", we can generate an infinitely large data set, eg
SQL> select rownum from dual
2 connect by 1=1; -- always true
ROWNUM
----------
1
2
3
4
5
...
...
The "connect by level" is just a variation on that them.
select level from dual connect by level <= n;
will return rows with values 1 .. n