question

Serge avatar image
Serge asked

select .... in (select)

Hi Mate

Grateful if you can provide me with example where I can use the

SELECT * FROM (table)……IN (SELECT ……)

Thank you.

select
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered

The concept is simply using a subquery to list out the possibilities that you want to find from the chosen column. For instance:

Select *
from SalesPeople
where Id in (select Id from AwardWinners where country = 'USA')
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder 3 avatar image
Scot Hauder 3 answered
SELECT * FROM table1

INTERSECT CORRESPONDING BY *

SELECT * FROM table2

oh well, I never was good at reading minds...

SELECT * FROM table1 [t1]

WHERE [t1].ID IN (SELECT ID FROM table2)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Prakriti Agrawal avatar image
Prakriti Agrawal answered

Hi,

If I am getting you correctly, you are looking for sub-queries. A sub-query is a query written inside a SELECT, INSERT, UPDATE or DELETE statement. an example would be: SELECT [name] FROM teacher WHERE teacher_id IN (SELECT teacher_id FROM student)

sub-query retuns entire data set i.e. here it will return all teacher_id from Student table. Outer query will filter records based on the teacher_id returned from sub-query.

Statements that include a subquery usually take one of these formats: WHERE expression [NOT] IN (subquery) WHERE expression comparison_operator [ANY | ALL] (subquery) WHERE [NOT] EXISTS (subquery)

http://msdn.microsoft.com/en-us/library/ms189575.aspx

Hope this helps, Paige

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dariusj18 avatar image
dariusj18 answered

I will try to help out by giving an example of what I think Prakriti Agrawal was getting at.

Using a subquery with IN is a very inefficient operation. Though I am sure there must be some circumstance this is needed, I always try to avoid doing so. The reason is because when using the IN statement the server actually returns a full recordset for the subquery, but if you use the EXISTS statement, the query engine will stop processing the subquery once it reaches any record that matches the conditions.

SELECT * 
FROM Table1 t1
WHERE EXISTS (
    SELECT * 
    FROM Table2 t2 
    WHERE t2.FK_id = t1.PK_id
)

This may not seem any faster on smaller Table2 tables, but when you are dealing with very large tables, well, it's just much faster.

The slower alternative would be

SELECT *
FROM Table1
WHERE PK_id IN (SELECT FK_id FROM Table2)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Niladri Biswas avatar image
Niladri Biswas answered

Suppose I have a table like

declare @t table(id int identity, name varchar(50))
insert into @t 
    select 'name1' union all select 'name2' union all 
    select 'name3' union all select 'name4' union all
    select 'name5'

I want to find the name of those persons whose id' are less than 3

select * from @t where id in(select id from @t where id < 3)

Output:

id  name
1   name1
2   name2
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

But, depending on the tables involved, this approach can be much more performant and comes with other benefits besides:

SELECT *
FROM A
JOIN (SELECT * 
     FROM B
     WHERE b.SomeColumn = 'SomeValue') 
     AS B
ON A.ID = B.ID;

Or you can get into common table expressions for the same thing:

WITH B AS 
(SELECT * 
FROM B
WHERE b.SomeColumn = 'SomeValue') 

SELECT *
FROM Table A
JOIN B
ON A.ID = B.ID;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.