Oracle select asterisk connect combining sql-92 combination

Question

The following query shows that select * in combination with connect by and left join does not return all columns, but only the columns used in these conditions. This behavior was useful to me, given that select * should not be used in a release, it is useful to request data.

 with t1 as ( select 1 id, 0 parent, 'ROOT' name from dual union all select 2 id, 1 parent, 'CHILD-1' name from dual union all select 3 id, 1 parent, 'CHILD-2' name from dual ), t2 as ( select 1 t1id, 'node' special from dual ) select * from t1 left join t2 on t2.t1id=t1.id start with id = 2 connect by prior parent = id; 

while other queries return all columns

  select * from t1 start with id = 2 connect by prior parent = id; select * from t1 left join t2 on t2.t1id=t1.id; 

I could not find the documentation for this function, is there anything?

Answer 1

I believe that the documentation you are looking for can be found here: Hierarchical Queries

The most important part:

Oracle processes hierarchical queries as follows:

• First, it evaluates the join, if present, regardless of whether the join is specified in the FROM clause or with WHERE clause predicates.

• The CONNECT BY clause is satisfied.

• All other predicates of the WHERE clause are evaluated.

Oracle then uses the information from these ratings to form a hierarchy using the following steps:

• Oracle selects the root row of the hierarchy — those rows that satisfy the START WITH clause.

• Oracle selects the children of each root row. Each child row must satisfy the CONNECT BY clause with respect to one of the root rows.

• Oracle selects successive generations of child rows. Oracle first selects the children of the rows returned in step 2, and then the children of those children, etc. Oracle always selects children by evaluating the CONNECT BY clause relative to the current parent row.

• If the query contains a WHERE clause without a join, then Oracle excludes all rows from the hierarchy that do not satisfy the WHERE clause. Oracle evaluates this condition for each row separately, and does not delete all child rows that do not satisfy the condition.

• Oracle returns rows in the order shown in Figure 9-1. In the diagram, children appear below their parents. For an explanation of hierarchical trees, see Figure 3-1 “Hierarchical Tree”.

Answer 2

Not SQL-92, but do SELECT * + CONNECT BY + LEFT JOIN

 WITH t1 AS ( SELECT 1 ID, 0 PARENT, 'ROOT' NAME FROM dual UNION ALL SELECT 2 ID, 1 PARENT, 'CHILD-1' NAME FROM dual UNION ALL SELECT 3 ID, 1 PARENT, 'CHILD-2' NAME FROM dual ), t2 AS ( SELECT 1 t1id, 'node' special FROM dual ) SELECT * FROM t1, t2 WHERE t2.t1id(+) = t1.ID START WITH ID = 2 CONNECT BY PRIOR PARENT = ID ;