5

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;
5

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.

1
  • 2
    Yes. The first query says "find me the children of X and then all their blue descendants" while the 2nd says "find me the blue children of X and then all their blue descendants" (where "blue" in the property checked in the subquery). Mar 24 '18 at 10:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.