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
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(*)
group by AuthorName
having COUNT(*) > 1;
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(*)
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
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.