In this tutorial, you will learn about the Oracle SQL correlated subquery, and also you will learn how to execute Oracle correlated subquery and the differences between the subquery and correlated Query.
correlated subquery means that the subquery depends on the outer query result i.e. the subquery which is depending on the outer query output is known as a correlated subquery.
Table of Contents
Correlated Subquery in Database Management Systems
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)
Output:
Write a query to list out all employee’s details who working in a department.
Output
Write a SQL query to list out all the employees whose salary is greater than avg of their department.
Output
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)
Output
Differences between Non-Correlated SQL and Correlated SQL
For example, I want to find out here First highest salary.
SELECT MAX(SAL) FROM EMP
It will display the maximum salary from your table after that what I want to do this is inner query later this value I am comparing with the 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 to the highest salary employee details are displayed on the screen so when you find the first highest salary in a non-correlated query.
I think in this section or in this area you will not get any problem and the 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 to 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 queries.
find the 2nd highest salary of employee –
Syntax- 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 queries.
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 someone 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 to suppose I am finding first highest salary I am going to writing 2 queries. The second highest salary I am going to writing 3 queries.
The third highest salary I am going to writing 3 queries.
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(including inner & Outer Query)
Nth (N+1)th
1 2
2 3
3 4
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 for the user so to overcome this problem we should go for a correlated subquery Mechanism.
Find the Nth highest/Least salary Employee details Using Correlated Query.
Syntax of Correlated subquery :-
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 41st (n=41) highest salary employee details.
SELECT * FROM EMP E1 WHERE 40=(SELECT COUNT(SAL) FROM EMP E2 WHERE E2.SAL>E1.SAL)