Welcome Omi,
Here is excerpts from BOL: In some Transact-SQL statements, the subquery can be evaluated as if it were an independent query. Conceptually, the subquery results are substituted into the outer query (although this is not necessarily how Microsoft SQL Server actually processes Transact-SQL statements with subqueries). There are three basic types of subqueries. Those that:
- Operate on lists introduced with IN, or those that a comparison operator modified by ANY or ALL.
- Are introduced with an unmodified comparison operator and must return a single value.
- Are existence tests introduced with EXISTS.
Here are the code sample from BOL(please not that the subquery in 1st query will return more then one record):
USE
AdventureWorks;
GO
SELECT
DISTINCT c.LastName, c.FirstName, e.EmployeeID
FROM
Person.Contact AS c JOIN HumanResources.Employee AS e
ON
e.ContactID = c.ContactID
WHERE
5000.00 IN
(SELECT BonusFROM Sales.SalesPerson spWHERE e.EmployeeID = sp.SalesPersonID) ;
GO
/* SELECT statement built using a subquery. */
SELECT
Name
FROM
AdventureWorks.Production.Product
WHERE
ListPrice =
(SELECT ListPriceFROM AdventureWorks.Production.ProductWHERE Name = 'Chainring Bolts' )
/* SELECT statement built using a join that returns
the same result set. */
SELECT
Prd1. Name
FROM
AdventureWorks.Production.Product AS Prd1JOIN AdventureWorks.Production.Product AS Prd2ON (Prd1.ListPrice = Prd2.ListPrice)
WHERE
Prd2. Name = 'Chainring Bolts'
hth