Subqueries are essentially a query (or queries) inside another query. That is for example a SELECT statement inside another SELECT statement.
There is already enough information available in the internet and written materials about subqueries including their common uses, when and how to use them (see bottom of post) and I will not elaborate on that further. However, I would like to share my experience where I almost abandoned an elegant solution using a subquery in favor of another approach because I couldn't get it to work for some reason.
Note that the scripts below are merely to illustrate the point and in no way demonstrates best practices, especially considerations in execution performance and actually could be achieved without a subquery but again only to simplify the point. [more]
I had a table for example named "Product" with a few columns in it so the subquery should have been as simple as:
SELECT * FROM (SELECT * FROM Products)
But for some reason I was getting Incorrect syntax near ')'. I know I have done and seen this a couple of times. Or I thought I did since actually during those times I really had a "FROM <an actual table> INNER JOIN <the sub query>". So rather than selecting from a subquery I was selecting from an actual table/view which was joined to a subquery. But even if that was the case, clearly this should be possible and I'm quite sure I was missing something.
After a short walk and little stretching I turns out that I was indeed missing something, an alias for the subquery (or the result set of the subquery).
Changing the query to the following worked:
SELECT * FROM (SELECT * FROM Products) <Alias>
or
SELECT * FROM (SELECT * FROM Products) AS <Alias>
where <Alias> is any valid text/string that can be used as a table alias. (enclose in [ and ] if you have spaces)
That's it. I'm not sure why an alias should be required that I guess that's up to another discussion (or update sometime).
Googling TSQL subqueries comes up with this good article on Using a Subquery in a T-SQL statement which contains some of the common uses for sub queries. But while your on it, have a look at similar techniques like using JOINs instead when appropriate or Common Table Expressions (CTE) or even the new CROSS and OUTER APPLY in SQL 2005.