SQL Correlated Subquery

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.

Also Read..

Subquery in SQL

Exist Operator

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 2,875 total views,  1 views today