Doubt dbms navathe

Question

These queries are same they are retriving the fname ,lname of employee who have dependent with same first name ,lname as that of employee - my doubt is that in second query why we are not taking the condition e.ssn=d.essn

1): SELECT E.Fname, E.Lname
FROM EMPLOYEE AS E
WHERE EXISTS ( SELECT *
FROM DEPENDENT AS D
WHERE E.Ssn=D.Essn AND E.Sex=D.Sex
AND E.Fname=D.Dependent_name);

2)SELECT E.Fname, E.Lname
FROM EMPLOYEE AS E
WHERE E.Ssn IN ( SELECT Essn
FROM DEPENDENT AS D
WHERE E.Fname=D.Dependent_name
AND E.Sex=D.Sex );

Comments

query₂:-

E.Ssn IN ( RESULT OF SUBQUERY ) ===>  let RESULT OF SUBQUERY = ( 102,103,105)

it means E.Ssn should equal to any one of the resultant ===> E.Ssn = 102 or E.Ssn = 103 or E.Ssn = 105

there you are internally checking the condition

---

Shaikh brother it correlated subquery we can not take the result directly from inner subquery and compare the result with outer one

We have to take one tuple of outer query and for each tuple of inner query we have to check it

---

We have to take one tuple of outer query and for each tuple of inner query we have to check it

after that , you got some result right ?

then follow my first comment 

---

Ok Shaikh bro ,
means can we say that 1) query is faster then second one because in first query if for atleast one employee the inner query matches the required condition then it returns true  

But in second one for each  one tuple this condition will be checked for each tuple in dependent table (E.Fname=D.Dependent_name
AND E.Sex=D.Sex  ) and then we select all the essn and then later we will compare the ssn of outer query with essn of inner subquery result

---

both queries are co-related queries only

---

Yes both are correlated but i am saying that first one is more efficient then second one

---

no.... those are equivalent

it may help https://gateoverflow.in/257092