Advanced SQL Tutorial (ctd)

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 will look 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 syntacttical limit to the level of besting. However, very complex queries should be avoided as they are difficult to understand and to maintain and may not perform that well either.

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_id 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.

Sub Queries In The From Clause

The examples so far in this advanced SQL tutorial all had sub queries in the where clause, but sub queries can also be used in the from clause 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 a 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 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 to use 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 a copy of the policy table with no rows:
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.

Click on the link to continue this advanced SQL tutorial and learn about correlated sub-queries.

Return from advanced sql tutorial to Oracle tips and tricks

---------------------------------------

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 will help you make your Oracle systems faster and more reliable and save you hours searching for information. Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox.

For fast, high quality technical support, call 0845 0031320, fill in the form to have one of our consultants call you or go to our Oracle support page for more info.

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.