Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Using outer joins with in-line views

Oracle Tips by Burleson Consulting
Don Burleson


By Mark Rittman

Inline Views And Outer Joins

I had an interesting question put to me at work yesterday, where a colleague was having problems with an outer join in an SQL statement.

My colleague was putting together as system that recorded bids for contracts, storing the userid of the system operator, the details of the prospective client, and details of all the company's sites, including their billing address. A query was being put together that returned the company address, the details of the bid, and the billing address, leaving the address bit blank if one hadn't yet been supplied.

The query used four tables, BID, LOCATION, USERS and ADDRESS, with BID joined to LOCATION by LOCATION_ID and to USER by USER_ID, and LOCATION joined to ADDRESS by LOCATION_ID.

The SQL looked like this;

select    BID.COMPANY_NAME,
          BID.LOCATION_NAME,
          TO_CHAR(BID.START_DATE, 'dd mon yyyy') || ' to ' || TO_CHAR(BID.END_DATE, 'dd mon yyyy') as BID_PERIOD,
          LOCATION.BRN,
          USERS.TITLE || ' ' || USERS.FORENAME || ' ' || USERS.SURNAME as USER_NAME,
          ADDRESS.CONTACT_TITLE,
          ADDRESS.CONTACT_FORENAME,
          ADDRESS.CONTACT_SURNAME,
          ADDRESS.POSITION,
          ADDRESS.ADDRESS_LINE_1,
          ADDRESS.ADDRESS_LINE_2,
          ADDRESS.TOWN,
          ADDRESS.COUNTY,
          ADDRESS.POSTCODE,
          ADDRESS.EMAIL,
          ADDRESS.TELEPHONE,
          ADDRESS.FAX
from      ADDRESS, LOCATION, BID, USERS
where     BID.LOCATION = LOCATION.LOCATION_ID
and       BID.USER_ID = USERS.USER_ID 
and       LOCATION.LOCATION_ID = ADDRESS.LOCATION_ID 
and       BID.BID_ID = 'Q0011DJR'
and       ADDRESS.ADDRESS_TYPE_ID = 3;

As a particular location could have more than one ADDRESS_TYPE_ID (billing address, postal address and so on) the ADDRESS_TYPE_ID field was specified to make sure only the billing address was returned. The output of the query was then a single row, with details of the bid, location, user and the billing address. The problem came when there was no ADDRESS_TYPE_ID = 3 (i.e. billing address) in the ADDRESS table for that particular location, which would lead to no rows being returned by the query. What was really required was that, in this situation, the query could be tweaked such that the location, bid and user details would still be returned, with the address details being left blank.

Using outer joins with in-line views

To achieve this, a left outer join was tried on the LOCATION.LOCATION_ID = ADDRESS.LOCATION_ID predicate, in the hope that this would lead to the location, bid and user details being returned even when no address table rows were retrieved. The resulting SQL statement looked like this (changes highlighted in red);

select    BID.COMPANY_NAME,
          BID.LOCATION_NAME,
          TO_CHAR(BID.START_DATE, 'dd mon yyyy') || ' to ' || TO_CHAR(BID.END_DATE, 'dd mon yyyy') as BID_PERIOD,
          LOCATION.BRN,
          USERS.TITLE || ' ' || USERS.FORENAME || ' ' || USERS.SURNAME as USER_NAME,
          ADDRESS.CONTACT_TITLE,
          ADDRESS.CONTACT_FORENAME,
          ADDRESS.CONTACT_SURNAME,
          ADDRESS.POSITION,
          ADDRESS.ADDRESS_LINE_1,
          ADDRESS.ADDRESS_LINE_2,
          ADDRESS.TOWN,
          ADDRESS.COUNTY,
          ADDRESS.POSTCODE,
          ADDRESS.EMAIL,
          ADDRESS.TELEPHONE,
          ADDRESS.FAX
from      ADDRESS, LOCATION, BID, USERS
where     BID.LOCATION = LOCATION.LOCATION_ID
and       BID.USER_ID = USERS.USER_ID 
and       LOCATION.LOCATION_ID = ADDRESS.LOCATION_ID (+)
and       BID.BID_ID = 'Q0011DJR'
and       ADDRESS.ADDRESS_TYPE_ID = 3;

However, the query still resulted in no rows being returned.

After having looked at the query, it became clear that the left outer join was in this instance a 'red herring' - even though all of the rows from the LOCATION table were being returned in the query, the query itself was still returning no rows, because of the ADDRESS.ADDRESS_TYPE_ID = 3 predicate later in the query.

Using outer joins with in-line views

What was required was a way of separating out the ADDRESS part of the query into an inline view, retrieving only those rows with an ADDRESS_TYPE_ID = 3, and then left outer joining this inline view to the rest of the query. An inline view is a subquery in the FROM clause of an outer query, which is then aliased and dealt with as if it was another table or view. Inline views are evaluated at runtime, and unlike normal views are not stored in the data dictionary; they're effectively named subqueries that derive their rows at run-time during the execution of the outer query.

Using the inline view, the SELECT statement that we came up with was as follows;

select   BID.COMPANY_NAME,
         BID.LOCATION_NAME,
         TO_CHAR(BID.START_DATE, 'dd mon yyyy') || ' to ' || TO_CHAR(BID.END_DATE, 'dd mon yyyy') as BID_PERIOD,
         LOCATION.CRN,
         USERS.TITLE || ' ' || USERS.FORENAME || ' ' || USERS.SURNAME as USER_NAME,
         ADDRESS.CONTACT_TITLE,
         ADDRESS.CONTACT_FORENAME,
         ADDRESS.CONTACT_SURNAME,
         ADDRESS.POSITION,
         ADDRESS.ADDRESS_LINE_1,
         ADDRESS.ADDRESS_LINE_2,
         ADDRESS.TOWN,
         ADDRESS.COUNTY,
         ADDRESS.POSTCODE,
         ADDRESS.EMAIL,
         ADDRESS.TELEPHONE,
         ADDRESS.FAX,
from 
        (
         SELECT ADDR.CONTACT_TITLE,
         ADDR.CONTACT_FORENAME,
         ADDR.CONTACT_SURNAME,
         ADDR.POSITION,
         ADDR.ADDRESS_LINE_1,
         ADDR.ADDRESS_LINE_2,
         ADDR.TOWN,
         ADDR.COUNTY,
         ADDR.POSTCODE,
         ADDR.EMAIL,
         ADDR.TELEPHONE,
         ADDR.FAX,
         ADDR.LOCATION_ID,
         ADDR.ADDRESS_TYPE_ID
         FROM ADDRESS ADDR
         where ADDR.ADDRESS_TYPE_ID = 3
         ) ADDRESS, LOCATION, BID, USERS
where    BID.LOCATION = LOCATION.LOCATION_ID
and      BID.USER_ID = USERS.USER_ID 
and      LOCATION.LOCATION_ID = ADDRESS.LOCATION_ID (+)
and      BID.BID_ID = 'Q0011DJR'

The inline view is in red. What's happening here is that, instead of referencing the ADDRESS table in the FROM clause as normal, and then trying to limit down the resultset later on with the ADDRESS.ADDRESS_TYPE_ID = 3, the inline view retrieves all addresses of address_type_id = 3, which is then left outer joined to the LOCATION table later on. Therefore, if there isn't an address in the inline view that has the correct LOCATION_ID, the left out join comes into play and returns all LOCATIONs as required. The ADDRESS.ADDRESS_TYPE_ID = 3 predicate can then be removed from the end of the query, and then, if an ADDRESS_TYPE_ID of 3 is not present for a bid, the columns from the LOCATION, BID and USER tables are still returned in the output row.

Having thought this through, I thought it was a good example of an inline view in action, as as doing a google search on inline views didn't come up with much useful information, I thought it worth jotting this one down. Let me know if there's a better way to achieve this result.

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

Outer joins have two ways of being written in Oracle.
let's assume we have two tables:

A
a1 num  a2 num
1               1
2               2
3               3

B
b1 num  b2 num
1               1
2               2
4               4

B_TAG
b1 num  b2 varchar2
1               hello
2               goodbye

we can write an outer join like this

--FORM 1
select a1,a2,b1,b2
from a,b
where a1 = b1 (+)

        A1         A2         B1         B2
---------- ---------- ---------- ----------
         1          1          1          1
         2          2          2          2
         3          3

or we can write it like this

--FORM 2
select a1,a2,b1,b2
from a left outer join b on a1=b1

        A1         A2         B1         B2
---------- ---------- ---------- ----------
         1          1          1          1
         2          2          2          2
         3          3

the latter was introduced in and has better functionality.

How do we perform filtering on rows?
There are two kinds of filters -
        A) we want to reduce the number of rows before the outer join
                this won't ruin the outer join
        B) we want to reduce the number of rows after the outer join -
                this will probably ruin the outer join...
                this is the problem you encounter...

What does the syntax looks like when we want to filter without losing the outer join?

form 1: Syntax for operators :

        = Equals
                select a1,a2,b1,b2
                from a,b
                where a1 = b1 (+)
                and b2 (+)= 3;

        A1         A2         B1         B2
---------- ---------- ---------- ----------
         1          1
         2          2
         3          3


       > < greater than / less than
                
                select a1,a2,b1,b2
            from a,b
            where a1 = b1 (+)
            and b2 (+)> 1

        A1         A2         B1         B2
---------- ---------- ---------- ----------
         1          1
         2          2          2          2
         3          3

        between

                select a1,a2,b1,b2
                from a,b
                where a1 = b1 (+)
                and b2 (+) between 1 and 5
      
        A1         A2         B1         B2
---------- ---------- ---------- ----------
         1          1          1          1
         2          2          2          2
         3          3


        is null / is not null
              
                select a1,a2,b1,b2
                from a,b
                where a1 = b1 (+)
                and b2 (+) is not null
      
        A1         A2         B1         B2
---------- ---------- ---------- ----------
         1          1          1          1
         2          2          2          2
         3          3

        like
              
                select a1,a2,b1,b2
                from a,b_tag
                where a1 = b1 (+)
                and b2 (+) like 'hel%'
      
        A1         A2         B1        B2
---------- ---------- ---------- ----------
         1          1          1 hello
         2          2
         3          3          

        in
                no way of represnting IN in form 1 outer joins.

FORM 2

        Equals
                
                select a1,a2,b1,b2
                from a left outer join b on a1=b1
                                and b2 = 2
                -- No where clause! the condition is an attribute of the outer join!

        < >
                select a1,a2,b1,b2
                from a left outer join b on a1=b1
                                and b2 > 2
        between
                select a1,a2,b1,b2
                from a left outer join b on a1=b1
                                and b2 between 0 and 2

        like
                select a1,a2,b1,b2
                from a left outer join b_tag on a1=b1
                                and b2 like 'hell%'
        is null
                select a1,a2,b1,b2
                from a left outer join b_tag on a1=b1
                                and b2 is null        

        in
                select a1,a2,b1,b2
                from a left outer join b on a1=b1
                                and b2 in (0,1)        
        
        A1         A2         B1         B2
---------- ---------- ---------- ----------
         1          1          1          1
         2          2
         3          3


So far for the outer join. Both forms are ANSI STANDARD FOR SQL, and Oracle supports both (not all vendors support both)."


 



 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational