LEFT OUTER JOIN how to use?

LEFT OUTER JOIN how to use?

Post by Kolbj?rn Aam » Tue, 12 Sep 1995 04:00:00

(Use mono-spaced font for best result)

Q0: What DBMS other than Watcom SQL 4.0 support LEFT OUTER JOIN?

Implementing table addition/inheritance in SQL using LEFT OUTER JOIN.

We have a Table A and want to add data in two different and
complementary ways. We want to express as follows: (a, b and c's are
tupples/instances/objects and A, B, and C are Relations/ Tables/
Classes). Where there is a b there always is one and only one a for
that b. Where there is a c there always is one and only one a for
that c.

  (A) <------------------ (B)
   +--------------------- (C)

meaning (in a more Object Oriented Jargon): A is the SUPER-class, and
B and C are SUB-classes for A. Some also say that B and C INHERIT
some of their properties from A. A, B and C need some attributes to
be proper behaving classes but we don't draw theme here. It suffice
to say that  A have a Primary/Preferred key, and a Secondary
key to make the a's more available to humans. System Assigned
Surrogate key as known from for instance Codd's RT/M paper is used as
primary keys. b's and c's use the primary key of a's to identify
which a's they are  additional information for. The following SQL
statements define our data base:

In Watcom SQL:

Create Table A (
     KEY_1                                INTEGER NOT NULL,
     Key_2_1                             CHAR (32) NOT NULL,
     Key_2_2                              INTEGER NOT NULL,
     Comment                              CHAR (32000)  );

Create Table B(
     KEY_1                                INTEGER NOT NULL,
     Title                             CHAR (255) NOT NULL);

Create Table C (
     KEY_1                                INTEGER NOT NULL,
     day                                  DATE  );

// *** Index Division for the Data Model

Create Unique Index NAV_I2_A on A (Key_2_1,Key_2_2,);

// ***  Uniqueness Constraints

Alter Table A Add Primary Key (KEY_1);

Alter Table A Add Unique (Key_2_1,Key_2_2);

Alter Table B Add Primary Key (KEY_1);

Alter Table C  Add Primary Key (KEY_1);

// ***  Referential Subset Constraints

Alter table B Add Foreign Key A_B(KEY_1) References A (KEY_1);

Alter table C Add Foreign Key A_C (KEY_1)  References A (KEY_1);


To SELECT from A, B and C into the same PowerBuilder DataWindow:

         {oj "A" LEFT OUTER JOIN "B" ON "A"."KEY_1" = "B"."KEY_1"},
         {oj "A" LEFT OUTER JOIN "C" ON "A"."KEY_1" = "C"."KEY_1"}  
         "A"."Key_2_1" ASC,  
         "A"."Key_2_2" ASC,  

PowerBuilder DataWindow Painter clarify what LEFT OUTER JOIN is:
{oj "A" LEFT OUTER JOIN "B" ON "A"."KEY_1" = "B"."KEY_1"}, meaning:
A.KEY_1 = B.KEY_1 and rows from A that have no B

{oj "A" LEFT OUTER JOIN "C" ON "A"."KEY_1" = "C"."KEY_1"}, meaning:
A.KEY_1 = C.KEY_1 and rows from A that have no C
In the graphic select browser LEFT OUTER JOIN is shown as (sketch)

-------+                       +----------....
A      |                       | B
-------+       +----+          +----------....
KEY_1  |------o| =  |--------- |KEY_1
       |       +----+          |
       :                       :

To update a data base entails UPDATEs, INSERTs and DELETEs. What the
Client software of choice is allowed or able to do is
constrained by the different Uniqueness and Subset constraints
already implemented above by the RDBMS of choice. These constraints
are not automatically  implemented in the client. To minimise the number
of updates that the RDBMS refuse to perform, it is wise to
implement a copy of the same constraint in the client. In principle
it should be possible to make the same program that perform the
NIAM grouping into Databases designs to perform an automatic
generation of user interfaces. Such a tool must for obvious reasons be
integrated closely with the client design program or otherwise
the graphical changes of the client will not feed back to the model
and consequently a change of the model can not be integrated with the
old clients designs. It is also worth hoping for the same tools
ability to change both database designs and consequently the old

In the mean time we have to implement things more or less by hand and
hope that client design tools like PowerBuilder 4.0 is able to do
part of the work for us. Using the SQL SELECT statement above we can
easily populate a so called DataWindow with A rows with additional
corresponding data from B and C rows. A Window in PowerBuilder is
like any window on a Macintosh or Windows program. Such a Window
may contain several DataWindows that may or may not be dependent on
each others contents. Such dependencies must be explicitly coded (as
RowFocusChanged events for the master to change the Detail
DataWindow)even though they should be obvious from the NIAM (data)
model and consequently from the RDBMS constraints. Another quirk of
the PowerBuilder DataWindow is its less than half-hearted ability to
update more than one table from a DataWindow. If you read the
documentation and believe what's written you may be able to
decipher that it is in deed POSSIBLE to update several tables from
the same DataWindow. I'm sorry to say, but it is quite an advantage
that these tables are not interrelated too closely for the multiple
update to work at it's best. What is meant by that will be addressed as follows:
How to update all modified rows.
In the Update Criteria Dialogue box of the DataWindow of choice you
can set which of the tables to update per default when updates are
required, we chose table A since it is the super class vs. B and C.
To have a tupple/object you at least have to have the required fields
of A populated. If you are able to decipher the less than half a
meter of documentation correctly you will be able to first update all
the changed columns of A without resetting the flags for changes in
possibly empty B and C columns: We assume that our DataWindow is
named dw_1:

if dw_1. Update(TRUE, FALSE) < 0 then
   Success = FALSE
end if

if Success then ...

-will do that for you. We don't want to COMMIT yet because things may
get sour later on. Then comes all the convoluted code to turn off the
update criteria for A. (A is implicit here)

dw_1.Modify("KEY_1.Update = No")
dw_1.Modify("Key_2_1.Update = No")
dw_1.Modify("Key_2_2.Update = No")
dw_1.Modify("Comment.Update = No")

We don't want the primary key of A to be used in the WHERE statement

dw_1.Modify("KEY_1.Key = No")

Then we want to set B to be the next table to update:

dw_1.Modify("DataWindow.Table.UpdateTable = ~"B~"")

Remember that typos here make for no feed-back what so ever ;-(
Then we tell which attributes from B to be updated if they have
their changed flags enabled (Since the KEY_1 have the same name
as in A the system has supplied a more explicit name B_KEY_1:

dw_1.Modify("B_KEY_1.Update = Yes")
dw_1.Modify("B_KEY_1.Key = Yes")
dw_1.Modify("B_Title.Update = Yes")

Then we are ready for a new update along the same lines as the
previous one

if dw_1.Update(TRUE, FALSE) < 0 then
   Success = FALSE
end if
if Success then ?

Since we have another table that may have changed columns/attributes
we still don't want to COMMIT since there are still things that may
stumble... Now we change Update Criteria using the convoluted scripts
once again:

dw_1.Modify("B_KEY_1.Update = No")
dw_1.Modify("B_KEY_1.Key = No")
dw_1.Modify("B_Title.Update = No")

dw_1.Modify("DataWindow.Table.UpdateTable = ~"C~"")

dw_1.Modify("C_KEY_1.Update = Yes")
dw_1.Modify("C_KEY_1.Key = Yes")
dw_1.Modify("C_Day.Update = Yes")

Then we are ready for the finale where we choose to forget all Update
flags if things are successful otherwise the update flags will still
be around:

if dw_1.Update() > 0 then
end if

Then we have to set Update Criteria back so another quest for update
behave the same way:

dw_1.Modify("C_KEY_1.Update = No")
dw_1.Modify("C_KEY_1.Key = No")
dw_1.Modify("C_Day.Update = No")

dw_1.Modify("DataWindow.Table.UpdateTable = ~"A~"")

dw_1.Modify("KEY_1.Update = Yes")
dw_1.Modify("Key_2_1.Update = Yes")
dw_1.Modify("Key_2_2.Update = Yes")
dw_1.Modify("Comment.Update = Yes")

As long as you need to update without any INSERTions or DELETions
things will be well. But try to make an INSERT to be handled by
this mechanism and you will get into problems with NULL values for
the B and C columns that are not populated.

insertion must be performed in a, b, c order since B and C is
dependent on A.

Insertion could have been done through the same mechanism as updates
if it where not for a simple need. A's secondary key can be system
assigned as the next key in the row of keys already used. New objects
must be  INSERTed and COMMITted one at a time.

Deletion must be performed in the opposite order; delete b's or c's
(that are dependent on a's), then a's are deletet as there are then
no objects depending on them.

Since deletion is performed in another order than updates the en-mass
update mechanism are deemed un-useful for deletion.

LEFT OUTER JOIN is not part of contemporary Standard SQL (SQL-92?)
according to Elmasri/Navathe: Fundamentals of Database Systems
pp.736. It's actually quite a disaster that such a tool for table
evolution is not part of the standard but have to be searched for
on a implementation basis.


Is there any chance that the next PowerBuilder could solve this problem
more implicit?

Is there any chance that SQL3 insert and delete will be more powerful by
infering more from interdependencies and constraints?

 Universitetsbiblioteket i Oslo/Bibliografisk avd.
 The University of Oslo library/Bibliographic dept.
 N-0242 Oslo, Norway

                                             Kolbj|rn H. Aamb|
                                             University Librarian    

Phone: +47 22 85 91 36
Fax: +47 22 85 90 50
E-mail: kolbjorn.aa...@ub.uio.no
There is No Way like Norway
Welcome to Virtual Relativity!


1. Multiple LEFT OUTER JOIN Syntax?

Hello Everyone,

I am creating an ODBC query to an Access database where the first table
has records for every Listings.ID, but each of the 2 tables I need to join
to it (Addendums and ListingMisc ) may or may not have records with a
matching value in their corresponding field.  The following statements work
as expected for one or the other join:

cSelect := SELECT Listings.ID, Listings.Class, Listings.Type,
Addendums.Addendum FROM
Listings LEFT OUTER JOIN Addendums ON (Listings.ID = Addendums.ListingID)


cSelect := SELECT Listings.ID, Listings.Class, Listings.Type,
ListingMisc.ListingFeatures1 FROM Listings LEFT OUTER JOIN ListingsMisc ON
(Listings.ID = ListingMisc.ListingID)

oServer1 := SQLSelect{ cSelect, oConn1 }
oServer1:Execute( cSelect )

The above Execute works correctly for either of the cSelect values listed.

How do I combine the Joins?  The MS ODBC help file says that both lists and
nested outer joins are supported, but I can't find samples of them and all
my attempts have returned syntax errors either at the FROM level or the JOIN


Lynn C. Ormond
Lucero Research

2. graphtex, TeXDraw - errors/help wanted

3. LEFT OUTER JOIN & inheritance

4. Human ERROR... It Get's Us All (Humor)

5. LEFT OUTER JOIN and Update()


7. Left Outer Join of Views?

8. Left Outer Join Syntax

9. LEFT OUTER JOIN problem


11. Left Outer Join Question

12. Left outer join with WHERE clause?