How to use result set in "IN" Function

Question

How to use result set in "IN" Function

grasshopper26

Old Hand

Points: 396
More actions
June 4, 2010 at 7:38 am

#225347

I would like to use thee result set in in function ex :
Select * from table1 where column1 in (select column2 from table2)
is it the right way of using it ?

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 1

Uhm, why don't you try it out?

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

grasshopper26 Old Hand Points: 396 More actions · Answer 2

Well i tried but its showing up the data for only one value instead of showing data for all the values(from second query )?

WayneS SSC Guru Points: 95461 More actions · Answer 3

What you wrote should work.

Note that you can re-write this query as a JOIN:

SELECT t1.*

FROM Table1 t1

JOIN Table2 t2

ON t1.Column1 = t2.Column2

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 4

Are you saying you want to see all columns from Table2 as well? Then Wayne's example only needs a tiny change:

SELECT t1.*, t2.*

FROM Table1 t1

JOIN Table2 t2

ON t1.Column1 = t2.Column2

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

grasshopper26 Old Hand Points: 396 More actions · Answer 5

grasshopper26

Old Hand

Points: 396

June 4, 2010 at 10:24 am

#1176629

Thanks Guys

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 6

WayneS (6/4/2010)
What you wrote should work.
Note that you can re-write this query as a JOIN:
SELECT t1.*
FROM Table1 t1
JOIN Table2 t2
ON t1.Column1 = t2.Column2

This is not the same as the IN query. It will potentially procedure a different result if a value appears in multipl rows in table2.

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 7

True Nils, but since the OP's question wasn't exactly clear (and he didn't get any more specific later), both Wayne and I just took a stab at it.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 8

I agree, the OP was unclear. I was not criticizing your answer, just pointing out that the two queries would return different results 🙂

WayneS SSC Guru Points: 95461 More actions · Answer 9

Jan Van der Eecken (6/4/2010)
Are you saying you want to see all columns from Table2 as well? Then Wayne's example only needs a tiny change:
SELECT t1.*, t2.*
FROM Table1 t1
JOIN Table2 t2
ON t1.Column1 = t2.Column2

Good call Jan!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2