mbordas@irma.Sun.COM (Mark Bordas - Sun Consulting) (01/13/90)
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
dberg@cod.NOSC.MIL (David I. Berg) (01/14/90)
In article <8314@newstop.EBay.Sun.COM>, mbordas@irma.Sun.COM (Mark Bordas - Sun Consulting) writes: > So the SQL statement, > "select socsec, name, amount_owed from table1 t1, table2 t2 > where t1.socsec = t2.socsec" returns: > (details omitted) > In other words, only the records that exist in both tables will be retrieved. > .....there are times when it would be desirable to have the > SQL statement return: > (details omitted) Use the SQL construct: select x,y,... from a OUTER b where....., like so: "select socsec, name, amount_owed from table1 t1, OUTER table2 t2 where t1.socsec = t2.socsec" This will give you the results you desire. -- David I. Berg (dberg@nosc.mil) GENISYS Information Systems MILNET: dberg@nosc.mil 4250 Pacific Hwy #118 UUCP: {akgua decvax dcdwest ucbvax}! San Diego, CA 92110 sdcsvax!noscvax!dberg (619) 226-1122
dave@sea375.UUCP (David A. Wilson) (01/15/90)
From article <1751@cod.NOSC.MIL>, by dberg@cod.NOSC.MIL (David I. Berg): > > Use the SQL construct: select x,y,... from a OUTER b where....., > like so: > > "select socsec, name, amount_owed from table1 t1, OUTER table2 t2 > where t1.socsec = t2.socsec" > Is the 'outer' specifier in any ANSI SQL standard? I sure haven't seen it in most RDBMS systems I have encountered. I believe a few exist that do implement outer joins but the last SQL specification I saw did not include it. If anyone working on the ANSI SQL standard reads this, I hope outer join will become part of the ANSI SQL soon. All other work arounds for solving outer join problems are usually slow and ugly(definitely not for anyone with a weak stomach.). -- David A. Wilson dave@sea375.uucp ...uw-beaver!uw-entropy!quick!sigma!sea375!dave
jeff@unify.uucp (Jeff Mischkinsky) (01/16/90)
In article <257@sea375.UUCP> dave@sea375.UUCP (David A. Wilson) writes: >From article <1751@cod.NOSC.MIL>, by dberg@cod.NOSC.MIL (David I. Berg): >> >> Use the SQL construct: select x,y,... from a OUTER b where....., >> like so: >> >> "select socsec, name, amount_owed from table1 t1, OUTER table2 t2 >> where t1.socsec = t2.socsec" >> >Is the 'outer' specifier in any ANSI SQL standard? I sure haven't seen it >in most RDBMS systems I have encountered. I believe a few exist that do >implement outer joins but the last SQL specification I saw did not include >it. If anyone working on the ANSI SQL standard reads this, I hope outer join >will become part of the ANSI SQL soon. All other work arounds for solving >outer join problems are usually slow and ugly(definitely not for anyone with >a weak stomach.). >-- > David A. Wilson > dave@sea375.uucp > ...uw-beaver!uw-entropy!quick!sigma!sea375!dave The current working draft for the "next" ANSI SQL standard (SQL2) contains outer join. In fact it allows you to specifiy your favorite variety-- left, right, full, and inner. -- Jeff Mischkinsky internet: jeff@unify.UUCP Unify Corporation ...!{csusac,pyramid}!unify!jeff 3870 Rosin Court voice: (916) 920-9092 fax: (916) 921-5340 Sacramento, CA 95834