correlated subqueries in SQL | How to write correlated subqueries in SQL?

In my previous article I have provided multiple complex sql queries for interviews and subqueries as well. In this article I would like to give you details about another most asked topic in interviews that is correlated subqueries with real life examples. The Correlated subqueries are most important topic and it is used in so many real world scenarios.

What you will find in this article?

1. What is Suqueries in SQL?
2. What is correlated subqueries with real example?

What is mean by Subqueries in SQL?

Subquery is query within query. The output of outer query is assigned to the column which is used in where condition of outer query. The subquery output is returning only one output value and based on that output value the outer query is executed. Subqueries are used in various real life scenarios like report development, Application logic development, Performance tuning of query.

“Subquery is Query within a query in which the value of inner query is assigned to the

outer query and outer query is executed…”

Types of Subqueries :

1.Single Row Subquery

2.Multirow Subquery

3.Correlated Subquery

4.Nested SubQueries

5.Scalar Subqueries

Real Life example :

If you want to fetch the records of employees where the department name is IT and Oracle :

Select Employee_No,Employee_Name from Employee

where Department_Name in

(Select Department_Name from Employee where Department_name in (‘IT’,’Oracle’));

What is correlated subqueries in SQL with example?

Correlated Query is nothing but the subquery whose output is depending on the inner query used in that query. Correlated query is the query which is executed after the outer query is executed. The outer query is always dependent on inner query. The approach of the correlated subquery is bit different than normal subqueries. In normal subqueries the inner queries are executed first and then the outer query is executed but in Correlated Subquery outer query is always dependent on inner query so first outer query is executed then inner query is executed. Correlated Subqueries always uses operator like Exist,Not Exist,IN,Not IN.

“Correlated Queries are also called as Synchronized queries…”

Steps to execute Correlated subqueries :

Step 1 : Executes the outer Query

Step 2 : For Each row of outer query inner subquery is executed once

Step 3 :The result of correlated subquery determines whether the fetched row should be the part of our output results

Step 4 : The Process is Repeated for all Rows

“It is not recommended to use Correlated Subqueries as it slows down the performance”

Real Life Example:

Fetch the Employees who have not assigned a single department.

Select * from Employee E where Not exist

(Select Department_no From Department D where E.Employee_id=D.Employee_ID);

Execution of query Steps :

Step 1:

Select * from Employee E ;

It will fetch the all employees

Step 2:

The First Record of the Employee second query is executed and output is given to first query.

(Select Department_no From Department D where E.Employee_id=D.Employee_ID);

Step 3:

Step 2 is repeated until and unless all output is been fetched.

The above query is correlated subquery in SQL . I hope you like this article. If you like this article or if you want any other examples kindly comment in comments section.