Docstoc

Chapter 11

Document Sample
Chapter 11 Powered By Docstoc
					Chapter 11



                 Joining Tables



Nov 2001 cylau       FoxPro Chapter 11   1
 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}
Nov 2001 cylau   FoxPro Chapter 11          2
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
Nov 2001 cylau      FoxPro Chapter 11          3
Example
 S7(name, club)
     Chan        Music
     Lee         Drama
     Cheung      English
     Wong        CYC
     Ho
 S6(name, club)
     Wong        CYC
     Kong        Locomotive
Nov 2001 cylau     FoxPro Chapter 11   4
 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)


 Nov 2001 cylau         FoxPro Chapter 11              5
Result of Union
          Chan     Music
          Cheung   English
          Ho
          Kong     Locomotive
          Lee      Drama
          Wong     CYC

“Wong CYC” appears once
Sorted by name (the 1st field)
Nov 2001 cylau        FoxPro Chapter 11   6
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
Nov 2001 cylau    FoxPro Chapter 11                 7
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}

Nov 2001 cylau    FoxPro Chapter 11           8
Example
                                      Wong      CYC
SELECT * FROM s6;                     Kong      Locomotive
UNION ALL;                            Chan      Music
                                      Lee       Drama
SELECT * FROM s7;                     Cheung    English
                                      Wong      CYC
                                      Ho



  “Wong CYC” appears twice
  Result obtained by placing s6 on top of s7
  NOT Sorted
Nov 2001 cylau         FoxPro Chapter 11                     9
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
Nov 2001 cylau       FoxPro Chapter 11             10
Cross-join (Natural join)

Table1 (2 records, m columns)
 R1
 R2
Table2 (3 records, n columns)
 r1
 r2
 r3
Nov 2001 cylau   FoxPro Chapter 11   11
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
 Nov 2001 cylau   FoxPro Chapter 11       12
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
Nov 2001 cylau        FoxPro Chapter 11         13
Result of Cross-join
 SELECT * from teama, teamb
           Name_a   Name_b           Other fields
           Chan     Au               ...
           Chan     Chow
           Chan     Wu
           Chan     Ma                    4 x 4 = 16 records
           Lee      Au
           Lee      Chow                  4 + 4 = 8 columns
           Lee      Wu
           Lee      Ma
           Cheung   Au
           …        …
           Wong     Ma               ...
Nov 2001 cylau        FoxPro Chapter 11                        14
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              ...
Nov 2001 cylau             FoxPro Chapter 11                 15
 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
 Nov 2001 cylau        FoxPro 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‟)


WHERE is checked first before JOIN, though JOIN
 appears before WHERE
    name_a       house        name_b
    Chan         Faith        Au
    Wong         Wisdom       Ma
Nov 2001 cylau            FoxPro Chapter 11   17
INTERSECT

We don‟t have (in FoxPro) :
     SELECT * FROM setA;
     INTERSECT;
     SELECT * FROM setB


Use other SQL commands to simulate the
 results

Nov 2001 cylau      FoxPro Chapter 11   18
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

Nov 2001 cylau        FoxPro Chapter 11                      19
  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

Nov 2001 cylau         FoxPro Chapter 11           20
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

Nov 2001 cylau           FoxPro 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        colour
                               Name_a         Name_b   colour
    Faith        red           Chan           Au       red
    Hope         orange        Lee            Chow     orange
    Love         green         Cheung         Wu       green
    Wisdom       blue          Wong           Ma       blue




Nov 2001 cylau            FoxPro Chapter 11               22
Joining to oneself

Table ISA(member     class)
         ada         girl
         bob         boy
         girl        human
         boy         human



Nov 2001 cylau   FoxPro 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        class_b
ada        girl        ada             girl
bob        boy         bob             boy
girl       human       girl            human
boy        human       boy             human
Nov 2001 cylau     FoxPro Chapter 11             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      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
Nov 2001 cylau             FoxPro Chapter 11                    25
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.




Nov 2001 cylau    FoxPro Chapter 11        26
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
Nov 2001 cylau            FoxPro Chapter 11                        27
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
Nov 2001 cylau        FoxPro Chapter 11                28
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
Nov 2001 cylau       FoxPro Chapter 11                29
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             ALL unmatched
                                               records on both
     Lee         Drama          Chow           sides are shown
     Cheung      English        Wu
     Wong        CYC            Ma
     Ho                         .NULL.
     .NULL.      .NULL.         Mak
Nov 2001 cylau             FoxPro Chapter 11                     30

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:2
posted:10/1/2011
language:English
pages:30