Exists and Not Exists

The WHERE EXISTS subquery is used when we want to display all rows where we have a matching column in both tables.  In most cases, this type of subquery can be re-written with a standard join to improve performance.

select
   book_key
from
   book
where 
   exists (select book_key from sales)
;


Where not exists (subquery)

The WHERE NOT EXISTS subquery is used to display cases where a selected column does not appear in another table.
select
   author_key
from
   author
where 
   not exists (select author_key from book_author)
;

As a general rule, the use of the NOT EXISTS subqueries are discouraged because the query can often be re-written as a standard join with much faster performance.

Refer: http://www.dbanotes.com/database-development/using-exists-in-oracle-sql-queries/

http://www.dba-oracle.com/sql/t_subquery_not_in_exists.htm