Performance issue with IN clause

Question

I have question for using IN clause in sql query which of the following provide better performance

SELECT * FROM emp WHERE deptno IN (10,20)

OR

WITH dep AS (SELECT 10 deptno FROM DUAL UNION ALL
         SELECT 20 deptno FROM DUAL)
SELECT * FROM EMP e
WHERE EXISTS (SELECT 1 FROM dep WHERE dep.deptno=e.deptno);

I am looking which will provide better performance

Answer 1

"In clause" will be better choice because in another example optimizer can't figure out how to join this two tables so it scans all of the emp table and see if particularly record meets your condition. I've checked this on huge table (more than million rows) and the query plan was very different. Of course I assumed that you have index on deptno column. Without it both solutions require full table scan on emp table.