Thissectionshowshow tocreateanduseouterjoinsinaSELECTstatement. “Creatinga Join”onpage2-52 discussesinnerjoins.Whereas aninnerjoin treatstwoormorejoinedtablesequally,an outerjointreatstwoormore joinedtablesasymmetrically.Anouterjoinmakesoneof thetablesdominant (alsocalledtheoutertable)overtheothersubordinate tables(alsocalled inner tables).
Inan innerjoinorina simplejoin, theresult containsonlythecombinations ofrowsthatsatisfythejoinconditions. Rowsthatdonotsatisfythejoin conditionsarediscarded.
Inan outerjoin,theresultcontains thecombinations ofrowsthatsatisfythe joinconditions andtherowsfromthedominanttablethatwouldotherwisebe discardedbecausenomatchingrowwas foundinthesubordinate table.The rowsfromthedominanttablethatdonothavematchingrowsinthe subordinatetablecontainNULLvaluesinthecolumns selectedfromthe subordinatetable.
Anouterjoinallowsyoutoapplyjoinfilterstotheinnertablebefore thejoin conditionisapplied.
Earlierversionsof thedatabase serversupportedonlytheInformixextension totheANSI-SQLstandardsyntaxforouterjoins.Thissyntaxisstill
supported.However,theANSI-SQLstandardsyntaxprovidesformore flexibilitywithcreatingqueries.Itisrecommendedthatyouusethe order_num total_price 1018 $15.00 1013 $19.80 1003 $20.00 1005 $36.00 1006 $36.00 1013 $36.00 1010 $36.00 1013 $40.00 1022 $40.00 1023 $40.00
ANSI-SQLstandardsyntaxtocreatenewqueries. Whicheverformofsyntax youuse,youmustuseitforallouterjoins inasinglequeryblock.
Beforeyourely onouterjoins,determinewhetheroneor moreinnerjoinscan work.Youcanoftenuseaninnerjoinwhenyoudo notneed supplemental informationfromothertables.
Important: YoucannotcombineInformixandANSIouter-joinsyntax inthe samequeryblock.
Forinformationonthesyntaxofouterjoins,seetheIBM Informix:Guideto SQLSyntax.
InformixExtensiontoOuterJoinSyntax
TheInformixextensiontoouter-joinsyntaxbeginsanouter joinwith the OUTERkeyword.WhenyouusetheInformixsyntax,youmust includethe joincondition intheWHEREclause.WhenyouusetheInformixsyntax foran outerjoin,thedatabaseserversupportsthefollowingthreebasictypesof outerjoins:
v Asimpleouterjoinontwotables
v Anouterjoinfora simplejointoathirdtable
v Anouterjoinoftwotablestoa thirdtable
Anouterjoinmusthavea Projectionclause,aFROMclause,anda WHERE clause.Thejoinconditions areexpressedinaWHEREclause.Totransforma simplejoinintoanouterjoin, insertthekeywordOUTERdirectlybefore the nameofthesubordinatetablesintheFROMclause.Asshownlaterinthis section,youcanincludetheOUTERkeywordmorethanonceinyourquery.
NoInformixextension toouter-joinsyntax isequivalenttotheANSIright outerjoin.
ANSIJoinSyntax
ThefollowingANSIjoinsaresupported:
v Leftouterjoin
DynamicServer
v
Rightouterjoin
EndofDynamic Server
TheANSIouter-joinsyntaxbeginsanouter joinwith theLEFTJOIN,LEFT OUTERJOIN,RIGHTJOIN,orRIGHTOUTERJOINkeywords. TheOUTER keywordisoptional.Queriescanspecifyajoinconditionandoptional join
filtersintheON clause.TheWHEREclausespecifiesa post-joinfilter.In addition,youcanexplicitlyspecifythetypeofjoinusingtheLEFTorright clause.ANSIjoinsyntaxalsoallowsthedominantorsubordinatepartof an outerjointo betheresultsetof anotherjoin,whenyoubeginthejoinwitha leftparenthesis.
IfyouuseANSIsyntax foranouterjoin,youmustusetheANSIsyntaxfor allouterjoinsina singlequeryblock.
Tip: Theexamples inthissectionusetablealiasesforbrevity. “UsingAliases” onpage2-59discussestablealiases.
LeftOuterJoin
Inthesyntax ofaleft outerjoin,thedominanttableoftheouterjoinappears totheleft ofthekeywordthatbeginstheouterjoin.Aleftouterjoinreturns alloftherowsforwhichthejoinconditionistrueand,in addition,returnsall otherrowsfromthedominanttable anddisplaysthecorrespondingvalues fromthesubservienttableasNULL.
ThefollowingqueryusesANSIsyntaxLEFTOUTERJOINtoachievethe sameresultsasFigure5-30onpage5-18,whichusestheInformixouter-join syntax:
Inthis example,youcanusetheONclausetospecifythejoincondition.You canaddanadditionalfilterintheWHEREclausetolimityourresult set;such afilterisa post-joinfilter.
Thefollowingqueryreturnsonlyrowsinwhichcustomershavenotmade anycallstocustomer service.Inthisquery,thedatabaseserverappliesthe filterintheWHEREclauseafteritperformstheouterjoinonthe
customer_numcolumnofthecustomerand cust_callstables. SELECT c.customer_num, c.lname, c.company, c.phone,
u.call_dtime, u.call_descr
FROM customer c LEFT OUTER JOIN cust_calls u ON c.customer_num = u.customer_num
Figure5-24.Query
SELECT c.customer_num, c.lname, c.company, c.phone, u.call_dtime, u.call_descr
FROM customer c LEFT OUTER JOIN cust_calls u ON c.customer_num = u.customer_num
WHERE u.customer_num IS NULL
Inaddition totheprevious examples,thefollowingexamples showvarious typesofqueryconstructionsthatareavailable withANSIjoinsyntax: SELECT *
FROM (t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1) ON t1.c1=t3.c1) JOIN (t4 LEFT OUTER JOIN t5 ON t4.c1=t5.c1) ON t1.c1=t4.c1;
SELECT *
FROM (t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1) ON t1.c1=t3.c1),
(t4 LEFT OUTER JOIN t5 ON t4.c1=t5.c1) WHERE t1.c1 = t4.c1;
SELECT *
FROM (t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1) ON t1.c1=t3.c1) LEFT OUTER JOIN (t4 JOIN t5 ON t4.c1=t5.c1) ON t1.c1=t4.c1;
SELECT *
FROM t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1) ON t1.c1=t2.c1;
SELECT *
FROM t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1) ON t1.c1=t3.c1;
SELECT *
FROM (t1 LEFT OUTER JOIN t2 ON t1.c1=t2.c1) LEFT OUTER JOIN t3 ON t2.c1=t3.c1;
SELECT *
FROM (t1 LEFT OUTER JOIN t2 ON t1.c1=t2.c1) LEFT OUTER JOIN t3 ON t1.c1=t3.c1;
SELECT *
FROM t9, (t1 LEFT JOIN t2 ON t1.c1=t2.c1), (t3 LEFT JOIN t4 ON t3.c1=10), t10, t11, (t12 LEFT JOIN t14 ON t12.c1=100); RightOuterJoin(IDS)
Inthesyntax ofarightouterjoin, thedominanttable oftheouter join appearstotherightofthekeywordthatbeginstheouterjoin.Arightouter joinreturnsalloftherowsforwhichthejoinconditionistrueand,in addition,returnsallotherrowsfromthedominanttableand displaysthe correspondingvaluesfromthesubservienttableasNULL.
Figure5-26 isan exampleofarightouter joinonthecustomerand orders tables.
Figure5-26 returnsallrowsfromthedominanttableordersand,asnecessary, displaysthecorrespondingvaluesfromthesubservienttablecustomer as NULL.
SimpleJoin
Figure5-28 isanexample ofasimple joinonthecustomerand cust_calls tables.
Figure5-28 returnsonlythoserowsinwhichthecustomer hasmadea callto customerservice,asFigure5-29 shows.
SELECT c.customer_num, c.fname, c.lname, o.order_num, o.order_date, o.customer_num
FROM customer c RIGHT OUTER JOIN orders o ON (c.customer_num = o.customer_num);
Figure5-26.Query
customer_num fname lname order_num order_date customer_num
104 Anthony Wiggins 1001 05/30/1998 104
101 Ludwig Pauli 1002 05/30/1998 101
104 Anthony Wiggins 1003 05/30/1998 104
<NULL> <NULL> <NULL> 1004 06/05/1998 106
Figure5-27.QueryResult
SELECT c.customer_num, c.lname, c.company, c.phone, u.call_dtime, u.call_descr FROM customer c, cust_calls u
WHERE c.customer_num = u.customer_num
SimpleOuterJoinon TwoTables
Figure5-30 usesthesameprojection clause,tables, andcomparisoncondition astheprecedingexample,butthis timeitcreates asimple outerjoinin Informixextensionsyntax.
TheadditionofthekeywordOUTERbeforethecust_callstablemakesitthe subservienttable.Anouterjoincausesthequerytoreturn informationonall customers,whetherornottheyhavemadecallstocustomerservice.Allrows fromthedominantcustomertable areretrieved,and NULLvaluesare
assignedtocolumnsofthesubservientcust_callstable,asFigure5-31 shows. customer_num 106
lname Watson company Watson & Son phone 415-389-8789 call_dtime 1998-06-12 08:20
call_descr Order was received, but two of the cans of ANZ tennis balls within the case were empty .
. .
customer_num 116 lname Parmelee company Olympic City phone 415-534-8822 call_dtime 1997-12-21 11:24
call_descr Second complaint from this customer! Received two cases right-handed outfielder gloves (1 HRO) instead of one case lefties.
Figure5-29.QueryResult
SELECT c.customer_num, c.lname, c.company, c.phone, u.call_dtime, u.call_descr FROM customer c, OUTER cust_calls u WHERE c.customer_num = u.customer_num
OuterJoinfora SimpleJointoa ThirdTable
UsingANSIsyntax, Figure5-32 showsanouterjointhatistheresultof a simplejointoathirdtable.Thissecond typeofouterjoinisknownasanested simplejoin.
Figure5-32 firstperformsasimple joinontheordersand itemstables, retrievinginformationonall ordersforitemswitha manu_codeofKARor SHM.Itthenperformsanouterjointocombinethis informationwithdata customer_num 101
lname Pauli
company All Sports Supplies phone 408-789-8075 call_dtime call_descr customer_num 102 lname Sadler company Sports Spot phone 415-822-1289 call_dtime call_descr . . . customer_num 107 lname Ream
company Athletic Supplies phone 415-356-9876 call_dtime call_descr customer_num 108 lname Quinn
company Quinn’s Sports phone 415-544-8729 call_dtime
call_descr
Figure5-31.QueryResult
SELECT c.customer_num, c.lname, o.order_num, i.stock_num, i.manu_code, i.quantity
FROM customer c, LEFT OUTER JOIN (orders o, items i) WHERE c.customer_num = o.customer_num
AND o.order_num = i.order_num AND manu_code IN (’KAR’, ’SHM’) ORDER BY lname
fromthedominantcustomertable.AnoptionalORDER BYclause reorganizes thedataintotheformthatFigure5-33shows.
OuterJoinofTwoTables toaThirdTable
UsingInformixextensionsyntax, Figure5-34 showsanouterjointhatisthe resultofanouter joinofeachof twotablestoathirdtable.Inthis thirdtype ofouterjoin,joinrelationshipsarepossibleonlybetweenthedominanttable andthesubservienttables.
Figure5-34 individuallyjoinsthesubservienttablesordersandcust_callsto thedominantcustomertable;itdoesnotjointhetwosubservienttables.An INTOTEMPclauseselectstheresultsinto atemporarytableforfurther manipulationorqueries, asFigure5-35shows.
customer_num lname order_num stock_num manu_code quantity
114 Albertson 118 Baxter 113 Beatty . . . 105 Vector 121 Wallack 1018 302 KAR 3 106 Watson
Figure5-33.QueryResult
SELECT c.customer_num, c.lname, o.order_num, order_date, call_dtime
FROM customer c, OUTER orders o, OUTER cust_calls x WHERE c.customer_num = o.customer_num
AND c.customer_num = x.customer_num ORDER BY lname
INTO TEMP service
IfFigure5-34 hadtriedtocreatea joinconditionbetweenthetwosubservient tablesoand x,asFigure5-36 shows,anerrormessagewouldindicatethe creationofatwo-sidedouterjoin.
JoinsThatCombineOuterJoins
Toachievemultiplelevelsofnesting,youcancreatea jointhatemploysany combinationofthethreetypesofouterjoins.UsingANSIsyntax,Figure5-37 createsajointhatistheresultofa combinationof asimple outerjoinontwo tablesanda secondouterjoin.
Figure5-37 firstperformsanouterjoinontheorders anditemstables, retrievinginformationonall ordersforitemswitha manu_codeofKARor SHM.Itthenperformsasecond outerjointhatcombinesthisinformation withdatafromthedominantcustomer table.
customer_num lname order_num order_date call_dtime
114 Albertson 118 Baxter 113 Beatty 103 Currie 115 Grant 101006/17/1998 . . . 117 Sipes 101206/18/1998 105 Vector 121 Wallack 101807/10/1998 1998-07-10 14:05 106 Watson 100405/22/1998 1998-06-12 08:20 106 Watson 101406/25/1998 1998-06-12 08:20
Figure5-35.QueryResult
WHERE o.customer_num = x.customer_num
Figure5-36.Query
SELECT c.customer_num, c.lname, o.order_num, stock_num, manu_code, quantity
FROM customer c, OUTER (orders o, OUTER items i) WHERE c.customer_num = o.customer_num
AND o.order_num = i.order_num AND manu_code IN (’KAR’, ’SHM’) ORDER BY lname
Youcanspecifythejoinconditionsintwowayswhenyouapplyanouterjoin totheresult ofanouterjointoa thirdtable.Thetwosubservienttablesare joined,but youcanjointhedominanttabletoeithersubservienttablewithout affectingtheresultsif thedominanttableand thesubservienttablesharea commoncolumn.