MS SQL Server Concepts and Programming Question:
Download Questions PDF

How To Use Subqueries with the IN Operators in MS SQL Server?

Answer:

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.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

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

Download MS SQL Server Interview Questions And Answers PDF

Previous QuestionNext Question
What Is a Subquery in a SELECT Query Statement in MS SQL Server?How To Use Subqueries with the EXISTS Operators in MS SQL Server?