Existence Check Subqueries

A subquery can also be used to simply check whether a SELECT statement retrieves any row at all. The format of the existence check subquery is as follows:

SELECT ... FROM ... WHERE [NOT] EXISTS (SELECT  *  FROM ...)

The existence check subquery does not retrieve any result set; it just returns true if the subquery retrieves at least one row, and false otherwise.

The following correlated existence check subquery returns a list of authors who were born in the same year that at least one of the books in the shop was published.

select full_name, yr_born from author 
 	where exists (select * from book where author.yr_born = book.publ_year);
FULL_NAME                            YR_BORN
Johnson, Samuel                         1709
Shelley, Mary Wollstonecraft            1797
Dumas, Alexandre                        1802
Hugo, Victor                            1802
Longfellow, Henry Wadsworth             1807
Gaskell, Elizabeth Cleghorn             1810
Stowe, Harriet Beecher                  1811
	...
Burroughs, Edgar Rice                   1875
London, Jack                            1876
Sinclair, Upton                         1878
Woolf, Virginia                         1882
Lewis, Sinclair                         1885
Ferber, Edna                            1887
Faulkner, William                       1897
Hemingway, Ernest                       1899
Steinbeck, John                         1902