Advanced SQL Tutorial (part 3)

This is part 3 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. This part looks at nested sub queries, sub-queries in the from clause and sub- queries that return no rows and part 4 covers correlated sub-queries. Performance issues are not thoroughly explored even though this is an advanced sql tutorial as that subject is covered by another series of articles - Oracle Performance Tuning. Sub Queries (ctd)

Nested Sub-Queries

The SQL syntax allows queries to be nested, meaning that a sub query itself can contain a sub query, enabling very complex queries to be built as there is no syntactical limit to the level of nesting. For example, the SQL statement to find the departments that have employees with a salary higher than the average employee salary could be written as: SELECT name FROM dept WHERE id IN (SELECT dept_no FROM emp WHERE sal > (SELECT avg(sal) FROM emp)) Any of the other comparison operators instead of '=' or 'IN' such as '>', or '<' can also be used with a sub query. However, very complex queries should be avoided as they are difficult to understand and to maintain and may not perform that well.

Sub Queries In The From Clause

The examples so far in this advanced SQL tutorial have all had sub queries in the WHERE clause, but sub queries can also be used in the FROM clause in a SELECT statement instead of a table name. In these circumstances the sub query acts as if it had been predefined as a view. For example, the following SQL statement returns the amount of used space, the free space and the total allocated space for all tablespaces in an Oracle database. SELECT ts.tablespace_name               ,ROUND(fs.mbytes,2) “Free (MBytes)” FROM dba_tablespaces ts           ,(              SELECT tablespace_name                            ,SUM(bytes)/1024/1024 mbytes               FROM dba_free_space               GROUP BY tablespace_name            ) fs WHERE ts.tablespace_name = fs.tablespace_name; Note that the sub query is given an alias “fs” so that results can be used in the main body of the query.

Sub Queries That Return No Rows

Up until now the queries shown have all been expected to produce a result, but when creating tables, it can be very useful to write the SQL with a sub query which will not return any rows - when just the table structure is required and not any of the data. In the following example we create an empty copy of the policy table: CREATE TABLE new_policy AS (SELECT * from policy WHERE 1=0); The sub query returns no data but does return the column names and data types to the CREATE TABLE statement, so the table is created with the same columns and data types as the source table but with no data. Return to advanced sql tutorial part 2-subqueries(ctd)   Continue to part 4 - correlated sub queries  --------------------------------------- Looking to sky-rocket productivity, save time and reduce costs? Training is a highly cost-effective and proven method of boosting productivity. 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  Need help with your Oracle databases? 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.