> > Most Generic and maybe fastest:
> >
> > select Name
> > from Table
> > where GroupID in (1,7)
> > group by Name
> > having count(GroupID) = 2;
There are considerable references in the ORACLE texts about IN
being inefficient.
> > select Name
> > from Table
> > where GroupID = 1
> > intersect
> > select Name
> > from Table
> > where GroupID = 7
Dat's da one I would've used.
> > select Name
> > from Table
> > where
> > GroupID = 1
> > and Name in
> > (select Name
> > from Table
> > where GroupID = 7);
Given what is being done in this I suspect it's performance after
optimisation would be much the same as the intersect and this should be
relatively generic.
> > select Name
> > from Table t
> > where
> > GroupID = 1
> > and exists
> > (select Name
> > from Table
> > where
> > Name = t.Name
> > GroupID = 7);
My only comment here is to change the selected item in the Exists
selection to a single character constant since exists matches the record
on the rows located not the selected data. EG
SELECT
Name
FROM
Table t
WHERE
GroupID=1 AND EXISTS (
SELECT
'X'
FROM
Table
WHERE
Name = t.Name AND GroupID=7
)
;
Should functionally be the same but the database won't waste time
handling String data from the Name selected... Of course I'd hope that
the optimiser would remove it anyway but I can't see a reference to that
in 'ORACLE: The Complete reference' from ORACLE Press - Version 7.3
though (O8 is definitely faster so it could the optimiser is even better now).
--
Aaron Scott-Boddendijk
Jump Productions
(07) 838-3371 Voice
(07) 838-3372 Fax
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
