Free Trial

Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.

  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

6. Advanced Retrieval Operations > The EXISTS Operator

The EXISTS Operator
The EXISTS operator check the number of rows returned by a subquery. If the subquery contains one or more rows, then the result is true and a row is placed in the result table; otherwise, the result is false and no row is added to the result table.
For example, suppose the online bookstore wants to see the titles of books that have been sold. To write the query using EXISTS, you would use
SELECT title
FROM book t1, work
WHERE t1.work_numb = work.work_numb
AND EXISTS (SELECT *
FROM volume
WHERE t1.isbn = volume.isbn
AND selling_price > 0);
The preceding is a correlated subquery. Rather than completing the entire subquery and then turning to the outer query, the DBMS processes the query in the following manner:
1. Look at a row in book.
2. Use the ISBN from that row in the subquery's WHERE clause.
3. If the subquery finds at least one row in volume with the same ISBN, place a row in the intermediate result table. Otherwise, do nothing.
4. Repeat steps 1 through 3 for all rows in the book table.
5. Join the intermediate result table to work.
6. Project the title column.

  

You are currently reading a PREVIEW of this book.

                                                                                                                    

Get instant access to over $1 million worth of books and videos.

  

Start a Free 10-Day Trial


  
  • Safari Books Online
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint