MS SQL Server Joins Question:

Sub-queries are often referred to as sub-selects, as they allow a select statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.