SQL NESTED Vs CORRELATED SUBQUERY Statement

Subqueries are divided into two types: nested and correlated.The differences between query types will be discussed in this article.

SQL NESTED SUBQUERY Statement

When you have a subquery in the WHERE or HAVING clause of another subquery, it's called nested.

Inner query runs first and only once in Nested Query.

The result of the Inner query is used to run the outer query. As a result, the Outer query is executed using the Inner query.

Example: Find details of Customer who have ordered in Order table.

CID	Name	Gender	City
C-1	Vinoth Kumar	Male	Pune
C-2	Azaghu Varshith	Female	Delhi

Let's look at Order table

OrderNo	CID	Amount	OrderDate
1	C-2	3000	2008/03/23 12:56:38
2	C-1	3000	2002/09/12 14:34:39
3	C-1	4000	2008/09/25 19:34:35
4	C-1	7000	1996/08/22 11:34:30

In below query CID in customer is to check who have order within same identification in Order

Mysql,Sql-server,Sqlite,Ms access

SELECT * FROM CO_Customer WHERE 
CID IN (SELECT CID FROM CO_Order);

The innermost subquery will be done first, and then the next subquery will be executed depending on its result, and finally the outer query will be executed based on that result.

The amount of nesting you can do depends on the implementation.

Output: The output will be

cid	customername	gender	city
C-1	Vinoth Kumar	Male	Pune
C-2	Azaghu Varshith	Female	Delhi

SQL CORRELATED SUBQUERY Statement

A Correlated Subquery is one that runs after the outer query has completed.

As a result, correlated subqueries use the opposite approach to conventional subqueries. The execution of the connected subquery is as follows:

A row is returned to the outer query.
The subquery (the correlated subquery) is run once for each candidate row in the outer query.
The linked subquery's results are utilised to determine whether or not the candidate row should be included in the result set.

For each row, the operation is repeated.

Correlated subqueries are different from regular subqueries in that the nested SELECT query refers back to the table from the initial SELECT statement.

Example: Let's look at Employee to execute correlated subquery

ID	Name	Gender	City	Dept	Salary
1	Balan	Male	Bangalore	Database	5000
2	Vidyavathi	Female	Pune	Programming	4000
3	Sakunthala	Female	Delhi	Security	11000
4	Dharan	Male	Bangalore	Database	32500

The following query identifies employees with salaries that are higher than the mean for all employees:

My-sql,Sql-server,sqlite,Ms access

SELECT ID, 
    Name,  
    salary
FROM
    Employee
WHERE
    Salary > (SELECT AVG(Salary) FROM Employee);

Output: The output of above query is who will get high salary than mean of all employees salary

id	name	salary
4	Dharan	32500

SQL Difference Between NESTED SUBQUERY And CORRELATED SUBQUERY Statement

Here we discuss the contrast in nested & correlated subquery clause

NESTED SUBQUERY	CORRELATED SUBQUERY
A query is typed inside another query in a nested query, and the result of the inner query is used in the execution of the outer query.	A question is nested inside another query in a Correlated query, and the inner query uses the values from the outer query.
Methodology Inner query executes first, and only once, in a bottom-up method. The result of the Inner query is used to run the outer query.	Top to Bottom Approach, in which the outer query executes first, followed by the inner query for each row of the outer query.
Dependency The execution of an inner query is independent of the execution of an outer query.	The inner query is influenced by the outer query.
Performance is superior than Correlated Query, but slower than Join Operation.	Performs slower than both Nested Query and Join operations since the inner query is executed for each outer query.

All Tutorials