Re: Q: Which query technique is better

Well having tried this myself. I can only conclude that the second solution runs about twice as fast with a more complicated version that I constructed. It's easy to see why I guess.

An I also discovered that I can still get a count(*) by using the resultset from the first query in a FROM(SELECT ....). Pretty neat but clearly very easy to abuse :)

Simon Harris wrote:

> I have a query which could be expressed either as:
>
> SELECT primaryKey
> FROM SomeTable
> WHERE data IN (1, 2, 3)
> GROUP BY primaryKey
> HAVING COUNT(*) = 3
>
> OR
>
> SELECT S1.primaryKey
> FROM SomeTable S1, SomeTable S2, SomeTable S3
> WHERE S1.primaryKey = S2.primaryKey
> AND S2.primaryKey = S3.primaryKey
> AND S1.primaryKey = S3.primaryKey
> AND S1.data = 1
> AND S2.data = 2
> AND S3.data = 3
>
> Note: I have included what appears to be an unnecessary JOIN condition
> however coming from a Sybase background, I am used to putting these in
> as "hints" to the optimizer.
>
> Also, actual values for data will have very low cardinality. I.e. in a
> table of tens of millions of rows, there will most likely be at most 100
> different possible values of data.
>
> Practically, the only difference I can see is that the second query
> would allow me to COUNT(*) if need be.
>
> Regards,
>
> Simon.
Received on Mon Dec 21 1998 - 05:48:37 CST