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 collegue 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.
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. I had a talk through with Jon Mead and we came up with the following solution.
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.
UPDATE: Yves Bergeron mentions another solution that adds another left outer join and avoids the need for the inline view. I'll try it out tomorrow and report back.
FURTHER UPDATE: This article has been linked to on OTN and I've had a few useful emails with additional advice. This one comes from Natti Bar-On from Oracle...
Hello Mark,
This is an email I've sent to a customer of mine a few weeks ago.
Attaching it as a whole and hope it might help. Nice seeing you on
OTN...
:)
BTW, there were problems with outer joins and inline-view-optimization
in 8i (mostly VPD related), but 9i more or less solved those.
Regards,
Natti Bar-On,
Sr. Technology Specialist,
Oracle Israel.
--------------------------------------------------------------
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 9i 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)."