Subqueries with EXISTS Examples

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)