Path: utzoo!utgpu!jarvis.csri.toronto.edu!rutgers!cs.utexas.edu!uunet!seismo!sundc!newstop!irma.Sun.COM!mbordas
From: mbordas@irma.Sun.COM (Mark Bordas - Sun Consulting)
Newsgroups: comp.databases
Subject: outer join question
Keywords: outer, join, ingres, sql
Message-ID: <8314@newstop.EBay.Sun.COM>
Date: 12 Jan 90 22:45:20 GMT
Sender: news@newstop.EBay.Sun.COM
Distribution: usa
Lines: 65


During the development of a database using Ingres, we have 
consistently run into difficulties due to outer joins - for those
unfamiliar with the terminology, the following example says it all.

 	table1			|		table2
 	------			|		------
 socsec 	 name		|	socsec		amount_owed
 ------		 ------		|	------		-----------
 111-11-1111	 Tom		|	111-11-1111	$100.00
 111-11-1112	 Dick		|	111-11-1112	$200.00
 111-11-1113	 Harry		|	111-11-1114	$400.00
 111-11-1114	 Carol		|	111-11-1115	$500.00
 111-11-1115	 Alice		|	111-11-1116	$600.00


 So the SQL statement,

 "select socsec, name, amount_owed from table1 t1, table2 t2
  where t1.socsec = t2.socsec"

  returns:

 		table3	
 		------	
 socsec 	 name		amount_owed
 ------		 -----		-----------
 111-11-1111	 Tom		$100.00
 111-11-1112	 Dick		$200.00
 111-11-1114	 Carol		$400.00
 111-11-1115	 Alice		$500.00

In other words, only the records that exist in both tables will be retrieved.
In most cases, this behavior is logical and desirable. This means, however, 
that if a record is missing from only one of these tables, you effectively get
none of the information, and, what is especially troublesome, there is no 
indication that this has happened.

In other words, there are times when it would be desirable to have the
SQL statement return:

 		table3	
 		------	
 socsec 	 name		amount_owed
 ------		 -----		-----------
 111-11-1111	 Tom		$100.00
 111-11-1112	 Dick		$200.00
 111-11-1113	 Harry		
 111-11-1114	 Carol		$400.00
 111-11-1115	 Alice		$500.00
 111-11-1115	  		$600.00

The fault is not in SQL itself, since it is correctly evaluating the 
statement. In practice, though, we need to provide for the second case.
Ingres describes this problem in its documentation, and suggest that a union 
be used instead (in effect, 2 SQL statements whose results are combined, 
rather than an actual relation between tables). Unfortunately, the performance
of unions under these conditions is far too slow to be useful.

If others have dealt with this situation, I would appreciate your resonses; 
also, a comparision with solutions used by other database products would 
be helpful.

mbordas@irma.East.Sun.Com
..!philabs!gotham!mbordas