EXISTS & IN in SQL

• When a subquery returns a NULL value then the whole statement become NULL. In that cases we are using the EXITS keyword. If we want to compare particular values in subqueries then we are using the IN keyword.

    Ex:-

   select *
   from emp
   where empno not in(select mgr from emp);   /*it through no rows selected because the inner query
                                                                          return NULL value*/

• If you are using the IN operator, the SQL engine will scan all records fetched from the inner query. On the other hand if we are using EXISTS, the SQL engine will stop the scanning process as soon as it found a match.

•   When your inner query fetching thousand of rows then EXIST would be better choice.When your inner query fetching few rows, then IN will be faster
• EXIST evaluate on true or false but IN compare multiple value. When you don't know the record is exist or not, your should choose EXIST.