1 / 30

Chapter 11

Chapter 11. Joining Tables. Union, Intersection and Minus in Set. Set A = {1, 2, 3} Set B = {1, 3, 5, 7} Set C = {2, 4} A Union B = {1, 2, 3, 5, 7} && 1, 3 once A Intersection B = {1, 3} A Minus B = {2} B Minus A = {5, 7} A Union B Union C = {1, 2, 3, 4, 5, 7}. UNION.

page
Download Presentation

Chapter 11

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 11 Joining Tables FoxPro Chapter 11

  2. Union, Intersection and Minus in Set Set A = {1, 2, 3} Set B = {1, 3, 5, 7} Set C = {2, 4} • A Union B = {1, 2, 3, 5, 7} && 1, 3 once • A Intersection B = {1, 3} • A Minus B = {2} • B Minus A = {5, 7} • A Union B Union C = {1, 2, 3, 4, 5, 7} FoxPro Chapter 11

  3. UNION SELECT * FROM setA; UNION; SELECT * FROM setB • Similar to APPEND FROM in FoxPro • Original files (setA and setB) not affected • records present both in setA and setB will appear only once. • setA and setB must have exactly the same structure FoxPro Chapter 11

  4. Example • S7(name, club) Chan Music Lee Drama Cheung English Wong CYC Ho • S6(name, club) Wong CYC Kong Locomotive FoxPro Chapter 11

  5. UNION (2) SELECT * FROM s7; UNION; SELECT * FROM s6 • If the two name fields are of the same type and width, this will succeed. • 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. name1 <> name2 --> name1) FoxPro Chapter 11

  6. Result of Union Chan Music Cheung English Ho Kong Locomotive Lee Drama Wong CYC • “Wong CYC” appears once • Sorted by name (the 1st field) FoxPro Chapter 11

  7. UNION (3) SELECT * FROM setA; && record X, Y, Z UNION; SELECT * FROM setB; && record W, X UNION; SELECT * FROM setC && record Z, K • result : record X, Y, Z, W, K FoxPro Chapter 11

  8. UNION ALL SELECT * FROM setA; UNION ALL; SELECT * FROM setB; • records present both in setA and setB will appear twice. • {1, 2, 3} Union {1, 3, 5} = {1,2,3,1,3,5} FoxPro Chapter 11

  9. Example Wong CYC Kong Locomotive Chan Music Lee Drama Cheung English Wong CYC Ho SELECT * FROM s6; UNION ALL; SELECT * FROM s7; • “Wong CYC” appears twice • Result obtained by placing s6 on top of s7 • NOT Sorted FoxPro Chapter 11

  10. JOIN vs UNION • UNION pools records from various tables with same structure together to make new (virtual) table. • Adding rows of two tables of the same structure to form a new one • JOIN combines fields from various tables to make new (virtual) table. • Adding columns of two tables to form a new one FoxPro Chapter 11

  11. Cross-join (Natural join) Table1 (2 records, m columns) R1 R2 Table2 (3 records, n columns) r1 r2 r3 FoxPro Chapter 11

  12. Cross-join (Natural join) (2) SELECT * FROM table1, table2 • will give (2 x 3 = 6 records, m+n columns) R1 r1 R1 r2 R1 r3 R2 r1 R2 r2 R2 r3 FoxPro Chapter 11

  13. Example • TEAMA (name, house, skill, sex) Chan Faith beginner f Lee Hope advanced m Cheung Love beginner m Wong Wisdom advanced f • TEAMB (name, house, skill, sex) Au Faith advanced m Chow Hope beginner f Wu Love advanced f Ma Wisdom beginner m FoxPro Chapter 11

  14. Result of Cross-join SELECT * from teama, teamb Name_a Name_b Other fields Chan Au ... Chan Chow Chan Wu Chan Ma Lee Au Lee Chow Lee Wu Lee Ma Cheung Au … … Wong Ma ... 4 x 4 = 16 records 4 + 4 = 8 columns FoxPro Chapter 11

  15. INNER JOIN (EQUI-JOIN) • Choosing partners from the same house SELECT * FROM teama; && Method 1 INNER JOIN teamb; ON teama.house = teamb.house SELECT FROM teama, teamb; && Method 2 WHERE teama.house = teamb.house name_a house_a name_b (other fields) Chan Faith Au … Lee Hope Chow … Cheung Love Wu … Wong Wisdom Ma ... FoxPro Chapter 11

  16. INNER JOIN with ‘AS’ • 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 skill name_b Wong advanced Au Lee advanced Au Cheung beginner Chow Chan beginner Chow Wong advanced Wu Lee advanced Wu Cheung beginner Ma Chan beginner Ma FoxPro Chapter 11

  17. 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’) • WHERE is checked first before JOIN, though JOIN appears before WHERE name_a house name_b Chan Faith Au Wong Wisdom Ma FoxPro Chapter 11

  18. INTERSECT We don’t have (in FoxPro) : SELECT * FROM setA; INTERSECT; SELECT * FROM setB • Use other SQL commands to simulate the results FoxPro Chapter 11

  19. Example: Clubs in 2 Schools • OurClub (clubID, name) && clubs in our school LF Logic Fun MS Maths and Sci SP Speech LM Locomotive • OtherClub (clubID, name) && clubs in other school MS Maths and Sci SP Speech FB Football FoxPro Chapter 11

  20. INTERSECT (2) SELECT c.clubID, c.name; FROM ourclub AS c; INNER JOIN otherclub AS o; ON c.name = o.name • If setA and setB have exactly the same structure, this can give their intersection. clubID name MS Maths and Sci SP Speech FoxPro Chapter 11

  21. INNER JOIN (Non-equi-join) • The join condition is other than equality. • >, <, <>, >=, <= 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 FoxPro Chapter 11

  22. 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 colour Faith red Hope orange Love green Wisdom blue • Name_a Name_b colour • Chan Au red • Lee Chow orange • Cheung Wu green • Wong Ma blue FoxPro Chapter 11

  23. Joining to oneself Table ISA(member class) ada girl bob boy girl human boy human FoxPro Chapter 11

  24. 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 class_b ada girl ada girl bob boy bob boy girl human girl human boy human boy human FoxPro Chapter 11

  25. 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 class ‘is a’ member class ada girl ada girl bob boy bob boy girl human girl human boy human boy human Result: ada is a human bob is a human FoxPro Chapter 11

  26. Outer Joins • Like an inner join, outer join is made with a join condition. • However, an outer join would extract all records that match plus some which do not match the join condition. FoxPro Chapter 11

  27. Example S7(name, club) Chan Music Lee Drama Cheung English Wong CYC Ho && Ho does not join any club CLUBINFO(club, teacher) Music Au Drama Chow English Wu CYC Ma Locomotive Mak && No one takes Locomotive FoxPro Chapter 11

  28. Left Outer Join SELECT s7.name, s7.club, c.teacher FROM s7; LEFT OUTER JOIN clubinfo as c; ON s7.club = c.club name club teacher Chan Music Au Lee Drama Chow Cheung English Wu Wong CYC Ma Ho .NULL. && not matched • ALL records on the LEFT are shown (matched or unmatched FoxPro Chapter 11

  29. Right Outer Join SELECT s7.name, s7.club, c.teacher FROM s7; RIGHT OUTER JOIN clubinfo as c; ON s7.club = c.club name club teacher Chan Music Au Lee Drama Chow Cheung English Wu Wong CYC Ma .NULL. .NULL. Mak && not matched • ALL records on the RIGHT are shown (matched or unmatched FoxPro Chapter 11

  30. Full Outer Join SELECT s7.name, s7.club, c.teacher FROM s7; FULL OUTER JOIN clubinfo as c; ON s7.club = c.club name club teacher Chan Music Au Lee Drama Chow Cheung English Wu Wong CYC Ma Ho .NULL. .NULL. .NULL. Mak ALL unmatched records on both sides are shown FoxPro Chapter 11

More Related