Microsoft readily reminds us that "correlated subqueries" can be included in the WHERE clause:

SELECT au_lname, au_fname
FROM authors
WHERE 100 IN
   (SELECT royaltyper
   FROM titleauthor
   WHERE titleauthor.au_ID = authors.au_id)

This form is discussed in detail here:

http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_11_67lc.asp

But what is not mentioned is that these sub-queries are supported in the SELECT projection list like the following:

SELECT DISTINCT
    title_id
,   au1 = (
        SELECT
            au_id
        FROM
            titleauthor
        WHERE
            au_ord = 1
        AND
            title_id = t.title_id
    )
FROM
    titleauthor AS t

It is an error to use a sub-query that returns more than one row of data. This form is discussed in detaail in "The Practical SQL Handbook" (a link to the book should be listed on this page at songhaysystem.com).