Oracle Consulting and Training
For Better, Faster, Smarter
Advanced SQL Tutorial (ctd)
This is the final part of our advanced SQL tutorial focusing on the design of more complex SQL statements
and sub queries in particular. Part1 introduced sub-queries and looked at non-correlated sub-queries. Part 2
looked at the uses of sub-queries. Part 3 looked at nested sub queries, sub-queries in the from clause and
sub-queries that return no rows.
The final part of our tutorial examines the use of correlated sub-queries. Performance issues are only
briefly mentioned as we have a whole series of articles on that topic (see Oracle Performance Tuning).
You might also be interested in our beginner's SQL tutorial, our PL/SQL tutorials and our Oracle tutorials.
Sub Queries (ctd)
As we've seen already, there are two types of sub queries:
correlated and non-correlated. We've already looked at
non-correlated sub queries in Advanced SQL Tutorial part
Just like non-correlated sub queries, correlated sub queries
are used to answer multi-part questions, but they are most
often used to check for existence or absence of matching
records in the parent table and the related table in the sub
A correlated sub query refers to a column from a table in the parent query. As mentioned in part 1 sub-
queries (both correlated and non-correlated) and joins are usually interchangeable.
However the SQL may be significantly faster when a correlated sub-query is used as correlated sub queries
refer to a column from their parent queries, they are executed once per row in the parent query whereas
non-correlated sub queries are executed once for the whole statement.
For example, using the emp and dept tables from before, to find out which departments have no employees
assigned to them, we can write the SQL statement in 3 different ways - as a non-correlated sub query, as an
outer join, or as a correlated sub-query.
Example 1 - non-correlated sub query
SELECT dept.name FROM dept WHERE dept.id NOT IN
SELECT dept_id FROM emp WHERE dept_id IS NOT NULL
Example 2- outer join
SELECT dept.name FROM dept,emp WHERE emp.dept_id (+) = dept.id
Example 3 - correlated sub query
SELECT dept.name FROM dept WHERE NOT EXISTS
SELECT dept_id FROM emp WHERE emp.dept_id = dept.id
The second example is an outer join SQL statement which may produce different results to the other 2
queries as it returns both matching rows and the non-matching rows on one side of the join. In this case the
query would return the names of departments which have no employees assigned to them plus the names of
those departments that do have employees assigned to them.
The first and the third SQL statements would produce exactly the same results, but the first would probably
be slower than the third if the dept_id column in the emp table were indexed (depending on the sizes of the
The first SQL statement can not use any indexes - the where clause of the sub query is just checking for
NOT NULL rows - so a full table scan would be performed. Also the sub query would be executed once for
each row in the dept table.
On the other hand, the sub query in the third example can use the index and since only the dept_id is
returned by the sub query, there is no need for any subsequent table access. For these reasons, the third
query would normally perform better than the first.
As you can see there are nearly always several ways in which the SQL for a query may be written, and it is
therefore best to try alternative SQL statements particularly for complex queries before deciding on the
This advanced SQL tutorial only touched on performance tuning which is a whole subject in itself. For
more help on Oracle performance tuning and other aspects of Oracle see the oracle tips and tricks section
and see our Oracle resources section for book reviews and links to other excellent Oracle resources.
Return to part 1 - introduction to sub queries
Return to advanced sql tutorial part 3-subqueries(ctd)
Looking to sky-rocket productivity, save time and reduce costs?
Smartsoft offers instructor-led training in Oracle and related technologies on or off site in cities across the
UK as well as self-study online training. See our scheduled Oracle training courses, or let us know your
Looking for more Oracle tips and tricks? For no frills, no fluff, just solid, reliable technical information,
take a short cut now and subscribe to our newsletter. Jam-packed full of tips and tricks, it will help you
make your Oracle systems faster and more reliable and save you hours searching for information.
Smartsoft Computing Ltd, Bristol, England
This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry
trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their
© Copyright Smartsoft Computing Ltd. All rights reserved.