Correlated subqueries

In the sub query, SQL evaluates the subquery once, substitutes the result of the subquery in the search condition, and evaluates the outer-level SELECT based on the value of the search condition. You can also write a subquery that SQL may need to re-evaluate as it examines each new row (WHERE clause) or group of rows (HAVING clause) in the outer-level SELECT. This is called a correlated subquery.

We use the correlated subquery in the following clause

• Correlated subquery in select-list
  
• Correlated subquery in a WHERE Clause
  
• Correlated subquery in a HAVING Clause
  
• Correlated subqueries in an UPDATE statement
  
• Correlated subqueries in a DELETE statement

I tried to test the sub query in Select-List and the following scripts will assist you to try the query.

DDL

CREATE TABLE Tbl_Employee
(
id NUMERIC,
emp_name VARCHAR(50),
boss_id NUMERIC
)

DML

INSERT INTO dbo.Tbl_Employee values
(1,'Sandya',1)
INSERT INTO dbo.Tbl_Employee values
(2,'Nidya',1)
INSERT INTO dbo.Tbl_Employee values
(3,'Ramya',1)
INSERT INTO dbo.Tbl_Employee values
(4,'Priya',2)
INSERT INTO dbo.Tbl_Employee values
(5,'Balakrishnan',1)

Sample Query

SELECT Emp.emp_name [Employee Name],
(
SELECT Boss.emp_name FROM dbo.Tbl_Employee Boss
WHERE Boss.id = Emp.boss_id
) [Boss Name]
FROM
dbo.Tbl_Employee Emp

Result