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), DOB DATE, Code VARCHAR(50) ) INSERT INTO dbo.Sales_Person (ExecutiveID, Name, Dob, Code) VALUES (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, SalesDt DATETIME ) INSERT INTO dbo.Sales_Details (SalesId, ExecutiveID, Amount, SalesDt) VALUES (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 WHERE SP.ExecutiveID IN ( 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
3,943 total views, 1 views today