question

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;