In / Exists and Not In / Not Exists

[dejambo]

Good afternoon,

I would like to know what the difference of these operators ... are the same? Return the same result? If possible I would like an example, I already read some texts on the internet ... but not I was able to understand their differences very well. Thanks.

[DanielNN]

In general, they return the same result. One is not necessarily better than the other in terms of performance, it will depend on how your data is, and your query.
However, "not in" and "Not Exists" can return different data when used fields that can be null.
Picking up a ready-made tone:

Select all

SQL> select count(*) from emp where empno not in ( select mgr from emp ); 
 
  COUNT(*) 
---------- 
         0 

Apparently, all overwhelmed is manager. However:

Select all

SQL> select count(*) from emp T1 
  2  where not exists ( select null from emp T2 where t2.mgr = t1.empno ); 
 
  COUNT(*) 
---------- 
         9

Already with "Not Exists" we note that there are 9 people who are not managers. In the "not in" he ignores the null.

Another thing, the return fields within an existations does not matter. It only checks if the query "wheel" or not.

[fbifabio]

Personnel,

according to the "Oracle Database Performance Tuning Guide" in certain circumstances is best to use in than existing and vice versa.

In general, in OLTP environments, if the selective predicate is in the subquery use in. If the selective predicate is on the query parent, use exist.

More details Explain and demonstrate in My SQL Tuning Training: http://www.fabioprado.net/p/sql-tuning- ... racle.html
[] S

Fábio Prado www.fabioprado.net]]