Subquery in MS SQL

A Subquery or Inner query or Nested query is the query within query is referred as subquery
Subquery are of two types
1. Co-related Subquery
2. Non Co-related Subquery

Co-related Subquery

Correlated Subquery is a subquery that uses values from the outer query. In this case the inner query has to be executed for every row of outer query.

For example we have table with it's columns named as

Emp (Empid,Empname,Salary,Deptid)

Now if we want to display the Employee ID,Employee Name,Salary of all employees whose salary is above average for their departments.
Then like as above situations we have to use co-related subquery.Query will as shown below

SELECT EmpID,Empname,Salary
FROM Emp
AS A WHERE Salary >
(SELECT AVG(salary) FROM Emp WHERE Deptid= A.Deptid);

Here we achieved our target and get required result.
Note : In Co-related subquery inner will never executed without outer query.

Non Co-related Subquery

Non Co-related Subquery is the subquery in which first inner query executed and based upon it's result outer query return it's result.

Non Co-related Subquery is three types as mentioned below:

1. Sub Query with Where Clause
2. Sub Query with Select Clause
3. Sub Query with From Clause

Sub Query with Where Clause : Sub Query in which inner query is written in where clause are referred as Sub Query with where clause.

For example:consider two tables as Emp (Empid,Empname,Salary,Deptid)
Dept (Deptid,Deptname)

Now we want to fetch employee name and salary of employee belong to 'IT' Department then we have to query in Where Clause as

SELECT Empname,Salary
FROM emp
WHERE Deptid =
(SELECT Deptid
FROM Dept WHERE Deptname = 'IT')

Here inner query will return Deptid of 'IT' Department which is used by outer query to fetch detail of employee.

Sub Query with Select Clause : Subquery in which inner query is written in select clause are referred as Sub Query with select clause.

For example:consider two tables as Emp (Empid,Empname,Salary,Deptid)
Dept (Deptid,Deptname)

Now we want to fetch total number of employee and total number of department in two different column from two different table mentioned above then we have use Subquery with Select clause as

SELECT
COUNT(empid) as total employee,
(SELECT COUNT(Deptid)
FROM Dept) as total department
FROM Emp

Here inner query will count total number of department and display them with number of employee.

Sub Query with From Clause : Subquery in which inner query is written in From clause are referred as Sub Query with From clause.

For example:consider two tables as Products (Productid,Productname,Price)
Orders (Orderid,Orderdate,Unitprice,Productid)

Now we want to fetch Product name and maximum unit price in two different column from two different tables then we can use Subquery with From Clause and inner join with it and the query will be

SELECT Y.Productname as Product Name,X.MAX(Unitprice) as Maximum Unit Price
FROM (SELECT Productid,
MAX(Unitprice)
FROM Orders
GROUP BY Productid) as X
INNER JOIN Products as Y on X.Productid = Y.Productid

Here inner query return the table with alias X in From Clause and it joined with Products table with alias Y.

I hope this article will help you.