Correlated Subquery - SQL-Server

Correlated Subquery in MS-SQL Server:

I am writing this article by assuming that you are very well aware with sub-query concept as this is little bit ahead than sub-query. Before we start technically, if you will observe the name “CO-Related subquery”, you will get the concept that this is something highly related to each other and yes, you are absolutely right. Correlated sub-query (also known as repeating subquery) is depend on the main outer query, if you will run it without outer query, you will be greeted with error, while in subquery, if you will run sub-query without outer query, it will give you results set.

MICROSOFT’s definition for Correlated sub-query in its books online:

Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.

Well, enough theory, right????? Now let’s move towards some practical stuff.

We will see this with two table related to cricket match. J

Create one table with cricketer’s personal details with below given query.

CREATE TABLE CricketerDetails

(

ID INT IDENTITY(1,1) CONSTRAINT pk_cricketID PRIMARY KEY NOT NULL,

Name VARCHAR(25) NOT NULL,

Country VARCHAR(10) NOT NULL

)

Now, we will insert few records in it.

INSERT INTO CricketerDetails (Name,Country) VALUES('Sachin Tendulkar','India')

INSERT INTO CricketerDetails (Name,Country) VALUES('Steve Waugh','Australia')

INSERT INTO CricketerDetails (Name,Country) VALUES('Saurav Ganguly','India')

INSERT INTO CricketerDetails (Name,Country) VALUES('Jaisurya','Sri Lanka')

Once you are done with this, please, create one table for score board as follow.

CREATE TABLE PersonalScore

(

MatchName VARCHAR(15) NOT NULL,

Run INT NOT NULL,

CricketerID INT NOT NULL CONSTRAINT fk_cricketid REFERENCES CricketerDetails(id)

)

Now, it is time to insert few records in score board table.

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES('LG cup',100,1)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES('LG cup',10,2)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES('LG cup',17,3)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES('LG cup',0,4)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES('WC-06',10,1)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES('WC-06',99,2)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES('WC-06',137,3)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES('WC-06',10,4)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES('WC-1998',190,1)

Now, If you want to see the tournament (match name) with cricketers name, who made run below than average run with correlated subquery.

SELECT C.Name,p.MatchName,p.Run

FROM PersonalScore p join CricketerDetails c

ON p.cricketerid=c.id

WHERE

run<(SELECT AVG(Run) from PersonalScore ps where p.matchname=PS.matchname)

First subquery will calculate the Average of runs and than it will execute every time outer query get new “MatchName”. Every single “MatchName” from outer query will be passed to subquery and it return the results based on the situation.

Reference: Ritesh Shah