SQL: MINUS vs. NOT EXISTS

From: Vikas Agnihotri <onlyforposting_at_yahoo.com>
Date: Sat, 21 Jul 2001 21:28:03 GMT
Message-ID: <77e87b58.0107041625.51bbbe37@posting.google.com>

Need to return all rows in Table A (200,000 rows) which do NOT exist in Table B (10000 rows).

Approach A:

select * from tablea
minus
select * from tableb where pk=constant

Approach B:
select * from tablea a
where not exists (select 1 from tableb b where a.pk=b.pk and b.pk=constant)

The results are the same but Approach A is an order of magnitude faster!

Why? I expected the NOT EXISTS approach would be faster versus doing the MINUS with its implied sort/unique.

Comments? Thanks Received on Sat Jul 21 2001 - 16:28:03 CDT