⟩ How To Use Subqueries with the IN Operators in MS SQL Server?
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.
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.rendc.org main 2006
102 www.rendc.org/html HTMLA 2007
103 www.rendc.org/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.rendc.org main 2006
102 www.rendc.org/html HTMLA 2007
103 www.rendc.org/sql SQL 2007
As you can see, the subquery is equivalent to a list of values.
