Lesson 5	Oracle EXISTS clause
Objective	Use EXISTS instead of IN for a Subquery

Use EXISTS instead of IN for Subquery

Correlated Subquery can produce more efficient code

The correlated subquery seen in the previous lesson may seem like a clumsy substitute for a JOIN query. In some cases this is true. In fact, Oracle's SQL parser resolves the correlated subquery into a JOIN query with no subquery whenever it can.
This lesson shows you how to use the correlated subquery in a way that really pays for itself in more efficient code. The EXISTS clause combined with a correlated subquery executes faster (less CPU time, less I/O time, and less overall response time) than an equivalent subquery using the IN clause in a normal subquery.

EXISTS clause syntax

Here is the general syntax of a query that uses the EXISTS clause:

SELECT col1, col2, ...FROM table alias1
WHERE EXISTS (SELECT 'x'
FROM table alias2
WHERE alias1.col5 = alias2.col6)

The syntax is very similar to the syntax for a regular correlated subquery. The differences are:

Rather than comparing a column with a result FROM the subquery, you simply use EXISTS in the outer query
Rather than returning a data value FROM the subquery, the subquery's SELECT clause always selects one literal: 'X'

Your query will run efficiently when you follow this syntax.

Using the EXISTS clause

The best way to use the EXISTS clause is to substitute it for an IN clause.
The link below converts a query with an IN clause to a query with an EXISTS clause.
Change in to exists

Interpret Correlated - Subqueries

Click the link below to try out your skills in interpreting correlated sub-queries.
Interpret Correlated - Subqueries

Using EXISTS and Its Correlated Subquery

EXISTS is a test for existence. It is placed the way IN might be placed with a subquery, but it differs in that it is a logical test for the return of rows from a query, not for the rows themselves. How many authors have written more than one book on the bookshelf?

select AuthorName, COUNT(*)
from BOOKSHELF_AUTHOR
group by AuthorName
having COUNT(*) > 1;

group by AuthorName

Attempting to find both AuthorName and Title fails, however, because the group by made necessary by the COUNT(*) is on the primary key of the BOOKSHELF_AUTHOR table (AuthorName, Title). Because each primary key, by definition, uniquely identifies only one row, the count of titles for that one row can never be greater than 1, so the having clause always tests false since it does not find any rows:

select AuthorName, Title, COUNT(*)
from BOOKSHELF_AUTHOR
group by AuthorName, Title
having COUNT(*) > 1;
no rows selected.

EXISTS provides a solution. The following subquery asks, for each AuthorName selected in the outer query, whether an AuthorName exists in the BOOKSHELF_AUTHOR table with a count of Titles greater than one. If the answer for a given name is yes, the EXISTS test is true, and the outer query selects an AuthorName and Title. The author names are correlated by the "BA" alias given to the first BOOKSHELF_AUTHOR table.

column AuthorName format a25
column Title format a30
select AuthorName, Title
  from BOOKSHELF_AUTHOR BA
  where EXISTS
  (select 'x'
    from BOOKSHELF_AUTHOR BA2
    where BA.AuthorName = BA2.AuthorName
    group by BA2.AuthorName
    having COUNT(BA2.Title) > 1)
 order by AuthorName, Title;

In the next lesson, you will learn the unusual case of embedding a subquery in the FROM clause of your query.