Advanced Oracle Tutorials - Go From Beginner To Expert With Real-World Examples

This series of Oracle tutorials will look at the use of sub-queries (non-correlated and correlated), views (join views, in-line views), joins (inner joins, outer joins, non-equi-joins and cross joins), and finally we'll look at the performance implications of various types of sql statements and how to ensure we don't inadvertently cause poor performance.

The first in our series of advanced Oracle tutorials focuses on the use of sub-queries in general and correlated sub-queries in particular.

Sub Queries

Also known as nested queries, these are used to answer multi-part questions and are often interchangeable with a join. In fact, when executed, a SQL statement containing a sub-query may well be treated by Oracle exactly as if it were a join.

As an example let's assume we have in our Oracle database one table called oracle_tutorial (which contains the names of all the tutorials we have and the authors id) and another table called author which links the author's names to his or her id.

These two tables would be defined as follows:

CREATE TABLE author
(author_id NUMBER PRIMARY KEY
,author_name VARCHAR2(30)
);

and

CREATE TABLE Oracle_tutorial
(tutorial_id   NUMBER PRIMARY KEY
,tutorial_name VARCHAR2(30)
,author_id     NUMBER REFERENCES author(author_id)
);

Taking the trivial example of finding the titles of all the Oracle tutorials written by an author called Andy McKay, this could be expressed as either:

SELECT tutorial_name FROM oracle_tutorial WHERE author_id =
(
    SELECT author_id FROM author 
    WHERE author_name = 'Andy McKay'
)

or

SELECT tutorial_name
FROM oracle_tutorial NATURAL JOIN author
WHERE author_name = 'Andy McKay'

There would be little difference in terms of performance in this simple query, but for more complex queries there could well be performance implications so it is always worth trying a few variations and obtaining the execution plans for complex queries before deciding on any particular version.

Non Correlated Sub-Queries

The most common use of sub queries is in the WHERE clause of queries to define the limiting condition (i.e. what value(s) a column(s) must have to be of interest). This was shown in the above example. However, they can also be used in other parts of SQL statements, specifically:

• in the FROM clause of a SELECT statement instead of a table name (known as an in-line view);
• to provide the new values for the specified columns in an UPDATE statement.
• in INSERT, UPDATE and DELETE statements instead of a table name;
• in the WHERE, HAVING and START WITH clauses of SELECT, UPDATE and DELETE statements to define the limiting set of rows
• to define the set of rows to be included by a view or a snapshot in a CREATE VIEW or CREATE SNAPSHOT statement or the set of rows to be created in the target table of a CREATE TABLE AS or INSERT INTO statement;

The example shown earlier used a simple equality expression. In that case we were interested in only one row from the sub query. We can also use the sub query to provide a set of rows, though. For example, to find the names of all Oracle tutorials written by either Andy McKay or Nick Sale, the following SQL statement could be used:

SELECT tutorial_name FROM oracle_tutorial WHERE author_id IN
(
    SELECT author_id FROM author
    WHERE author_name IN ('Andy McKay','Nick Sale')
)

In fact, the original example could also return more than one row from the sub-query if there were two or more authors with the name Andy McKay in our set of Oracle tutorials. In that case, the first example would cause a run-time SQL error, though, because by using '=' we specified that the sub query should produce no more than one row (it is perfectly legitimate for a sub query to return no rows).

The question can also be reversed to ask for the names of all the Oracle tutorials that were NOT written by Andy McKay. To do this, the sense of the sub query just has to be reversed by prefixing it with 'NOT' or '!'.

SELECT tutorial_name FROM oracle_tutorial WHERE author_id NOT IN
(
    SELECT author_id FROM author
    WHERE author_name = 'Andy McKay'
)

Or

SELECT tutorial_name FROM oracle_tutorial WHERE author_id !=
(
    SELECT author_id FROM author
    WHERE author_name = 'Andy McKay'
)

Looking for instructor-led Oracle training?