Skip to Main Content
  • Questions
  • Multiple Outer Joins in One Statement


Question and Answer

Tom Kyte

Thanks for the question, Shelli.

Asked: March 31, 2006 - 4:13 pm UTC

Last updated: April 01, 2006 - 10:20 am UTC

Version: 10.1

Viewed 1000+ times

You Asked

Hi Tom,

I'm trying to create a very complex SQL statement that uses multiple outer joins. I'm not sure that what I'm trying to do in one statement can be done, but here it goes...

We are using a property_key and property table to extend other tables in the database without making schema changes (we kept having to add columns to existing tables and are using this to avoid that in the future).

property_key property
---------------- ---------
property_key_id property_id
key property_key_id
entity_name entity_id
default_value value

The property_key.entity_name is the name of a table which we want to expand with a property. The property_key.key is a text identifier that can be used across all instances of this db schema (e.g. test, stage, prod) without having to worry about keeping property_key.property_key_id consistent.

The property.entity_id is the primary key of the record we are adding a property to with property.value.

Our service table has one property_key associated with it:

SELECT * FROM property_key WHERE entity_name = 'service'

property_key_id key entity_name default_value
--------------- -------------- ----------- -------------
1 is_provisioned service N

I'm trying to write a sql statement that will return me all the attributes from the service plus its property and the default_value from the property_key. I have successfully written a statement that will return all the services and their properties (if existing) using an outer join:

SELECT s.*, p1.*
FROM service s, property p1, property_key pk1
WHERE s.event_id = p1.entity_id(+)
AND pk1.key = 'is_provisioned'

However, when I try to add the default value for the property_key, things go wrong. I tried this statement:

SELECT s.*, p1.*, pk1.default_value
FROM service_event s, property p1, property_key pk1
WHERE s.event_id = p1.entity_id(+)
AND p1.property_key_id = pk1.property_key_id
AND pk1.key = 'carrier_provisioned'
ORDER BY s.event_id

Which only returns those records where's there's a join between service and property (those without a property and therefore using the default value are not returned.

I tried to add another outer join to the statement:

SELECT s.*, p1.*, pk1.default_value
FROM service_event s, property p1, property_key pk1
WHERE s.event_id = p1.entity_id(+)
AND p1.property_key_id(+) = pk1.property_key_id
AND pk1.key = 'carrier_provisioned'
ORDER BY s.event_id

and get back the error ORA-01417: a table may be outer joined to at most one other table.

Is there anyway that I can get all the info I want in one statement?


and Tom said...

I would not recommend doing this - not at all. There is NOTHING WRONG with actually having to have a data model.

There is something wrong with this sort of generic code.

I read the first paragraph and just frankly "stopped" (well, read the last one, you could use ansi outer join syntax, but DON'T - just say NO)

You will end up hating this model, it won't perform, it won't scale, it'll be like pulling teeth getting your data back out.

Just a short while ago, I was consulting with a customer - bad, serious bad, performance from their database. Step one - describe the model to me. They say "well, there are these four tables..." I know what is coming next and pull up chapter one from Effective Oracle by Design on the screen, namely this section:

Don’t Use Generic Data Models

Frequently, I see applications built on a generic data model for “maximum flexibility” or applications built in ways that prohibit performance. Many times, these are one and the same thing! For example, it is well known you can represent any object in a database using just four tables:
Create table objects ( oid int primary key, name varchar2(255) );
Create table attributes
( attrId int primary key, attrName varchar2(255),
datatype varchar2(25) );
Create table object_Attributes
( oid int, attrId int, value varchar2(4000),
primary key(oid,attrId) );
Create table Links ( oid1 int, oid2 int,
primary key (oid1, oid2) );

IT WAS AT THIS POINT THEY SAID - hey, you already know our model, this is great - even the table names are the same!

I then pointed out the TITLE of the section...

That’s it. No more CREATE TABLE for me! I can fill the ATTRIBUTES table with rows like this:
insert into attributes values ( 1, 'DATE_OF_BIRTH', 'DATE' );
insert into attributes values ( 2, 'FIRST_NAME', 'STRING' );
insert into attributes values ( 3, 'LAST_NAME', 'STRING' );
And now I’m ready to create a PERSON record:
insert into objects values ( 1, 'PERSON' );
insert into object_Attributes values( 1, 1, '15-mar-1965' );
insert into object_Attributes values( 1, 2, 'Thomas' );
insert into object_Attributes values( 1, 3, 'Kyte' );
insert into objects values ( 2, 'PERSON' );
insert into object_Attributes values( 2, 1, '21-oct-1968' );
insert into object_Attributes values( 2, 2, 'John' );
insert into object_Attributes values( 2, 3, 'Smith' );
And since I’m good at SQL, I can even query this record to get the FIRST_NAME and LAST_NAME of all PERSON records:
ops$tkyte@ORA920> select
max( decode(attrName, 'FIRST_NAME', value, null )) first_name,
2 max( decode( attrName, 'LAST_NAME', value, null ) ) last_name
3 from objects, object_attributes, attributes
4 where attributes.attrName in ( 'FIRST_NAME', 'LAST_NAME' )
5 and object_attributes.attrId = attributes.attrId
6 and object_attributes.oid = objects.oid
7 and = 'PERSON'
8 group by objects.oid
9 /

-------------------- --------------------
Thomas Kyte
John Smith
Looks great, right? I don’t need to create tables anymore, because I can add columns at the drop of a hat (with an insert into the ATTRIBUTES table). The developers can do whatever they want, and the DBA can’t stop them. This is ultimate flexibility. I’ve seen people try to build entire systems based on this model.
But how does this model perform? Miserably, terribly, and horribly. A simple select first_name, last_name from person query is transformed into a three-table join with aggregates and all. Furthermore, if the attributes are NULLABLE—that is, there might not be a row in OBJECT_ATTRIBUTES for some attributes—you may need to use an outer join instead of just joining, which might remove more optimal query plans from consideration.
Writing queries with this model might look straightforward. For example, if I wanted to get everyone who was born in March or has the last name of Smith, I could simply take the query to get the FIRST_NAME and LAST_NAME of all PERSON records and wrap an inline view around it:
ops$tkyte@ORA920> select *
2 from (
3 select
max(decode(attrName, 'FIRST_NAME', value, null)) first_name,
4 max(decode(attrName, 'LAST_NAME', value, null)) last_name,
5 max(decode(attrName, 'DATE_OF_BIRTH', value, null))
6 from objects, object_attributes, attributes
7 where attributes.attrName in ( 'FIRST_NAME',
8 and object_attributes.attrId = attributes.attrId
9 and object_attributes.oid = objects.oid
10 and = 'PERSON'
11 group by objects.oid
12 )
13 where last_name = 'Smith'
14 or date_of_birth like '%-mar-%'
15 /

-------------------- -------------------- --------------------
Thomas Kyte 15-mar-1965
John Smith 21-oct-1968
So, it looks easy to query, but think about the performance! If you had a couple thousand OBJECT records and a couple tens of thousands of OBJECT_ATTRIBUTES, Oracle would need to process the entire inner group by query first and then apply the WHERE clause.
This is not a made-up data model, one that I crafted just to make a point. This is an actual data model that I’ve seen people try to use. Their goal is ultimate flexibility. They don’t know what OBJECTS they need, and they don’t know what ATTRIBUTES they will have. Well, that is what the database was written for in the first place: Oracle implemented this thing called SQL to define OBJECTS and ATTRIBUTES and lets you use SQL to query them.

Is this answer out of date? If it is, please let us know via a Comment