Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Concepts by Burleson Consulting

SQL Subqueries and Aggregation

A subquery is a condition where an SQL query is “nested” or placed inside another SQL query.  The ISO 99 SQL standard allows for SQL queries to be embedded inside other SQL statements in several ways.  SQL queries can be placed inside the SELECT clause (scalar subqueries), inside the FROM clause (in-line views), and inside the WHERE clause (basic subqueries).

The ability to nest SQL statements inside each other provides tremendous power to SQL and allows SQL statements to perform extremely complex processing without using a procedural language such as PL/SQL.

In relational terminology, the main query is called the outer query, and the subquery is often referred to as the inner query.

There are several types of basic SQL subqueries, all defined by the types of SQL predicate in the WHERE clause, so let’s take a closer look at these types of subqueries:

Basic subqueries

When we see a SQL statement that specifies a subquery, we first need to carefully check the where clause and determine if the subquery is a noncorrelated subquery or a correlated subquery. A correlated subquery is a query whereby the key in the subquery is correlated (using the = operator) with a column that is selected in the outer query. On the other hand, a noncorrelated subquery is a query where the subquery executes independently of the outer query, passing a result set to the outer query at the end of its execution. Noncorrelated subqueries are commonly seen when using the IN, NOT IN, EXISTS, and NOT EXISTS SQL clauses.

In a basic subquery, an SQL statement is embedded inside the WHERE clause of the query.  In this example, we locate all authors who have not yet published a book.

select
   author_last_name
from
   author
where
   author_key not in
     (select author_key from book_author);

This type of query is called a non-correlated subquery because the subquery does not make any references to the outside query. To see how this works, copy this code from this screen and run it against your sample database.  You should get the following output: 

AUTHOR_LAST_NAME
----------------------------------------                                       
clark                                                                          
mee  

In this example, we select all authors who have not yet published their first book. Internally, this query reads all author_key values from the book_author table and then compares this result set with the author_key value in the author table. The savvy Oracle SQL tuner is always on the lookout for both correlated and noncorrelated subqueries for several reasons. The foremost is to search for opportunities for replacing the subquery with a standard join, and the other is to examine the uniqueness of the indexes in the subquery to see if changing the index structure can change the table access method.

Where column > (subquery)

The following subquery selects those employees whose salary is greater than the minimum salary for their jobs.

select
   emp_last_name
from
   emp
where
   emp_salary > (select job_min_sal from job
                     where
                     emp.job_key = job.job_key)
;
 

EMP_LAST_NAME                                                                  
------------------------------                                                 
king                                                                           
jackson                                                                        
korn                                                                           
linus                                                                           
tokheim                                                                        
johnson                                                                        
baker                                                                           
coleman                                                                        
brannigan                                                                      

9 rows selected.
 

This is known as a correlated subquery because the subquery references the outer query in the subqueries WHERE clause. Internally, correlated subqueries are very expensive to process because the inner query must be executed for every row returned by the outer query.

Where column = (subquery)

This type of subquery checks where a condition in the outer query is equal to a condition in the subquery.  In this example we are locating all book titles that belong to a publisher.

select
   book_title
from
   book
where
   pub_key = (select pub_key from publisher
                     where
                     publisher.pub_key = book.pub_key)

BOOK_TITLE                                                                     
-----------------------------------------------------------------
windows sucks                                                                  
piano greats                                                                   
DOS for dummies                                                                
The zen of auto repair                                                         
zero loss finance                                                              
operations research theory                                                     
non violins in the workplace                                                   
UNIX for experts                                                               
pay no taxes and go to jail                                                    
the fall of microsoft                                                           
writers market                                                                 
managing stress                                                                
bears are people too                                                            
reduce spending the republican way                                             
the willow weeps no more                                                       
Oracle 10g sql tuning                                                             
was george washington gay?                                                     
cooking light                                                                  
never eat boogers                                                               
how to housebreak your horse                                                     

20 rows selected.

Where column < (subquery)

In this example, we display the last names of all employees whose salary is less than the minimum salary for their job grade.  Note that this is a correlated subquery because the inner query references the outer query.

select
   emp_last_name
from
   emp
where
   emp_salary < (select job_min_sal from job
                     where
                     emp.job_key = job.job_key)
;
 

EMP_LAST_NAME                                                                  
------------------------------                                                 
levender

 

Where exists (subquery)

The WHERE EXISTS subquery is used when we want to display all rows where we have a matching column in both tables.  In most cases, this type of subquery can be re-written with a standard join to improve performance.

select
   book_key
from
   book
where
   exists (select book_key from sales)
;

BOOK_K                                                                         
------                                                                         
B101                                                                           
B102
                                                                         B103                                                                           
B104                                                                           

B105                                                                            
B106                                                                           

B107                                                                           
B108                                                                            
B109                                                                           
B110                                                                           
B111                                                                            
B112                                                                           
B113                                                                           
B114                                                                            
B115                                                                           
B116                                                                           
B117  
                                                              

B118
B119                                                                           
B120 
 

Where not exists (subquery)

The WHERE NOT EXISTS subquery is used to display cases where a selected column does not appear in another table.

select
   author_key
from
   author
where
   not exists (select author_key from book_author)
;



As a general rule, the use of the NOT EXISTS subqueries are discouraged because the query can often be re-written as a standard join with much faster performance.

Where column in (subquery)

select
   book_title
from
   book
where
   pub_key in (select pub_key from publisher
                     where

                    
publisher.pub_key = book.pub_key)

BOOK_TITLE                                                                     
-----------------------------------------------------------------
windows sucks                                                                  
piano greats                                                                   
DOS for dummies                                                                
The zen of auto repair                                                         
zero loss finance                                                               
operations research theory                                                     
non violins in the workplace                                                   
UNIX for experts                                                                
pay no taxes and go to jail                                                    
the fall of microsoft                                                          
writers market                                                                  
managing stress                                                                
bears are people too                                                           
reduce spending the republican way                                              
the willow weeps no more  
                                                     
Oracle 10g sql tuning                                                            
was george washington gay?                                                      
cooking light                                                                  
never eat boogers
how to housebreak your horse                                                   

20 rows selected.
 

Where column not in (subquery)

In this example, we select all books that do not have sales.  Note that this is a non-correlated subquery.

select
   book_key
from
   book
where
   book_key not in (select book_key from sales)
;


BOOK_K                                                                          
------                                                                         
B117                                                                           
B118  
                                                                          
B119                                                                           
B120 

There are serious problems with subqueries that may return NULL values. It is a good idea to discourage the use of the NOT IN clause (which invokes a subquery) and to prefer NOT EXISTS (which invokes a correlated subquery), since the query returns no rows if any rows returned by the subquery contain null values.


For more details, see the "Easy Oracle Series" a set of books especially designed by Oracle experts to get you started fast with Oracle database technology.


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

   
 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.