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: 
Check [IN (vs) EXISTS and NOT IN (vs) NOT EXISTS](http://asktom.oracle.com/pls/asktom/f?p=100:11:0%3a%3a%3a%3aP11_QUESTION_ID:442029737684).
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