Subqueries & Correlated Subqueries By Sagar Jaybhay 2020

Subqueries In SQL Server:

Subqueries are enclosed in parenthesis. Subquery also called an inner query and the query which enclosed that inner query is called an outer query. Many times subqueries can be replaced with joins.

select * from Employee where DepartmentID not in (select distinct DepartmentID  from Department)

Another example select Department_Name,(select count(*) from Employee where DepartmentID=d.DepartmentID) from Department as d;

The above query is an example of use subquery in the select list. The above result can be achieved using join also see below query

select d.Department_Name,COUNT(e.empid) as empcount from Department d
join Employee e on e.DepartmentID=d.DepartmentID
group by d.Department_Name
order by empcount;

According to MSDN, you can nested up to 32 levels. Columns present in subqueries can not be used in the outer select list of a query.

Correlated Subqueries:

If our subquery depends on the outer query for its value then it is called Correlated subqueries. It means subquery depends on outer subquery/ Correlated subqueries are executed for every single row executed by outer subqueries. A correlated subquery can be executed independently

select distinct Department_Name,(select count(*) from Employee where DepartmentID=d.DepartmentID group by DepartmentID) as empcount from Department as d  order by empcount;

What to choose for Performance Subquery or Join?

According to MSDN, there is no big difference between queries that use sub-queries and joins. But in some cases, we need to check the performance and Join produces better performance because the nested query is must be processed for each result of the outer query. In such cases, JOIN will perform better. In general, JOIN works faster as compared to subqueries but in reality, it will depend on the execution plan generated by SQL Server. If the SQL server generates the same execution plan then you will get the same result.