Roberta:

Sub-queries are exactly what their name says, they are a query inside another query.

In the case you outlined, a join is better than a sub-query. They will both work, but the join will usually be faster. (Hidden little secret, SQL will often convert the "in (subquery)" code to a join behind the scenes, for exactly this reason. It just won't tell you it's doing it. Of course, if you look at the query plan, then the cat's out of the bag and you'll see the join.)

The way to write sub-queries is the same way you write any other query. They have the same rules. The only exception is that inline sub-queries can only return one row.

Inline sub-queries are where you have a sub-query in your Select clause, instead of your From or Where clause, or where the sub-query follows "=" instead of "in". They can also be used in Order By, and Group By clauses, the same as Select clauses.

For example:

select

(select title

from dbo.Titles

where gender = People.gender) as Title,

FirstName, LastName

from dbo.People

This assumes you have a "gender" column in your People table, and another gender column in your Titles table, and the two can match.

Again, this would be better solved with a join, but it's just a simple example to demonstrate the concept of an inline sub-query.

But you'll see the main rule, that sub-queries have to work the same way as main-queries. It has Select, it has From, it has Where. Same rules, same syntax, just the added rule that it can only return one row.

Does that help?