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)."
