How To Use Subqueries with the IN Operators in 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 fyi_rates table.

SELECT id, url, tag, YEAR(created) As year 
   FROM fyi_links WHERE id IN (SELECT id FROM fyi_rates)
GO
id      url                     tag     Year
101     dev.fyicenter.com       DEV     2006
102     dba.fyicenter.com       DBA     2007
103     sqa.fyicenter.com       SQA     2007

SELECT id, url, tag, YEAR(created) As year 
   FROM fyi_links 
   WHERE id IN (101, 102, 103, 204, 205, 206, 207)
GO
id      url                     tag     Year
101     dev.fyicenter.com       DEV     2006
102     dba.fyicenter.com       DBA     2007
103     sqa.fyicenter.com       SQA     2007

As you can see, the subquery is equivalent to a list of values.

⇒ Using Subqueries with the EXISTS Operators in SQL Server

⇐ What Is a Subquery in a SELECT Query Statement in SQL Server

⇑ Using SELECT Statements with Joins and Subqueries in SQL Server

⇑⇑ SQL Server Database Tutorials