Subqueries In SQL Server
Subqueries are enclosed in parentheses. Subquery is also called an inner query and the query which encloses 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 using subquery in the select list. The above result can be achieved using join also; see the 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 nest up to 32 levels.
Columns present in subqueries cannot 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 a Correlated Subquery. 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 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 the SQL Server. If the SQL server generates the same execution plan then you will get the same result.