How To Use Subqueries with the IN Operators in MS SQL Server?
Submitted by: AdministratorA 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.
Submitted by: Administrator
<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.
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
☺ | How to download Microsoft SQL Server 2005 Express Edition? |
☺ | What Happens If Strings Are Casted into Wrong Code Pages in MS SQL Server? |
☺ | Can You Roll Back the DDL Statement in a Trigger? |
☺ | What are system databases in MS SQL Server? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Sybase Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |