CODEWITHSUNDEEP
sqloracle-databaseoracle11gsqlplus
Manushree Mishra
Manushree Mishra

Reputation: 87

EXISTS clause and JOIN

Query 1

select ename,deptno 
from emp1 
where exists 
(select deptno from dpt where dpt.deptno=emp1.deptno AND deptno>20);

returns ename corresponding to the conditions imposed on the deptno field in the subquery

Query 2

select ename,deptno 
from emp1 
where exists 
(select deptno from dpt where deptno>20);

but in the query 2 the result contains all the values of the fields ename and deptno

What changes the result set in both the queries? Is it because of the join? How actually does the join work to bring a different result set in query 1? Why is the where condition is being considered in the query 1 and not in query 2 ?

 legends:
 empname is employee name in the table emp,
 deptno is department no. which is the common field in emp and dept tables.

Upvotes: 2

Views: 46

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175726

First query is correlated subquery:

Correlated Subquery is a sub-query that uses values from the outer query. In this case the inner query has to be executed for every row of outer query.

select ename,deptno 
from emp1 
where exists 
(select deptno from dpt where dpt.deptno=emp1.deptno AND deptno>20);

You can rewrite it to:

select e.ename, e.deptno 
from emp1 e
join dpt d
  on d.deptno = e.deptno
where d.deptno > 20;

Second query (noncorrelated/simple subquery):

A noncorrelated subquery is subquery that is independent of the outer query and it can executed on its own without relying on main outer query.

select ename,deptno 
from emp1 
where exists 
  (select deptno from dpt where deptno>20);

Is equivalent to (if there exists something is subquery):

select ename,deptno 
from emp1 
where 1 = 1

Or (if subquery returns nothing):

select ename,deptno 
from emp1 
where 1 = 0

Upvotes: 4

Related Questions