SQL--JOIN之完全用法? ???
? ?? ?
? ?? ???
? ?? ???
? ?
? ?
??外聯(lián)接。外聯(lián)接可以是左向外聯(lián)接、右向外聯(lián)接或完整外部聯(lián)接。? ???
??在? ?FROM? ?子句中指定外聯(lián)接時，可以由下列幾組關(guān)鍵字中的一組指定：? ?
? ?
??LEFT? ?JOIN? ?或? ?LEFT? ?OUTER? ?JOIN。? ???
??左向外聯(lián)接的結(jié)果集包括? ?LEFT? ?OUTER? ?子句中指定的左表的所有行，而不僅僅是聯(lián)接列所匹配的行。如果左表的某行在右表中沒有匹配行，則在相關(guān)聯(lián)的結(jié)果集行中右表的所有選擇列表列均為空值。? ?
? ?
??RIGHT? ?JOIN? ?或? ?RIGHT? ?OUTER? ?JOIN。? ???
??右向外聯(lián)接是左向外聯(lián)接的反向聯(lián)接。將返回右表的所有行。如果右表的某行在左表中沒有匹配行，則將為左表返回空值。? ?
? ?
??FULL? ?JOIN? ?或? ?FULL? ?OUTER? ?JOIN。? ???
??完整外部聯(lián)接返回左表和右表中的所有行。當某行在另一個表中沒有匹配行時，則另一個表的選擇列表列包含空值。如果表之間有匹配行，則整個結(jié)果集行包含基表的數(shù)據(jù)值。? ?
? ?
??僅當至少有一個同屬于兩表的行符合聯(lián)接條件時，內(nèi)聯(lián)接才返回行。內(nèi)聯(lián)接消除與另一個表中的任何行不匹配的行。而外聯(lián)接會返回? ?FROM? ?子句中提到的至少一個表或視圖的所有行，只要這些行符合任何? ?WHERE? ?或? ?HAVING? ?搜索條件。將檢索通過左向外聯(lián)接引用的左表的所有行，以及通過右向外聯(lián)接引用的右表的所有行。完整外部聯(lián)接中兩個表的所有行都將返回。? ?
? ?
??Microsoft?? ?SQL? ?Server?? ?2000? ?對在? ?FROM? ?子句中指定的外聯(lián)接使用以下? ?SQL-92? ?關(guān)鍵字：? ???
? ?
??LEFT? ?OUTER? ?JOIN? ?或? ?LEFT? ?JOIN? ?
? ?
? ?
??RIGHT? ?OUTER? ?JOIN? ?或? ?RIGHT? ?JOIN? ?
? ?
? ?
??FULL? ?OUTER? ?JOIN? ?或? ?FULL? ?JOIN? ???
??SQL? ?Server? ?支持? ?SQL-92? ?外聯(lián)接語法，以及在? ?WHERE? ?子句中使用? ?*=? ?和? ?=*? ?運算符指定外聯(lián)接的舊式語法。由于? ?SQL-92? ?語法不容易產(chǎn)生歧義，而舊式? ?Transact-SQL? ?外聯(lián)接有時會產(chǎn)生歧義，因此建議使用? ?SQL-92? ?語法。? ?
? ?
??使用左向外聯(lián)接? ?
??假設(shè)在? ?city? ?列上聯(lián)接? ?authors? ?表和? ?publishers? ?表。結(jié)果只顯示在出版商所在城市居住的作者（本例中為? ?Abraham? ?Bennet? ?和? ?Cheryl? ?Carson）。? ?
? ?
??若要在結(jié)果中包括所有的作者，而不管出版商是否住在同一個城市，請使用? ?SQL-92? ?左向外聯(lián)接。下面是? ?Transact-SQL? ?左向外聯(lián)接的查詢和結(jié)果：? ?
? ?
??USE? ?pubs? ?
??SELECT? ?a.au_fname,? ?a.au_lname,? ?p.pub_name? ?
??FROM? ?authors? ?a? ?LEFT? ?OUTER? ?JOIN? ?publishers? ?p? ?
??ON? ?a.city? ?=? ?p.city? ?
??ORDER? ?BY? ?p.pub_name? ?ASC,? ?a.au_lname? ?ASC,? ?a.au_fname? ?ASC? ?
? ?
??下面是結(jié)果集：? ?
? ?
??au_fname? ?au_lname? ?pub_name? ???
??--------------------? ?------------------------------? ?-----------------? ???
??Reginald? ?Blotchet-Halls? ?NULL? ?
??Michel? ?DeFrance? ?NULL? ?
??Innes? ?del? ?Castillo? ?NULL? ?
??Ann? ?Dull? ?NULL? ?
??Marjorie? ?Green? ?NULL? ?
??Morningstar? ?Greene? ?NULL? ?
??Burt? ?Gringlesby? ?NULL? ?
??Sheryl? ?Hunter? ?NULL? ?
??Livia? ?Karsen? ?NULL? ?
??Charlene? ?Locksley? ?NULL? ?
??Stearns? ?MacFeather? ?NULL? ?
??Heather? ?McBadden? ?NULL? ?
??Michael? ?O'Leary? ?NULL? ?
??Sylvia? ?Panteley? ?NULL? ?
??Albert? ?Ringer? ?NULL? ?
??Anne? ?Ringer? ?NULL? ?
??Meander? ?Smith? ?NULL? ?
??Dean? ?Straight? ?NULL? ?
??Dirk? ?Stringer? ?NULL? ?
??Johnson? ?White? ?NULL? ?
??Akiko? ?Yokomoto? ?NULL? ?
??Abraham? ?Bennet? ?Algodata? ?Infosystems? ?
??Cheryl? ?Carson? ?Algodata? ?Infosystems? ?
? ?
??(23? ?row(s)? ?affected)? ?
? ?
??不管是否與? ?publishers? ?表中的? ?city? ?列匹配，LEFT? ?OUTER? ?JOIN? ?均會在結(jié)果中包含? ?authors? ?表的所有行。注意：結(jié)果中所列的大多數(shù)作者都沒有相匹配的數(shù)據(jù),因此，這些行的? ?pub_name? ?列包含空值。? ?
? ?
??使用右向外聯(lián)接? ?
??假設(shè)在? ?city? ?列上聯(lián)接? ?authors? ?表和? ?publishers? ?表。結(jié)果只顯示在出版商所在城市居住的作者（本例中為? ?Abraham? ?Bennet? ?和? ?Cheryl? ?Carson）。SQL-92? ?右向外聯(lián)接運算符? ?RIGHT? ?OUTER? ?JOIN? ?指明：不管第一個表中是否有匹配的數(shù)據(jù)，結(jié)果將包含第二個表中的所有行。? ?
? ?
??若要在結(jié)果中包括所有的出版商，而不管城市中是否還有出版商居住，請使用? ?SQL-92? ?右向外聯(lián)接。下面是? ?Transact-SQL? ?右向外聯(lián)接的查詢和結(jié)果：? ?
? ?
??USE? ?pubs? ?
??SELECT? ?a.au_fname,? ?a.au_lname,? ?p.pub_name? ?
??FROM? ?authors? ?AS? ?a? ?RIGHT? ?OUTER? ?JOIN? ?publishers? ?AS? ?p? ?
??ON? ?a.city? ?=? ?p.city? ?
??ORDER? ?BY? ?p.pub_name? ?ASC,? ?a.au_lname? ?ASC,? ?a.au_fname? ?ASC? ?
? ?
??下面是結(jié)果集：? ?
? ?
??au_fname? ?au_lname? ?pub_name? ???
??--------------------? ?------------------------? ?--------------------? ???
??Abraham? ?Bennet? ?Algodata? ?Infosystems? ?
??Cheryl? ?Carson? ?Algodata? ?Infosystems? ?
??NULL? ?NULL? ?Binnet? ?&? ?Hardley? ?
??NULL? ?NULL? ?Five? ?Lakes? ?Publishing? ?
??NULL? ?NULL? ?GGG&G? ?
??NULL? ?NULL? ?Lucerne? ?Publishing? ?
??NULL? ?NULL? ?New? ?Moon? ?Books? ?
??NULL? ?NULL? ?Ramona? ?Publishers? ?
??NULL? ?NULL? ?Scootney? ?Books? ?
? ?
??(9? ?row(s)? ?affected)? ?
? ?
??使用謂詞（如將聯(lián)接與常量比較）可以進一步限制外聯(lián)接。下例包含相同的右向外聯(lián)接，但消除銷售量低于? ?50? ?本的書籍的書名：? ?
? ?
??USE? ?pubs? ?
??SELECT? ?s.stor_id,? ?s.qty,? ?t.title? ?
??FROM? ?sales? ?s? ?RIGHT? ?OUTER? ?JOIN? ?titles? ?t? ?
??ON? ?s.title_id? ?=? ?t.title_id? ?
??AND? ?s.qty? ?>? ?50? ?
??ORDER? ?BY? ?s.stor_id? ?ASC? ?
? ?
??下面是結(jié)果集：? ?
? ?
??stor_id? ?qty? ?title? ???
??-------? ?------? ?---------------------------------------------------------? ???
??(null)? ?(null)? ?But? ?Is? ?It? ?User? ?Friendly?? ???
??(null)? ?(null)? ?Computer? ?Phobic? ?AND? ?Non-Phobic? ?Individuals:? ?Behavior? ???
??Variations? ???
??(null)? ?(null)? ?Cooking? ?with? ?Computers:? ?Surreptitious? ?Balance? ?Sheets? ???
??(null)? ?(null)? ?Emotional? ?Security:? ?A? ?New? ?Algorithm? ???
??(null)? ?(null)? ?Fifty? ?Years? ?in? ?Buckingham? ?Palace? ?Kitchens? ???
??7066? ?75? ?Is? ?Anger? ?the? ?Enemy?? ???
??(null)? ?(null)? ?Life? ?Without? ?Fear? ???
??(null)? ?(null)? ?Net? ?Etiquette? ???
??(null)? ?(null)? ?Onions,? ?Leeks,? ?and? ?Garlic:? ?Cooking? ?Secrets? ?of? ?the? ???
??Mediterranean? ???
??(null)? ?(null)? ?Prolonged? ?Data? ?Deprivation:? ?Four? ?Case? ?Studies? ???
??(null)? ?(null)? ?Secrets? ?of? ?Silicon? ?Valley? ???
??(null)? ?(null)? ?Silicon? ?Valley? ?Gastronomic? ?Treats? ???
??(null)? ?(null)? ?Straight? ?Talk? ?About? ?Computers? ???
??(null)? ?(null)? ?Sushi,? ?Anyone?? ???
??(null)? ?(null)? ?The? ?Busy? ?Executive's? ?Database? ?Guide? ???
??(null)? ?(null)? ?The? ?Gourmet? ?Microwave? ???
??(null)? ?(null)? ?The? ?Psychology? ?of? ?Computer? ?Cooking? ???
??(null)? ?(null)? ?You? ?Can? ?Combat? ?Computer? ?Stress!? ???
? ?
??(18? ?row(s)? ?affected)? ?
? ?
??有關(guān)謂詞的更多信息，請參見? ?WHERE。? ???
? ?
??使用完整外部聯(lián)接? ?
??若要通過在聯(lián)接結(jié)果中包括不匹配的行保留不匹配信息，請使用完整外部聯(lián)接。Microsoft?? ?SQL? ?Server?? ?2000? ?提供完整外部聯(lián)接運算符? ?FULL? ?OUTER? ?JOIN，不管另一個表是否有匹配的值，此運算符都包括兩個表中的所有行。? ?
? ?
??假設(shè)在? ?city? ?列上聯(lián)接? ?authors? ?表和? ?publishers? ?表。結(jié)果只顯示在出版商所在城市居住的作者（本例中為? ?Abraham? ?Bennet? ?和? ?Cheryl? ?Carson）。SQL-92? ?FULL? ?OUTER? ?JOIN? ?運算符指明：不管表中是否有匹配的數(shù)據(jù)，結(jié)果將包括兩個表中的所有行。? ?
? ?
??若要在結(jié)果中包括所有作者和出版商，而不管城市中是否有出版商或者出版商是否住在同一個城市，請使用完整外部聯(lián)接。下面是? ?Transact-SQL? ?完整外部聯(lián)接的查詢和結(jié)果：? ?
? ?
??USE? ?pubs? ?
??SELECT? ?a.au_fname,? ?a.au_lname,? ?p.pub_name? ?
??FROM? ?authors? ?a? ?FULL? ?OUTER? ?JOIN? ?publishers? ?p? ?
??ON? ?a.city? ?=? ?p.city? ?
??ORDER? ?BY? ?p.pub_name? ?ASC,? ?a.au_lname? ?ASC,? ?a.au_fname? ?ASC? ?
? ?
??下面是結(jié)果集：? ?
? ?
??au_fname? ?au_lname? ?pub_name? ???
??--------------------? ?----------------------------? ?--------------------? ???
??Reginald? ?Blotchet-Halls? ?NULL? ?
??Michel? ?DeFrance? ?NULL? ?
??Innes? ?del? ?Castillo? ?NULL? ?
??Ann? ?Dull? ?NULL? ?
??Marjorie? ?Green? ?NULL? ?
??Morningstar? ?Greene? ?NULL? ?
??Burt? ?Gringlesby? ?NULL? ?
??Sheryl? ?Hunter? ?NULL? ?
??Livia? ?Karsen? ?NULL? ?
??Charlene? ?Locksley? ?NULL? ?
??Stearns? ?MacFeather? ?NULL? ?
??Heather? ?McBadden? ?NULL? ?
??Michael? ?O'Leary? ?NULL? ?
??Sylvia? ?Panteley? ?NULL? ?
??Albert? ?Ringer? ?NULL? ?
??Anne? ?Ringer? ?NULL? ?
??Meander? ?Smith? ?NULL? ?
??Dean? ?Straight? ?NULL? ?
??Dirk? ?Stringer? ?NULL? ?
??Johnson? ?White? ?NULL? ?
??Akiko? ?Yokomoto? ?NULL? ?
??Abraham? ?Bennet? ?Algodata? ?Infosystems? ?
??Cheryl? ?Carson? ?Algodata? ?Infosystems? ?
??NULL? ?NULL? ?Binnet? ?&? ?Hardley? ?
??NULL? ?NULL? ?Five? ?Lakes? ?Publishing? ?
??NULL? ?NULL? ?GGG&G? ?
??NULL? ?NULL? ?Lucerne? ?Publishing? ?
??NULL? ?NULL? ?New? ?Moon? ?Books? ?
??NULL? ?NULL? ?Ramona? ?Publishers? ?
??NULL? ?NULL? ?Scootney? ?Books