May include but is not limited to: simple, correlated, scalar, list, table valued
A subquery is a query inside another one. I must confess that when I began working with SQL Server (way back in the past) I used subqueries instead of for example joins.
For those who are still here, let me explain why did I do that. Because it is straightforward and simple and requires only the absolute beginner SQL statements. You can do almost everything using subqueries, and if you don’t know how to do something, you definitely will use them.
Now to be a little professional, I’ll introduce the two types of subqueries: correlated and (shocking name) noncorrelated. These names are a little bit self-explanatory. A correlated subquery is one that correlates with the main query, to be more concrete, the subquery depends on the main one. An example:
SELECT * FROM Customers
WHERE CustomerID IN
(SELECT CustomerID FROM Orders WHERE TotalValue > 100)
In contrast, a noncorrelated subquery hasn’t got this relationship.