0

In the scenario described below, the following two queries achieve the same results: "I want to select all models from a table called PRODUCT that do not appear in a separate table called PC" This can be accomplished with the following code:

select model
from product
where model not in(
select model from pc);

This can also be accomplished by using an OUTER JOIN:

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

Seeing as these two queries achieve the same results, is one preferable over the other? Are there any advantages with using an OUTER JOIN in place of NOT IN?

Zampanò
  • 574
  • 3
  • 11
  • 33
  • 1
    It depends on the platform, the size of the tables, the indexes defined, etc etc. You can get a better idea of what is going on "under the hood" if you look at the execution plan. You should expect there to be no difference in this simple case. – Hogan Aug 05 '17 at 17:50
  • 2
    Most query optimizers these days should optimize both queries to the same plan. – Sergey Kalinichenko Aug 05 '17 at 17:50
  • Check also [this question](https://stackoverflow.com/q/173041/2224701). I personaly use mostly NOT EXISTS too. – Vojtěch Dohnal Aug 05 '17 at 17:54
  • 1
    Strictly speaking these two queries are not equivalent, If `model` is null in `product` table, then the first query always skip this record while the second query always include this record. Secondly, if there is NULL value in the `pc` table, you will alwas get an empty resultset from the first query, while the second query always gives not empty resultset. One may say that you are comparing apples to oranges and ask: "which one is better, an apple or an orange". – krokodilko Aug 05 '17 at 18:34

1 Answers1

0

I think this will help you to understand it better way : http://www.sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/