Types of sub-queries

SQL Server allow three types of subqueries:

1. Single Row subquery: - Subquery returns only one row
2. Multiple Row subquery: - Subquery returns multiple rows
3. Multiple column subquery : - Subquery returns multiple columns.

---1. Example
Select *
From Table1
Where Col1 > = (Select Sum(Col1) From Table2)

Here in first example inner query will always return single value

---2. Example
Select *
From Table1
Where Col1 IN (Select Col1 From Table2)

Here in second example inner query can returns multiple row

---3. Example
Select *
From Table1 t1
Inner Join (Select Col1, Sum(Col2) As Col2
From Table2
Group By Col1
) t2
On t1.Col1 = t2.Col1

Here in third example subquery used in JOIN will return multiple 2 columns.


There are another two terms used in Subquery called Correlated and NonCorrelated.

A correlated subquery is an inner subquery which is referenced by the main outer query such that the inner query is considered as being executed repeatedly.

Example:
----Example of Correlated Subqueries

SELECT e.EmployeeID
FROM Employee e
WHERE e.ContactID IN
(
SELECT c.ContactID
FROM Person.Contact c
WHERE MONTH(c.ModifiedDate) = MONTH(e.ModifiedDate)
)
GO

A noncorrelated subquery is subquery that is independent of the outer query and it can executed on its own without relying on main outer query.

Example:
----Example of Noncorrelated Subqueries

SELECT e.EmployeeID
FROM Employee e
WHERE e.ContactID IN
(
SELECT c.ContactID
FROM Person.Contact c
WHERE c.Title = 'Mr.'
)
GO