Re: NOT IN conditions are extremly SLOW

Robert Wehofer schrieb am 06.10.2005 in <5c0d3$4344dea5$d4badae4$31884_at_news.chello.at>:

>> SELECT
>>   COUNT(*)
>> FROM
>>   (SELECT
>>      COL_A,COL_B
>>    FROM
>>      TABLE_A
>>    MINUS
>>    SELECT
>>      COL_A,COL_B
>>    FROM
>>      TABLE_B
>>   )

> Your statement is much faster, thank you.

> But how do I get all columns of TABLE_A selected?

> SELECT * FROM (SELECT COL_A, COL_B FROM TABLE_A MINUS SELECT COL_A, COL_B
> FROM TABLE_B)

> only retrieves columns COL_A and COL_B, but I would like to have all columns
> of TABLE_A

try

SELECT
  *
FROM
  TABLE_A
WHERE
  (COL_A,COL_B) in
   (SELECT
      COL_A,COL_B
    FROM
      TABLE_A
    MINUS
    SELECT
      COL_A,COL_B
    FROM
      TABLE_B
   )

and
SELECT
  *
FROM
  TABLE_A A
JOIN
  (COL_A,COL_B) in
   (SELECT
      COL_A,COL_B
    FROM
      TABLE_A
    MINUS
    SELECT
      COL_A,COL_B
    FROM
      TABLE_B
   ) X
ON
  (X.COL_A=A.COL_A) and (X.COL_B=A.COL_B)

I suppose there are more efficient ways, but this sould work anyway.

> Robert

Andreas

BTW: What Factor faster?

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de