Grateful if you can provide me with example where I can use the
SELECT * FROM (table)……IN (SELECT ……)
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)
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)
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)
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.