Chapter 11 Joining Tables Nov 2001 cylau Fox. Pro Chapter 11 1
Union, Intersection and Minus in Set A = {1, 2, 3} Set B = {1, 3, 5, 7} Set C = {2, 4} z. A Union B = {1, 2, 3, 5, 7} && 1, 3 once z. A Intersection B = {1, 3} z. A Minus B = {2} z. B Minus A = {5, 7} z. A Union B Union C = {1, 2, 3, 4, 5, 7} Nov 2001 cylau Fox. Pro Chapter 11 2
UNION SELECT * FROM set. A; UNION; SELECT * FROM set. B z Similar to APPEND FROM in Fox. Pro z Original files (set. A and set. B) not affected z records present both in set. A and set. B will appear only once. z set. A and set. B must have exactly the same structure Nov 2001 cylau Fox. Pro Chapter 11 3
Example z S 7(name, club) Chan Music Lee Drama Cheung English Wong CYC Ho z S 6(name, club) Wong CYC Kong Locomotive Nov 2001 cylau Fox. Pro Chapter 11 4
UNION (2) SELECT * FROM s 7; UNION; SELECT * FROM s 6 z If the two name fields are of the same type and width, this will succeed. z If the name of a merged field is different in the two source files, that of the first file will be used for the output (e. g. name 1 <> name 2 --> name 1) Nov 2001 cylau Fox. Pro Chapter 11 5
Result of Union Chan Cheung Ho Kong Lee Wong Music English Locomotive Drama CYC z “Wong CYC” appears once z Sorted by name (the 1 st field) Nov 2001 cylau Fox. Pro Chapter 11 6
UNION (3) SELECT * FROM set. A; UNION; SELECT * FROM set. B; UNION; SELECT * FROM set. C && record X, Y, Z && record W, X && record Z, K zresult : record X, Y, Z, W, K Nov 2001 cylau Fox. Pro Chapter 11 7
UNION ALL SELECT * FROM set. A; UNION ALL; SELECT * FROM set. B; zrecords present both in set. A and set. B will appear twice. z{1, 2, 3} Union {1, 3, 5} = {1, 2, 3, 1, 3, 5} Nov 2001 cylau Fox. Pro Chapter 11 8
Example SELECT * FROM s 6; UNION ALL; SELECT * FROM s 7; Wong Kong Chan Lee Cheung Wong Ho CYC Locomotive Music Drama English CYC z “Wong CYC” appears twice z Result obtained by placing s 6 on top of s 7 z NOT Sorted Nov 2001 cylau Fox. Pro Chapter 11 9
JOIN vs UNION z. UNION pools records from various tables with same structure together to make new (virtual) table. y. Adding rows of two tables of the same structure to form a new one z. JOIN combines fields from various tables to make new (virtual) table. y. Adding columns of two tables to form a new one Nov 2001 cylau Fox. Pro Chapter 11 10
Cross-join (Natural join) Table 1 (2 records, m columns) R 1 R 2 Table 2 (3 records, n columns) r 1 r 2 r 3 Nov 2001 cylau Fox. Pro Chapter 11 11
Cross-join (Natural join) (2) SELECT * FROM table 1, table 2 zwill give (2 x 3 = 6 records, m+n columns) R 1 r 1 R 1 r 2 R 1 r 3 R 2 r 1 R 2 r 2 R 2 r 3 Nov 2001 cylau Fox. Pro Chapter 11 12
Example z TEAMA (name, house, skill, sex) Chan Faith beginner Lee Hope advanced Cheung Love beginner Wong Wisdom advanced f m m f z TEAMB (name, house, skill, sex) Au Faith advanced Chow Hope beginner Wu Love advanced Ma Wisdom beginner m f f m Nov 2001 cylau Fox. Pro Chapter 11 13
Result of Cross-join SELECT * from teama, teamb Name_a Name_b Other fields Chan Lee Lee Cheung … Wong Au Chow Wu Ma Au … Ma . . . Nov 2001 cylau 4 x 4 = 16 records 4 + 4 = 8 columns . . . Fox. Pro Chapter 11 14
INNER JOIN (EQUI-JOIN) z Choosing partners from the same house SELECT * FROM teama; && Method 1 INNER JOIN teamb; ON teama. house = teamb. house SELECT FROM teama, teamb; WHERE teama. house = teamb. house name_a Chan Lee Cheung Wong Nov 2001 cylau house_a Faith Hope Love Wisdom name_b(other Au Chow Wu Ma Fox. Pro Chapter 11 && Method 2 fields) … … …. . . 15
INNER JOIN with ‘AS’ z Pairing member with the same skill SELECT a. name, a. skill, b. name FROM teama AS a; INNER JOIN teamb AS b ON a. skill=b. skill name_a Wong Lee Cheung Chan Nov 2001 cylau skill advanced beginner name_b Au Au Chow Wu Wu Ma Ma Fox. Pro Chapter 11 16
JOIN and WHERE SELECT a. name, a. house, b. name; FROM teama AS a; INNER JOIN teamb AS b; ON a. house = b. house; WHERE a. house IN (‘Faith’, ‘Wisdom’) z WHERE is checked first before JOIN, though JOIN appears before WHERE name_a Chan Wong Nov 2001 cylau house Faith Wisdom name_b Au Ma Fox. Pro Chapter 11 17
INTERSECT We don’t have (in Fox. Pro) : SELECT * FROM set. A; INTERSECT; SELECT * FROM set. B z. Use other SQL commands to simulate the results Nov 2001 cylau Fox. Pro Chapter 11 18
Example: Clubs in 2 Schools z Our. Club (club. ID, name) LF Logic Fun MS Maths and Sci SP Speech LM Locomotive z Other. Club (club. ID, name) MS Maths and Sci SP Speech FB Football Nov 2001 cylau && clubs in our school && clubs in other school Fox. Pro Chapter 11 19
INTERSECT (2) SELECT c. club. ID, c. name; FROM ourclub AS c; INNER JOIN otherclub AS o; ON c. name = o. name z If set. A and set. B have exactly the same structure, this can give their intersection. club. ID name MS SP Maths and Sci Speech Nov 2001 cylau Fox. Pro Chapter 11 20
INNER JOIN (Non-equi-join) z. The join condition is other than equality. z>, <, <>, >=, <= SELECT a. name home, b. name visitor; FROM teama a; INNER JOIN teamb b; && note: ‘AS’ omitted ON a. skill<> b. skill and a. sex>b. sex home visitor Lee Chow Cheung Wu Nov 2001 cylau Fox. Pro Chapter 11 21
Joining more than two tables SELECT a. name, b. name, h. colour FROM teama a; INNER JOIN teamb b ON a. house=b. house; INNER JOIN hs_colour h ON a. house=h. house What is the result if TABLE hs_colour is house Faith Hope Love Wisdom Nov 2001 cylau colour red orange green blue Name_a Chan Lee Cheung Wong Fox. Pro Chapter 11 Name_b Au Chow Wu Ma colour red orange green blue 22
Joining to oneself Table ISA(member ada bob girl boy Nov 2001 cylau class) girl boy human Fox. Pro Chapter 11 23
Joining to oneself (2) SELECT *; FROM isa AS a; INNER JOIN isa AS b; ON a. member = b. member Member_a class_a member_b ada girl ada bob boy bob girl human girl boy human boy Nov 2001 cylau Fox. Pro Chapter 11 class_b girl boy human 24
Joining to oneself (3) SELECT a. member, “ is a “, b. class; FROM isa AS a; INNER JOIN isa AS b; ON a. class = b. member Member ada bob girl boy class ‘is a’ girl boy human member ada bob girl boy class girl boy human Result: ada is a human bob is a human Nov 2001 cylau Fox. Pro Chapter 11 25
Outer Joins z. Like an inner join, outer join is made with a join condition. z. However, an outer join would extract all records that match plus some which do not match the join condition. Nov 2001 cylau Fox. Pro Chapter 11 26
Example S 7(name, club) Chan Lee Cheung Wong Ho Music Drama English CYC && Ho does not join any club CLUBINFO(club, teacher) Music Au Drama Chow English Wu CYC Ma Locomotive Mak Nov 2001 cylau && No one takes Locomotive Fox. Pro Chapter 11 27
Left Outer Join SELECT s 7. name, s 7. club, c. teacher FROM s 7; LEFT OUTER JOIN clubinfo as c; ON s 7. club = c. club name club teacher Chan Music Au Lee Drama Chow Cheung English Wu Wong CYC Ma Ho. NULL. && not matched z ALL records on the LEFT are shown (matched or unmatched Nov 2001 cylau Fox. Pro Chapter 11 28
Right Outer Join SELECT s 7. name, s 7. club, c. teacher FROM s 7; RIGHT OUTER JOIN clubinfo as c; ON s 7. club = c. club name club teacher Chan Music Au Lee Drama Chow Cheung English Wu Wong CYC Ma. NULL. Mak && not matched z ALL records on the RIGHT are shown (matched or unmatched Nov 2001 cylau Fox. Pro Chapter 11 29
Full Outer Join SELECT s 7. name, s 7. club, c. teacher FROM s 7; FULL OUTER JOIN clubinfo as c; ON s 7. club = c. club name Chan Lee Cheung Wong Ho. NULL. Nov 2001 cylau club Music Drama English CYC. NULL. teacher Au Chow Wu Ma. NULL. Mak Fox. Pro Chapter 11 ALL unmatched records on both sides are shown 30
Скачать презентацию Chapter 11 Joining Tables Nov 2001 cylau Fox
97b8f75403e3c43998551c4630f2c3c3.ppt