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)

Correlated Sub-Queries

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 1. 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. 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 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. 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 requirements. 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  Contact Us View our privacy policy 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 linked to individuals. View woopra privacy policy.  Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. © Copyright Smartsoft Computing Ltd. All rights reserved.