Remove Correlated subquery (Use Joins) from sql script

A correlated subquery is a subquery that uses the values of the outer query. In other words, it depends on the outer query for its values. Because of this dependency, a correlated subquery cannot be executed independently as a simple subquery. Moreover, a correlated subquery is executed repeatedly, once for each row evaluated by the outer query. The correlated subquery is also known as a repeating subquery.

Correlated queries are sometimes called synchronized queries.

See the below example

Database: AdventureWorks2017

select p.Name, p.ProductNumber, (select psc.Name from Production.ProductSubcategory psc where psc.ProductSubcategoryID=p.ProductSubcategoryID) as ProductSubcategory, (select pc.name from Production.ProductCategory pc where pc.ProductCategoryID in                 (select ProductCategoryID from Production.ProductSubcategory psc where pc.ProductCategoryID=psc.ProductCategoryID                                                                   and psc.ProductSubcategoryID in (select psc.ProductSubcategoryID from Production.ProductSubcategory psc                                                                   where psc.ProductSubcategoryID=p.ProductSubcategoryID ))) as Productcategory from Production.Product p

See the result  

The same result we will get using joins

Here I am using left join to get the result.

select p.Name, p.ProductNumber, psc.Name as productnubcategoryname, pc.name as productname from Production.Product p left join Production.ProductSubcategory psc on psc.ProductSubcategoryID=p.ProductSubcategoryID left join Production.ProductCategory pc on pc.ProductCategoryID=psc.ProductCategoryID

See the result

  

If we see the execution plan for both query we will get the idea which will be better to use.

Correlated query:

  

From a single table we are reading the data multiple times.

In sub-query row by row operation happen.

Join execution plan

In join set based execution happens.