Condition in WHERE vs condition in CONNECT BY

Question

Can anyone explain to me the difference between the following two queries? Even though they seem the same the results are different.

select concept.concept_id, concept.PARENT_ID 
from ebti_thes_concept_v concept
  start with PARENT_ID = '11025'
  connect by parent_id = prior concept_id
  and exists (..) 

In the second one, the exists predicate is moved from the connect by clause to the where clause.

select concept.concept_id, concept.PARENT_ID
from ebti_thes_concept_v concept
where exists (..)
 start with PARENT_ID = '11025'
 connect by parent_id = prior concept_id;

Answer 1

After reading the documentation, I came to the following conclusions.

One difference between the two queries is that the first query has two condition in the connect by , in order to identify a relationship - a row must fulfill not only the parent_id = prior concept_id condition but also the exists clause.

 connect by parent_id = prior concept_id
 and exists (..)

While the second one has only one condition.

 connect by parent_id = prior concept_id;

The part of the query that makes the distinction more obvious is the start with.

In the first query, all the rows with PARENT_ID = '11025' will be returned and used as root rows. The two conditions of the connect by will be used to find the children of those rows. The exists condition will not be applied to the root rows.

On the other hand, the second query will fetch the rows with PARENT_ID = '11025' and filter them with the exists statement.

As a result, the results will be totally different.