Subqueries and SQL Server

Subqueries are an interesting idea. They are, at the most basic level, a select query within a a SELECT, INSERT, UPDATE or DELETE statement, or inside another subquery. Subqueries can be used anywhere an expression allowed.

In an earlier post, located here on self joins, I explained how to create a very basic system to allow the entry of posts and comments on those posts. Near the end of that post, I showed how to use a subquery that allows you to select all posts and the number of comments on that post:

1. SELECT p.[subject] AS 'Subject', p.[body] AS 'Body', p.postedBy AS 'Posted By' , p.datePosted AS 'Posted On', (SELECT COUNT(T.postID) FROM Posts AS T WHERE T.parentPost = p.postID) AS 'Comments'
2. FROM Posts AS P
3. WHERE P.parentPost IS NULL;

What that query will do is execute the subquery, or inner query, and return the results, which can be returned by themselves, or be used in expressions as constraints.

The remainder of this post will refer to the information presented in the self join post located here.

The following query will return the posts that have comments associated with them. What it will do is execute the subquery and make sure that the value returned is greater than 0. It will use this as the constraint in our WHERE statement.

1. SELECT p.[subject] AS 'Subject', p.[body] AS 'Body', p.postedBy AS 'Posted By' , p.datePosted AS 'Posted On'
2. FROM Posts AS P
3. WHERE (SELECT COUNT(T.postID) FROM Posts AS T WHERE T.parentPost = p.postID) > 0;

For even more information on subqueries, you can look at the Subquery Fundamentals provided by MSDN.