IN/EXISTS and NOT IN/NOT EXISTS

Tuning de Banco, Tuning de SQL, Ferramentas de tuning
  

Poston Thu, 02 Aug 2012 2:58 pm

Good afternoon, I wonder what the difference of these operators ... are equal? return the same result?If possible I would like to a instance, already read some texts on the internet ... but I couldn't understand very well their differences. Thank you.
dejambo

Poston Fri, 03 Aug 2012 9:55 am

In General, they return the same result. One is not necessarily better than the other in terms of performance, will depend on how is your data, and your query.
However, the " NOT IN " and " NOT EXISTS " can return different data when used fields that can be null.
Taking a ready example of tone:
Code: Select all
SQL> select count(*) from emp where empno not in ( select mgr from emp );

  COUNT(*)
----------
         0
Apparently, all is empregrado Manager. However:
Code: Select all
SQL> select count(*) from emp T1
  2  where not exists ( select null from emp T2 where t2.mgr = t1.empno );

  COUNT(*)
----------
         9
with the " not exists " we notice that there are 9 people who are not managers. In " NOT IN " he ignores the NULL.

Another thing, the return within a field EXISTS doesn't matter. It only checks whether the query " " wheel or not.
DanielNN
Location: Fortaleza - CE

att,

Daniel N.N.

Poston Mon, 13 Aug 2012 4:05 pm

Staff, according to the " Oracle Database Performance Tuning Guide ", in certain circumstances it is best to use IN what EXISTS and vice versa.

In General, OLTP environments, if the predicate is selective in subquery use in. If the predicate is selective in query father, use EXISTS.

More details to explain and demonstrate in my workouts of SQL Tuning: http://www.fabioprado.net/p/sql-tuning- ... racle.html [] s Fabio Prado http://www.fabioprado.net
fbifabio
Location: São Paulo - SP

Fábio Prado
www.fabioprado.net



Return to DBA Tuning

Who is online

Users browsing this forum: No registered users and 2 guests