Join

EksempelDB: Kursregister

person

personid navn
1 Per Olsen
2 Anne Evensen
3 Hans Jensen
4 Inger Karlsen
5 Hans Nilsen
6 Janne Elise Julsen

kurs

kursid navn
1 Matematikk
2 Datakommunikasjon
3 Databaser
4 Matematikk II
5 Programmering for web
6 Engelsk fonetikk

elevKurs

kursid(FK->kurs) blokk elevid(FK->person)
1 1-02 1
1 1-02 2
1 1-03 4
2 2-02 2
2 4-03 3
2 4-03 4
3 6-03 3
4 5-03 4
5 2-03 2
5 2-03 4

laererKurs

kursid(FK->kurs) blokk laererid(FK->person)
1 1-02 5
1 1-03 1
2 2-02 6
2 4-03 6
3 6-03 1
4 5-03 5
5 2-03 6
5 6-04 NULL

Inner-join

Equi-join

Equi betyr 'lik', d.v.s at vi foretar en join på likhet.
En equi-join er en type inner-join. 

SELECT DISTINCT navn 
FROM person p, elevKurs ek
WHERE p.personid = ek.elevid;
= 
SELECT DISTINCT navn 
FROM person p 
INNER JOIN elevKurs ek
  ON p.personid = ek.elevid;
NB: DISTINCT brukes for at hvert navn kun skal listes opp en gang
=> 
navn                 
-------------------- 
Anne Evensen
Hans Jensen
Inger Karlsen
Per Olsen

(4 row(s) affected)

Theta-join

En theta-join er en type inner-join.
I en theta-join tester man på ulikhet eller mengde/størrelse, og bruker da operatorene >, <, >=, <=, != <>. 

SELECT personid, elevid, navn 
FROM person p, elevKurs ek
WHERE p.personid > ek.elevid
AND kursid = 1;
= 
SELECT personid, elevid, navn 
FROM person p INNER JOIN elevKurs ek
ON p.personid > ek.elevid
WHERE kursid = 1;
=> 
personid    elevid      navn                 
----------- ----------- -------------------- 
2           1           Anne Evensen
3           1           Hans Jensen
4           1           Inger Karlsen
5           1           Hans Nilsen
6           1           Janne Elise Julsen
3           2           Hans Jensen
4           2           Inger Karlsen
5           2           Hans Nilsen
6           2           Janne Elise Julsen
5           4           Hans Nilsen
6           4           Janne Elise Julsen

(11 row(s) affected)

Natural join

En natural join er egentlig bare en vanlig equi-join.

SELECT personid, elevid, navn 
FROM person p, elevKurs ek
WHERE p.personid = ek.elevid;
= 
SELECT personid, elevid, navn 
FROM person p JOIN elevKurs ek
ON p.personid = ek.elevid;
=> 
personid    elevid      navn                 
----------- ----------- -------------------- 
1           1           Per Olsen
2           2           Anne Evensen
4           4           Inger Karlsen
2           2           Anne Evensen
3           3           Hans Jensen
4           4           Inger Karlsen
3           3           Hans Jensen
4           4           Inger Karlsen
2           2           Anne Evensen
4           4           Inger Karlsen

(10 row(s) affected)

Cross-join

En cross-join som ikke har noen WHERE kalles også kartesisk produkt.
En cross-join som har en WHERE oppfører seg som en inner-join. 

SELECT kursid, elevid, navn
FROM person p, elevKurs ek;
= 
SELECT kursid, elevid, navn
FROM person CROSS JOIN elevKurs;
=> 
kursid      elevid      navn                 
----------- ----------- -------------------- 
1           1           Per Olsen
1           2           Per Olsen
1           4           Per Olsen
2           2           Per Olsen
2           3           Per Olsen
2           4           Per Olsen
3           3           Per Olsen
4           4           Per Olsen
5           2           Per Olsen
5           4           Per Olsen
1           1           Anne Evensen
1           2           Anne Evensen
1           4           Anne Evensen
2           2           Anne Evensen
2           3           Anne Evensen
2           4           Anne Evensen
3           3           Anne Evensen
4           4           Anne Evensen
5           2           Anne Evensen
5           4           Anne Evensen
1           1           Hans Jensen
1           2           Hans Jensen
1           4           Hans Jensen
2           2           Hans Jensen
2           3           Hans Jensen
2           4           Hans Jensen
3           3           Hans Jensen
4           4           Hans Jensen
5           2           Hans Jensen
5           4           Hans Jensen
1           1           Inger Karlsen
1           2           Inger Karlsen
1           4           Inger Karlsen
2           2           Inger Karlsen
2           3           Inger Karlsen
2           4           Inger Karlsen
3           3           Inger Karlsen
4           4           Inger Karlsen
5           2           Inger Karlsen
5           4           Inger Karlsen
1           1           Hans Nilsen
1           2           Hans Nilsen
1           4           Hans Nilsen
2           2           Hans Nilsen
2           3           Hans Nilsen
2           4           Hans Nilsen
3           3           Hans Nilsen
4           4           Hans Nilsen
5           2           Hans Nilsen
5           4           Hans Nilsen
1           1           Janne Elise Julsen
1           2           Janne Elise Julsen
1           4           Janne Elise Julsen
2           2           Janne Elise Julsen
2           3           Janne Elise Julsen
2           4           Janne Elise Julsen
3           3           Janne Elise Julsen
4           4           Janne Elise Julsen
5           2           Janne Elise Julsen
5           4           Janne Elise Julsen

(60 row(s) affected)

Self-join

Self-join vil si at man lager en join på en og samme tabell

SELECT DISTINCT e1.*
FROM elevKurs e1, elevkurs e2
WHERE e1.kursid = e2.kursid
AND e1.blokk = e2.blokk
AND e1.elevid != e2.elevid;
=> 
kursid      blokk elevid      
----------- ----- ----------- 
1           1-02  1
1           1-02  2
2           4-03  3
2           4-03  4
5           2-03  2
5           2-03  4

(6 row(s) affected)

Outer-join

Left outer-join

SELECT kursid, blokk, p.*
FROM laererKurs l, person p
WHERE l.laererid *= p.personid;
= 
SELECT kursid, blokk, p.*
FROM laererKurs l
LEFT OUTER JOIN person p
  ON p.personid = l.laererid;
=> 
kursid      blokk personid    navn                 
----------- ----- ----------- -------------------- 
1           1-02  5           Hans Nilsen
1           1-03  1           Per Olsen
2           1-02  6           Janne Elise Julsen
2           4-03  6           Janne Elise Julsen
3           6-03  1           Per Olsen
4           5-03  5           Hans Nilsen
5           2-03  6           Janne Elise Julsen
5           6-04  NULL        NULL

(8 row(s) affected)

Right outer-join

SELECT kursid, blokk, p.*
FROM laererKurs l, person p
WHERE l.laererid =* p.personid;
= 
SELECT kursid, blokk, p.*
FROM laererKurs l
RIGHT OUTER JOIN person p
  ON p.personid = l.laererid;
=> 
kursid      blokk personid    navn                 
----------- ----- ----------- -------------------- 
1           1-03  1           Per Olsen
3           6-03  1           Per Olsen
NULL        NULL  2           Anne Evensen
NULL        NULL  3           Hans Jensen
NULL        NULL  4           Inger Karlsen
1           1-02  5           Hans Nilsen
4           5-03  5           Hans Nilsen
2           1-02  6           Janne Elise Julsen
2           4-03  6           Janne Elise Julsen
5           2-03  6           Janne Elise Julsen

(10 row(s) affected)

Full outer-join

(SELECT kursid, blokk, p.*
FROM laererKurs l, person p
WHERE l.laererid *= p.personid)
UNION
(SELECT  kursid, blokk, p.*
FROM laererKurs l, person p
WHERE l.laererid =* p.personid)
ORDER BY personid, kursid;
= 
SELECT kursid, blokk, p.*
FROM laererKurs l
FULL OUTER JOIN person p
  ON p.personid = l.laererid;
=> 
kursid      blokk personid    navn                 
----------- ----- ----------- -------------------- 
5           6-04  NULL        NULL
1           1-03  1           Per Olsen
3           6-03  1           Per Olsen
NULL        NULL  2           Anne Evensen
NULL        NULL  3           Hans Jensen
NULL        NULL  4           Inger Karlsen
4           5-03  5           Hans Nilsen
1           1-02  5           Hans Nilsen
5           2-03  6           Janne Elise Julsen
2           1-02  6           Janne Elise Julsen
2           4-03  6           Janne Elise Julsen

(11 row(s) affected)

Fil med tabeller og data

Elin K. Ajer Andreassen, 2004