Inline Views And Outer Joins
February 24th, 2004 by Mark Rittman
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).”
February 24th, 2004 at 4:26 pm
Hi,
I think a simple solution would have consisted to put also a (+) in the address_type_id condition such as:
and ADDRESS.ADDRESS_TYPE_ID(+) = 3
February 24th, 2004 at 4:45 pm
Yves,
You might be right. I’ll try it out when I get in the office tomorrow. Wouldn’t suprise me if the simpler solution worked.
Mark
February 24th, 2004 at 5:09 pm
Ah, thats nicer syntax than the kludge I’ve been using:
AND (ADDRESS.ADDRESS_TYPE = 3 OR ADDRESS.ADDRESS_TYPE IS NULL)
Works as expected, too!
February 24th, 2004 at 5:12 pm
oooh no. I’m on the front page of OTN now ….
I’m going to get every SQL expert pulling this one apart now, and it was only a five minute bit of work. Here we go…
Mark
February 24th, 2004 at 5:42 pm
Of course, as handy as (+) is for outer join syntax, its a really poor way to phrase queries in recent versions of Oracle (9i and later) as the optimizer will just drop the (+) if you miss one, and revert to a standard equijoin - as you pointed out.
The better option would be to use the LEFT OUTER JOIN syntax just introduced in 9i and bypass the (+) entirely. But I’ve had some trouble with this in 9i when using views, as schema resolution appears to be done differently with LEFT OUER JOIN than with (+). So there’s no true solution.
March 2nd, 2004 at 2:50 am
Hi. Just wanted to thank whoever it was that wrote this post for the information. We (3 of us) have spend days researching into writing what we call subqueries in outer joins for oracle, and haven’t found a thing. This was fantastic and solved our problem in moments.
Thanks again,
Danson