intersect

This post is just asking for an advise.

I have 6 different querys returning me this results

select * from threeLeptonCut;
select * from zVetoCut;
select * from topcut;
select * from jetVetoCut;
select * from leptoncuts;
select * from Misseecuts;

2 24485 14,7936 -310,021 bkg*.root
6 24416 31,2464 10,4272 bkg*.root
11 24411 22,04 -17,4846 bkg*.root
....

-

2 24485 14,7936 -310,021 bkg*.root
10 24461 75,7698 -1,09335 bkg*.root
13 24434 89,0862 -65,6022 bkg*.root
...

etc.

now i want to join the show all the tuples the appear in all 6 query results.
What do you think is the fastest way or lower time cost to do it?

Intersect, in, exists?, any other better?

On SQL Server 2005 INTERSECT operator is best approach,

Code Snippet

Select * From threeLeptonCut

Intersect

Select * From zVetoCut

Intersect

Select * From topcut

Intersect

Select * From jetVetoCut

Intersect

Select * From leptoncuts

Intersect

Select * From Misseecuts;

On SQL Server 2000 EXISTS is one of the best method. But for your query you have to check all the columns.

Code Snippet

Select * From threeLeptonCut T1

Exists (Select * From zVetoCut T2 Where T1.Col1=T2.Col1 And T1.Col2=T2.Col2 And T1.Col3=T2.Col3 And T1.Col4=T2.Col4)

And Exists (Select * From topcut T3 Where T1.Col1=T3.Col1 And T1.Col2=T3.Col2 And T1.Col3=T3.Col3 And T1.Col4=T3.Col4)

And Exists (Select * From jetVetoCut T4 Where T1.Col1=T4.Col1 And T1.Col2=T4.Col2 And T1.Col3=T4.Col3 And T1.Col4=T4.Col4)

And Exists (Select * From leptoncuts T5 Where T1.Col1=T5.Col1 And T1.Col2=T5.Col2 And T1.Col3=T5.Col3 And T1.Col4=T5.Col4)

And Exists (Select * From Misseecuts T6 Where T1.Col1=T6.Col1 And T1.Col2=T6.Col2 And T1.Col3=T6.Col3 And T1.Col4=T6.Col4)

|||I had just logged on to post a similar query to Luis. From his description I think he is (as I am) looking for the Intersection of the six queries. I have 4 tables, each containing 1 int column, and I want a list of integers which appear in all 4 tables only.

Intersect only seems to work with two sets, not four.

Any ideas much appreciated.

Greg.

|||

You can use INTERSET on multiple sets..

Here the sample,

Code Snippet

Create Table #data1 (

[Data] Varchar(100)

);

Insert Into #data1 Values(1);

Insert Into #data1 Values(2);

Insert Into #data1 Values(3);

Insert Into #data1 Values(4);

Insert Into #data1 Values(5);

Create Table #data2 (

[Data] Varchar(100)

);

Insert Into #data2 Values(2);

Insert Into #data2 Values(30);

Insert Into #data2 Values(4);

Create Table #data3 (

[Data] Varchar(100)

);

Insert Into #data3 Values(2);

Insert Into #data3 Values(30);

Insert Into #data3 Values(4);

--SQL Server 2005

Select * From #Data1

Intersect

Select * From #Data2

Intersect

Select * From #Data3

--SQL Server 2000

Select * From #Data1 Where

Exists (Select 1 From #Data2 Where #Data1.Data = #Data2.Data)

And Exists (Select 1 From #Data3 Where #Data1.Data = #Data3.Data)

|||Thanks Manivannan. I've just realised that - need to get my "set-based" hat on again!.

Greg.

|||

Did you mean to say UNION instead of UNION ALL? he only wants the common rows, right?

|||phdiwakar thanks for pointing that .. I really surprised about this.. I edited now. |||

Hi Manivannan.D.Sekaran,

The OP is asking about comparison among different methods and for methods not mentioned, not how to do it using the methods mentioned in the list. Beside that, if you want to simulate the INTERSECT operator using EXISTS, then you need to use "select distinct" and also check for NULL values.

Example:

declare @.t1 table(c1 int)

declare @.t2 table(c1 int)

insertinto @.t1 values(1)

insertinto @.t1 values(1)

insertinto @.t1 values(null)

insertinto @.t2 values(2)

insertinto @.t2 values(null)

insertinto @.t2 values(1)

select c1 from @.t1

INTERSECT

select c1 from @.t2

selectdistinct

c1

from

@.t1 as t1

where

exists(

select*

from @.t2 as t2

where t2.c1 = t1.c1 or(t1.c1 isnulland t2.c1 isnull)

)

select

c1

from

@.t1 as t1

where

exists(

select*

from @.t2 as t2

where t2.c1 = t1.c1

)

go

AMB