Q

Using a query with IN in T-SQL

Can I use this in T-SQL:

 
where c.asset_id = v.parent_asset_id and
(c.asset_type,d.asset_type,v.assoc_type) in (select
m.asset_key,n.asset_key,o.association_key from dbo.t_association_rule o,
dbo.t_asset_master m, dbo.t_asset_master n) 

SQL Server does not support using the IN operator in that way, that is, evaluating whether a set of two or more columns are "in" a sub-query. I have a couple of solutions which I believe accomplish the same thing. One uses an inner join, while the other involves using the EXISTS operator and a correlated sub-query. Using an employee table as an example, here's the first:

select e.FirstName, e.LastName
  from Emp e inner join OtherEmp oe 
    on ( oe.FirstName = e.FirstName and 
         oe.LastName = e.LastName );
Here's the second solution:
select e.FirstName, e.LastName
  from Emp e
  where exists ( select * from OtherEmp oe
                   where oe.FirstName = e.FirstName 
                     and oe.LastName = e.LastName );
That fact that in your particular situation, you use columns from different tables should not affect the effectiveness of these two approaches.

For More Information

  • What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

This was first published in May 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close