CODEWITHSUNDEEP
oracle-databaseouter-joinnot-exists
Zampanò
Zampanò

Reputation: 594

Replacing NOT IN with NOT EXISTS and an OUTER JOIN in Oracle Database 12c

I understand that the performance of our queries is improved when we use EXISTS and NOT EXISTS in the place of IN and NOT IN, however, is performance improved further when we replace NOT IN with an OUTER JOIN as opposed to NOT EXISTS?

For example, the following query selects all models from a PRODUCT table that are not in another table called PC. For the record, no model values in the PRODUCT or PC tables are null:

select model
from product
where not exists(
select * 
from pc
where product.model = pc.model);

The following OUTER JOIN will display the same results:

select product.model
from product left join pc
on pc.model = product.model
where pc.model is null;

Seeing as these both return the same values, which option should we use to better improve the performance of our queries?

Upvotes: 0

Views: 4216

Answers (1)

LoztInSpace
LoztInSpace

Reputation: 5697

The query plan will tell you. It will depend on the data and tables. In the case of OUTER JOIN and NOT EXISTS they are the same.

However to your opening sentence, NOT IN and NOT EXISTS are not the same if NULL is accepted on model. In this case you say model cannot be null so you might find they all have the same plan anyway. However when making this assumption, the database must be told there cannot be nulls (using NOT NULL) as opposed to there simply not being any. If you don't it will make different plans for each query which may result in different performance depending on your actual data. This is generally true and particularly true for ORACLE which does not index NULLs.

Check out EXPLAIN PLAN

Upvotes: 3

Related Questions