In this tutorial, you will learn about the Oracle correlated subquery and also you will learn how to execute Oracle correlated subquery and differences between subquery and correlated Query.
correlated subquery means that subquery depends on the outer query result i.e. the subquery which are depending on the outer query output is known as a correlated subquery.
A Correlated subquery is one in which the inner query depends upon the outer query for its execution. Specifically, it uses a column from one of the tables in the outer query.
The inner query is executed iteratively for each selected row of the outer query. In the case of the independent subquery, the inner query just executes once.
Write a query to list the department names which have at least one employee?
SELECT DEPTNO,DNAME FROM DEPT D WHERE EXISTS (SELECT 1 FROM EMP E WHERE E.DEPTNO=D.DEPTNO)
Write a query to List out all employees details who working in a department.
Write a SQL query to List out all the employees whose salary is greater than avg of their department.
Write a SQL query to list out all the departments which do not have employees at all?
SELECT DEPTNO,DNAME FROM DEPT D WHERE NOT EXISTS (SELECT 1 FROM EMP E WHERE E.DEPTNO=D.DEPTNO)
Differences b/w Non-Correlated SQL and Correlated SQL
For example first off all I want to find out here First highest salary.
SELECT MAX(SAL) FROM EMP
It will display maximum salary from your table after that what I want to do this is inner query later this value I am comparing with outer query where conditional column . So how we can write now-
SELECT * FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP)
When you execute this query what happened the highest salary employee details are displayed on the screen so when you finding the first highest salary in non correlated query .
I think in this section or in this area you will not getting any problem and user also very happy and simply can write it.
But if I want to find out the second highest salary employee details what is happening can see I am going to taking your second highest salary suppose in same to non-correlated.
When you want to find the second highest salary what is the problem here in non-correlated . I need to use two inner query.
find the 2nd highest salary employee details
SELECT * FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP))
For the 1st highest salary purpose we are using one inner and one outer query.
For the 2nd highest salary purpose we are using one inner and two outer query.
Now We want to find the 3rd highest salary employee details what we need to do. we need to add one more inner query to find the 3rd highest salary employee details.
SELECT * FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP)))
suppose some one asking the 30th highest salary employee details like 40th,50th,60th so these positions of people highest and least salary want to ask then how many times I want to write the subquery is very difficult .
So up to now the first second and third highest salary you see carefully non correlated subquery .For the first highest salary employee details 1 inner and 1 Outer query required.
Second highest employee details 2 inner and 1 Outer query required. Third highest employee details 3 inner and 1 Outer query required.
Suppose As per this logic if we want to go what is happening suppose I am finding first highest salary I am going to writing 2 query .Second highest salary I am going to writing 3 query .
Third highest salary I am going to writing 3 query .
so if you like this that what is happening can you see if I want to find Nth highest salary then you need to write N+1 query(inculding innner & Outer Query)
Suppose somebody’s asking finding 40th highest salary employee details then how many times query you need to write 41 times(1 outer & 40 inner) query statement. This is somewhat difficult to the user so to overcome this problem we should go for correlated subquery Mechanism.
Find the Nth highest/Least salary Employee details Using Correlated Query.
SELECT * FROM <TABLE-NAME> <TABLE-ALIAS-NAME1>
WHERE N-1=(SELECT COUNT(COLUMN-NAME) FROM <TABLE-NAME> <TABLE-ALIAS-NAME2 WHERE <TABLE-ALIAS-NAME2>.<COLUMN-NAME>(<>)<TABLE-ALIASNAME1>.<COLUMN-NAME>).
SELECT * FROM EMP e1
WHERE n-1=(SELECT COUNT(DISTINCT SALARY) FROM EMP e2 where e2.SALARY>e1.SALARY)
Finding 1st (n=1) highest salary employee details.
SELECT * FROM EMP E1 WHERE 0=(SELECT COUNT(SAL) FROM EMP E2 WHERE E2.SAL>E1.SAL)
Finding 2nd (n=2) highest salary employee details.
SELECT * FROM EMP E1 WHERE 1=(SELECT COUNT(SAL) FROM EMP E2 WHERE E2.SAL>E1.SAL)
Finding 41th (n=41) highest salary employee details.
SELECT * FROM EMP E1 WHERE 40=(SELECT COUNT(SAL) FROM EMP E2 WHERE E2.SAL>E1.SAL)