What is Correlated Subquery in SQL?

A correlated subquery is a subquery that uses the values of the outer query and it is evaluated for each row processed by the outer query.

A correlated subquery always depends on outer query for its value. Because of this dependency, a correlated subquery cannot be executed independently like as simple Subquery.

Lets look at an example of correlated subquery in SQL.

First we create a sample tables named Sales_Person and Sales_Details then insert some records in tables as shown below.

CREATE TABLE dbo.Sales_Person
(ExecutiveID INT,
Name VARCHAR(150),
Code VARCHAR(50)

INSERT INTO dbo.Sales_Person
(ExecutiveID, Name, Dob, Code) 
(1, 'Rakesh Kumar', '1989-05-11','EN12Q'),
(2, 'Rohit Singh', '1985-06-19','EN92H'),
(3, 'Sanjay Rawat', '1990-01-20','EN67T'),
(4, 'Akash Grower', '1984-09-15','EN28W'),
(5, 'Lokesh Negi', '1984-08-10','EN66Z'),
(6, 'Amit Raj', '1988-02-17','EN72R'),
(7, 'Jay Prakash', '1989-02-10','EN26Y')

CREATE TABLE dbo.Sales_Details
SalesId INT,
ExecutiveID INT,
Amount INT,

INSERT INTO dbo.Sales_Details
(SalesId, ExecutiveID, Amount, SalesDt)
(101, 1, 4500, '2019-01-15'),
(102, 1, 2900, '2019-01-18'),
(103, 2, 2500, '2019-02-19'),
(104, 4, 4900, '2019-03-15'),
(105, 1, 1500, '2019-05-15'),
(106, 2, 9000, '2019-04-11'),
(107, 3, 7800, '2019-03-12'),
(108, 6, 4400, '2019-08-11'),
(109, 3, 6700, '2019-01-08'),
(110, 5, 3500, '2019-07-18'),
(111, 5, 1500, '2019-06-15'),
(112, 6, 8000, '2019-09-05'),
(113, 7, 1500, '2019-02-07')

Now we have a tables Sales_Person and Sales_Details in SQL Server Database.

As you can see, Sales_Person table contains the personal details of sales executives while Sales_Details contains all the sales done by sales executive on different-different day.

SELECT * FROM dbo.Sales_Person
SELECT * FROM dbo.Sales_Details

Lets assume that you want see the details of those Sales Executive who have done at least two sales in a year.

Following T-SQL returns the executive who have done at least two sales.

SELECT ExecutiveID FROM dbo.Sales_Details
GROUP BY ExecutiveID
HAVING COUNT(ExecutiveID)>=2

In above result you can see that it returns five executives who have done at least 2 sales, but this is not sufficient to fulfill you requirement as it returns only executive Id’s not the details of Sales Person.

Lets see how you can use correlated subquery to get the desired result.

Implementing a Correlated Subquery

You can make above T-SQL query as correlated subquery which references columns from the table of the outer query and returns the details of those Sales Person who have at least two sales as shown below.

SELECT ExecutiveID, Name, Dob, Code
FROM dbo.Sales_Person SP 
   SELECT ExecutiveID FROM dbo.Sales_Details SD
   WHERE SP.ExecutiveID = SD.ExecutiveID
   GROUP BY SD.ExecutiveID
   HAVING COUNT(SD.ExecutiveID)>=2

As you can see, it returns the details of sales person who have done at least two sales.

Recommended for you

Subquery in SQL

Exist Operator

 65 total views,  2 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.