Oracle Consulting and Training
For Better, Faster, Smarter
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)
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.
,ROUND(fs.mbytes,2) “Free (MBytes)”
FROM dba_tablespaces ts
GROUP BY tablespace_name
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
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.