A sub query used with the EXISTS clause, always returns data in terms of a TRUE or FALSE value. It checks for the existence of a data rows according to the condition specified in the inner query and passes the existence status to the outer query to produce the result set.
SELECT pub_name FROM publishers WHERE EXISTS (SELECT * FROM titles WHERE type='business')
Display the name of publishers who publish business related books.
SELECT title FROM titles WHERE advance > (SELECT avg(advance) FROM titlesWHERE type='business')
Display the titles of all those books for which the advance amount is greater than the average advance for bussiness related books.