Home ยป SQL Correlated Subquery

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 TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

Leave a Reply

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