Question: Can someone explain the difference between `IN` and `EXISTS` and `NOT IN` 
and `NOT EXISTS`. 
Because I have read that `EXISTS` will work better than `IN` and `NOT EXISTS` will work better than `NOT IN`.
The query i created is follows..

```
delete from tt_left t
 where t.val = 0
   and t.text in (select t1.text
                      from tt_left t1
                     where t.text = t1.text
                       and t.resulttext = t1.resulttext
                       and t.val = 0
                       and t1.val = 1);

```

How to convert this to `EXISTS`?
Is there any other better method?

Answer: 
The optimiser will treat `IN` and `EXISTS` the same (*if* the IN clause is not a list of constants)  

That's because it is a "semi-join"

Likewise, `NOT IN` and `NOT EXISTS` will be *usually* treated the same. This is an "anti-semi-join". The exception is where you have a NULL in the NOT IN subquery. This causes the NOT IN to always be false

So, performance wise there is *no difference* but `EXISTS`/`NOT EXISTS` will always be *correct*

See "[NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: Oracle](http://explainextended.com/2009/09/17/not-in-vs-not-exists-vs-left-join-is-null-oracle/)"

> 
> Oracle‘s optimizer is able to see that NOT EXISTS, NOT IN and LEFT JOIN / IS NULL are semantically equivalent as long as the list values are declared as NOT NULL.
> 
> 
> It uses same execution plan for all three methods, and they yield same results in same time.
> 
> 
> In Oracle, it is safe to use any method of the three described above to select values from a table that are missing in another table.
> 
> 
> However, if the values are not guaranteed to be NOT NULL, LEFT JOIN / IS NULL or NOT EXISTS should be used rather than NOT IN, since the latter will produce different results depending on whether or not there are NULL values in the subquery resultset.
> 
> 
> 

And [IN vs. JOIN vs. EXISTS: Oracle](http://explainextended.com/2009/09/30/in-vs-join-vs-exists-oracle) which has a similar conclusion
Some Forums & Posts say thats "NOT IN & NOT EXIST uses same execution plan, and they yield same results in same time."

But According to Me & my Experience with lacs of rows & dozens of tables,
And the REASON is: 

When using “NOT IN”, the query performs nested full table scans, 
whereas for “NOT EXISTS”, query can use an index within the sub-query.

Similar reason is for 'IN' & 'EXIST'...

Thankx :D