Oracle select asterisk connect by join sql-92 combination

Question

Following query shows that select * combined with connect by and left join doesn't return all columns, but only columns used in these conditions. This behavior was useful for me, given that select * should not be used in release, it is useful requesting for 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;

whereas other queries returns 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 found documentation on this functionality, is there any ?

Dropforge

SELECT t1.*, t2.* works. No idea why SELECT * doesn't. – Corker

and adding WHERE name IS NOT NULL AND ( special IS NULL OR special IS NOT NULL ) will then mean those columns appear in the SELECT *. – Corker

Answer 1

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

The most relevant portion:

Oracle processes hierarchical queries as follows:

• A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause predicates.

• The CONNECT BY condition is evaluated.

• Any remaining WHERE clause predicates are evaluated.

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

1. Oracle selects the root row(s) of the hierarchy--those rows that satisfy the START WITH condition.

2. Oracle selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY condition with respect to one of the root rows.

3. 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, and so on. Oracle always selects children by evaluating the CONNECT BY condition with respect to a current parent row.

4. If the query contains a WHERE clause without a join, then Oracle eliminates all rows from the hierarchy that do not satisfy the condition of the WHERE clause. Oracle evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition.

5. Oracle returns the 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".

Trimaran

Answer 2

Not SQL-92, but accomplish 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
;

Keary