Examples of statements using EXISTS and their equivalent alternatives follow. You've seen some of them before.
Here are two ways to find authors who live in the same city as a publisher:
A. SELECT au_lname, au_fname FROM authors WHERE city =ANY (SELECT city FROM publishers) B. SELECT au_lname, au_fname FROM authors WHERE exists (SELECT * FROM publishers WHERE authors.city = publishers.city) au_lname au_fname -------- -------- Carson Cheryl Bennet Abraham (2 row(s) affected)
The two queries find titles of books published by any publisher located in a city that begins with the letter B:
C. SELECT title FROM titles WHERE pub_id IN (SELECT pub_id FROM publishers WHERE city LIKE 'B%') D. SELECT title FROM titles WHERE EXISTS (SELECT * FROM publishers WHERE pub_id = titles.pub_id AND city LIKE 'B%') title ---------------------------------------------------- The Busy Executive's Database Guide Cooking with Computers: Surreptitious Balance Sheets You Can Combat Computer Stress! Straight Talk About Computers But Is It User Friendly? Secrets of Silicon Valley Net Etiquette Is Anger the Enemy? Life Without Fear Prolonged Data Deprivation: Four Case Studies Emotional Security: A New Algorithm (11 row(s) affected)