17-3 資?庫查詢??聯???表

ƮwiH]tƭӸƪAPƪiHpmAڭ̥iHھڳoǸ쪺pʨӨϸƮwƧŦXڥ@ɪpAب㦳pʪƮwAY٬pʸƮw]Relational Databases^C

ڭ̭H asp/example/database/song01.mdb song ƪӻA䤺epUG

Ǹ qW ~
Τߨ}W it 1993 
ť iff 1998 
iff 2001 
̼xͤH Ȱa 2000 
ʤH S.H.E 2002 
I.O.I.O. S.H.E 2002 
11 CQIۧAWr è 2002 
12 ̼xͤH Ȱa 2000 
13 ӧA򳣤n iff 1999 
14 ~Ѯ Ȱa 1999 
16 ʦX]K è 2002 

ܩaAuD̡۪v줤AܦhƳOƪApGڭ٭nW[MuD̡۪vơAҦpuXD~vBuvBuݦnvAN|W[hЪơA]Aڭ̥iHNMD۪̬Ʃt@Ӹƪ singerAëwCq]b song ƪ^ҹq]b singer ƪ^Ao˰nBOG

K_Aڭ̨ϥΤ@ӸƸ֪ɮ asp/example/database/song02.mdb ӶiHUA䤤 song ƪepUG

Ǹ W ~
Τߨ}W 1993 
ť 1998 
ʤH 2002 
I.O.I.O. 2002 
17  1983 
18  1993 

singer ƪepUG

Ǹ mW O XD~
it kq 1973 
iff kq 1975 
Ȱa kq 1971 
S.H.E 1960 
11 è kq 1969 

ѤWzCiHDAC@q@OuqǸvA singer ƪuǸvANiHqơCڭ̥iHϥ SQL dߨӦCXqMqҦiզXAҥΪ SQL OG

SELECT song.W, singer.mW FROM song, singer; ұo쪺GOG

W mW
Τߨ}W it 
ť it 
ʤH it 
I.O.I.O. it 
 it 
 it 
Τߨ}W iff 
ť iff 
ʤH iff 
I.O.I.O. iff 
 iff 
 iff 
Τߨ}W Ȱa 
ť Ȱa 
ʤH Ȱa 
I.O.I.O. Ȱa 
 Ȱa 
 Ȱa 
Τߨ}W S.H.E 
ť S.H.E 
ʤH S.H.E 
I.O.I.O. S.H.E 
 S.H.E 
 S.H.E 
Τߨ}W è 
ť è 
ʤH è 
I.O.I.O. è 
 è 
 è 

oӦC@ 30 ơANѲĤ@Ӹƪ]6 ơ^MĤGӸƪ]5 ơ^ҦiզXAOڭ̨èSΨӸƪpAҥHo쪺GMTCڭ̥iH⦳pʪ]bҬO song ƪuqǸvH singer ƪuǸv^[J󦡡AoUC SQL OG

SELECT song.W, singer.mW FROM singer, song WHERE (song.qǸ=singer.Ǹ); u]p˵veOG

ұo쪺GOG

W mW
Τߨ}W it 
ť iff 
ʤH S.H.E 
I.O.I.O. S.H.E 

WzkOH where lyӫwƩҥƪSʡAt@ذkhObu]p˵vwƪpAڭ̥iN song M singer ƪ[Ju]p˵vdߡAM song ƪuqǸvA singer ƪuǸvANiHإ߳oӸƪpʡ]|puqHspʪ^AMA[JݭndߪW١AܵepUG

SQL OpUG SELECT song.Ǹ, song.W, singer.mW FROM song INNER JOIN singer ON song.qǸ = singer.Ǹ; ұo쪺GOG

Ǹ W mW
Τߨ}W it 
ť iff 
ʤH S.H.E 
I.O.I.O. S.H.E 

bWzdҤASQL OϥΤF INNER JOINCƹWAƪs]Join^AiHTG

  1. Inner JoinGu|CXӨӸƪs쪺ƬۦPOC
  2. Left JoinGCXҦӦۥƪOAHγs۵kƪO
  3. Right JoinGCXҦӦۥkƪOAHγs۵ƪO
rƹWeAѡAڭ̨ӬݬݽdҡCpGڭsp쪺uqAN|XusݩʡvApUG

w]ȴNOuȥ]tӸƪs쪺ƬۦPOvAoNO Inner JoinCpGڭI 2ӿﶵ]u]AҦӦ'song'OMu]Adzs۵'singer'v^AoNO Left JoinAuTwvAdߵepUG

䤤puqQ[WF@ӥѥVkbYAN Left JoinA۹ SQL ROpUG SQL OpUG SELECT song.Ǹ, song.W, singer.mW FROM song LEFT JOIN singer ON song.qǸ = singer.Ǹ; ұo쪺GOG

Ǹ W mW
Τߨ}W it 
ť iff 
ʤH S.H.E 
I.O.I.O. S.H.E 
17  null 
18  null 

GNuCXҦqAHγoǺqҥiqvC

Hint
unullvNSƦsbC

pGڭI 3 ӿﶵ]u]AҦӦ'singer'OMu]Adzs۵'song'v^AoNO Right JoinA SQL ROpUG

SELECT song.Ǹ, song.W, singer.mW FROM song RIGHT JOIN singer ON song.qǸ = singer.Ǹ; ұo쪺GOG

Ǹ W mW
Τߨ}W it 
ť iff 
null null Ȱa 
I.O.I.O. S.H.E 
ʤH S.H.E 
null null è 

GNuCXҦqAHγoǺqҥiqvC

Hint
Outer JoinApUG
  • Left Join S٬ Left Outer JoinFRight Join S٬ Right Outer JoinC
  • Full Outer Join NO Left Join M Right Join pAO Access ثeä䴩C

HWkAObإ߬d߮ɡA~إ߰_ƪpAt@Ӥ覡AhOƥNإߦnƪpAoOä[ʪpAҥHbid߮ɡA]|Np]tiӡCHƮw song02.mdb ҡAڭ̥iH}ҡuƮwu/ƮwpϡvAݨ쪺epUG

ڭ̥iH song ƪuqǸvA singer ƪuǸvANiHإ߳oӸƪä[pAɷ|XӡuspveApUG

oӵeﶵujѦҧʡvAڭ̷|bU@`CثeAunUuإߡvAYiإ߮ڰ Inner Join ä[pAA]iHUusvAӳ]wLO JoinAp Left Join άO Right JoinCsإߪpA|buƮwpϡvHuq覡ܥXӡAuqҳsYOpAepUG

YnspAiHuqAYi}ҡuspvC

@إߥä[pAbu]p˵vҦUsd߮ɡAun[JƪAYiä[pAӤݭnAۦʥ[JC


JScript {]pPΡGΩAݪ ASP