Q: Which query technique is better

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 - 00:19:48 CST