MS SQL Server Concepts and Programming Question:

Download Questions PDF

A subquery can be used with the IN operator as "expression IN (subquery)". The subquery should return a single column with one or more rows to form a list of values to be used by the IN operation. The following tutorial exercise shows you how to use a subquery with the IN operator. It returns all links with ids in the ggl_rates table.
<pre>
SELECT id, url, tag, YEAR(created) As year
FROM ggl_links WHERE id IN (SELECT id FROM ggl_rates)
GO
id url tag Year
101 www.globalguideline.com main 2006
102 www.globalguideline.com/html HTMLA 2007
103 www.globalguideline.com/sql SQL 2007

SELECT id, url, tag, YEAR(created) As year
FROM ggl_links
WHERE id IN (101, 102, 103, 204, 205, 206, 207)
GO
id url tag Year
101 www.globalguideline.com main 2006
102 www.globalguideline.com/html HTMLA 2007
103 www.globalguideline.com/sql SQL 2007
</pre>
As you can see, the subquery is equivalent to a list of values.