Hi Mate
Grateful if you can provide me with example where I can use the
SELECT * FROM (table)……IN (SELECT ……)
Thank you.
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')
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)
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
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)
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
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;
No one has followed this question yet.