This is the final part of our advanced SQL tutorial focusing on the design of more complex SQL statements and sub queries in particular. Part 1 introduced sub-queries and looked at non-correlated sub-queries. Part 2 covered the the uses of sub-queries and part 3 covered
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.
As we've seen already, there are two types of sub query: correlated and non-correlated. We've already looked at non-correlated sub queries (see advanced SQL tutorial part 1). All of the examples of sub queries up until now have been non-correlated sub queries.
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 query.
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.
SELECT dept.name FROM dept
WHERE dept.id NOT IN
(
SELECT
dept_id
FROM emp
WHERE dept_id IS NOT NULL
)
SELECT dept.name
FROM dept,emp
WHERE emp.dept_id (+) = dept.id
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 differnt reults 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 tables).
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 preferred one.
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.
---------------------------------------
For Oracle training with experienced consultants on site or off-site at training centres throughout the UK contact us with your requirements or click on the link for the schedules, prices and course outlines for our Oracle SQL and PL/SQL training courses
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 ezine. Jam-packed full of tips and tricks, it should save you hours searching for information and help you to keep your Oracle systems running smoothly and efficiently. Subscribe today and your first issue will soon be winging its way to your mailbox.
Return from advanced sql tutorial to Oracle tips and tricks
Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320
Contact Us
Click here to view our privacy policy .
This site uses woopra.com to gather statistical information about our visitors. View woopra privacy policy .
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.
© Copyright Smartsoft Computing Ltd 2001-2009. All rights reserved.