Explain and contrast the results provided by the EXISTS, NOT EXISTS, and doubly-nested NOT EXISTS keywords

What will be an ideal response?

The EXISTS and NOT EXISTS keywords are used in the WHERE clause of correlated subqueries, and thus are the basis of just another form of correlated subquery. The EXISTS keyword will be true if any row in the subquery meets the subquery condition(s). The NOT EXISTS keyword will be true when all rows in the subquery fail to meet the subquery condition(s). The double use of NOT EXISTS, which requires three nested SELECT statements, can be used to test for situations where every row of a table meets a specified condition tested. The doubly nested NOT EXISTS pattern is a famous pattern in SQL and very useful when needed.