DATABASE & SQL/PLSQL

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.

Example:

SELECT  pub_name
FROM  publishers
WHERE  EXISTS (SELECT  *  FROM titles WHERE type='business')

output

Display the name of publishers who publish business related books.

SELECT title
FROM titles
WHERE advance > (SELECT  avg(advance)  FROM titlesWHERE type='business')

output

Display the titles of all those books for which the advance amount is greater than the average advance for bussiness related books.