Subqueries & Correlated Subqueries SQL Server 2020

In this article Sagar Jaybhay explain how to write Subqueries and Correlated Subqueries in SQL Server and What to choose for Performance Subquery or Join?

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.

Transaction Link:

Sagar Jaybhay, from Maharashtra, India, is currently a Senior Software Developer. He has continuously grown in the roles that he has held in the more than seven years he has been with this company. Sagar Jaybhay is an excellent team member and prides himself on his work contributions to his team and company as a whole.

Related posts