SQL 3 : JOIN

1
SQL 3 JOIN

• Het selecteren van data uit gekoppelde tabellen

2
Vorige week

• Tabel rijen en kolommen gevuld met data
• Datatype typologie op basis van mogelijk
  operaties
• Selecteren uit een enkele tabel ( SELECT )

3
Basis datatype

• Type informatie in een veld
• Bepaalt het soort operaties dat je er op kan
  uitvoeren.
• basis datatypen die in iedere database of
  programmeertaal op één of andere manier worden
  ondersteund
• Numeriek
• Tekst
• Boolean
• Binair
• Datum/tijd
• Lange tekst

4
Syntax van een eenvoudige SELECT

• SELECT ID, naam, prijs FROM product WHERE
  categorie boek ORDER BY prijs
• ID naam prijs8 groentensoep 2,157 appelsa
  p 2,959 blik tonijn 3,15

5
Berekeningen in SELECT (numeriek)

• Selecteer de naam, prijs en prijs_plus_btw
• SELECT ID, naam, prijs, prijs prijs
  btw_tarief/100 AS prijs_met_btw FROM product
• ID Naam prijs prijs_met_btw1 fiets 100 1202
  brood 1 1,05 5 boek 30 36

6
Statistieken (aggregaties)

• SELECT COUNT() AS aantal, AVG(prijs) AS
  midPrijs, MAX(prijs) AS maxPrijs, MIN(prijs)
  AS minPrijsFROM product
• Aantal midPrijs maxPrijs minPrijs 9
  873,10 8000 2.05

7
Statistieken (aggregaties) per groep

• SELECT categorie, COUNT() AS aantal,
  AVG(prijs) AS midPrijs, MAX(prijs) AS
  maxPrijs, MIN(prijs) AS minPrijsFROM
  productGROUP BY categorie
• Cat Aantal midPrijs maxPrijs minPrijsvervoer
  6 1188 8000 2.05
• boek 3 20,70 40 10

8
SELECT DISTINCT

• SELECT DISTINCT categorie FROM product
• categorievervoervoedselboek
• Dit is hetzelfde als
• SELECT categorie FROM product GROUP BY categorie

9
En..

• Hoe ging het huiswerk?

10
vandaag

• Gegevens selecteren uit meerdere gekoppelde
  tabellen.
• Eigenlijk heel simpel, maar je moet het effe
  door hebben.

11
Vandaag te leren

• Gerelateerde tabellen
• Primaire Key (PK) , Foreign Key (FK)
• JOIN ( rijen uit verschillende tabellen
  samenvoegen )
• LEFT JOIN
• RIGHT JOIN
• INNER JOIN
• Soorten relaties
• Many-to-one
• One-to-many
• One-to-one
• Many-to-many

12
Relaties

• Een boek is gescheven door een auteur
• Een student volgt een opleiding
• De klant doet een bestelling
• Een product gemaakt door een producent
• Meer voorbeelden?
• Voor iedere entiteit een tabel
• http//nl.wikipedia.org/wiki/Entiteit

13
Entiteit

• Een op zichzelf staand iets dat bestaat en
  kenmerken heeft.
• Bijvoorbeeld
• Product
• Producent
• Schrijver
• Boek
• Leerling
• Vak
• Bestelling
• Opleiding

14
Relatie tussen product en producent
Foreign Key (FK) Primairy Key (PK)
15
(No Transcript)
16
Relatie tussen product en producent
17
Gekoppelde tabellen
18
Verschillende soorten relaties naar kwantiteit

• Many-to-one ( product -geproduceerd doorgt
  producent )
• One-to-many ( klant -doetgt bestelling )
• One-to-one ( man isgetrouwdmetgt vrouw )
• Many-to-many ( bestelling besteldproductgt
  product )

19
JOINSELECT uit twee gekoppelde tabellen

• Selecteer alle producten en koppel daar aan de
  naam van de producent
• SELECT product., producent.naam FROM product,
  producentWHERE product.producentID
  producent.ID
• ID naam producentID producent.Naam3 tonijn 6 u
  nilever4 kip 6 unilever5 step 4 joops
  fiets

20
JOINSELECT uit twee gekoppelde tabellen

• Selecteer alle producten en koppel daar aan de
  naam van de producent
• SELECT product.FROM product
• ID naam producentID 3 tonijn 6 4 kip 6 5 s
  tep 4

21
JOINSELECT uit twee gekoppelde tabellen

• Selecteer alle producten en koppel daar aan de
  naam van de producent
• SELECT product., producent.naam FROM product
  JOIN producent ON product.producentID
  producent.ID
• ID naam producentID producent.Naam3 tonijn 6 u
  nilever4 kip 6 unilever5 step 4 joops
  fiets

22
JOINSELECT uit twee gekoppelde tabellen

• Selecteer alle producten en koppel daar aan de
  naam van de producent en het btw tarief
• SELECT product., producent.naam ,
  btwtarief.percentage FROM product JOIN
  producent ON product.producentID
  producent.ID JOIN btwtarief ON
  product.btwtariefID btwtarief.ID
• ID naam producentID producent.Naam
  percentage3 tonijn 6 unilever 5 4 kip 6 unil
  ever 55 step 4 joops fiets 20

23
Drie soorten koppelingen

• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• INNER JOIN
• Opdracht voor gevorderden
• Leg aan beginners uit wat het verschil is tussen
  deze soorten joins. Geef voorbeelden.

24
En verder...

• De sheets hierna gaan over de subtiele
  verschillen tussen de verschillende soorten JOINS
  ( INNER, LEFT, RIGHT ).
• Dat heeft pas betekenis als je de JOIN begrijpt
  en kan toepassen

25
LEFT JOIN

• Alle rijen uit de linker tabel, plus de
  gekoppelde rijen uit de rechter tabel
• SELECT product., producent.naam AS
  producentNaamFROM product LEFT JOIN producent
  ON product.producentID producent.ID
• ID naam producentID producentNaam3 tonijn 6 un
  ilever5 step 4 joops fiets10 Karel
  en NULL NULL

26
RIGHT JOIN

• Alle rijen uit de rechter tabel, plus de
  gekoppelde rijen uit de linker tabel
• SELECT product., producent.naam AS
  producentNaamFROM product RIGHT JOIN producent
  ON product.producentID producent.ID
• ID naam producentID producentNaam3 tonijn 6
  unilever5 step 4 joops fietsNULL NULL 9 Am
  alia

27
INNER JOIN

• Alleen rijen die aan elkaar gekoppeld zijn.
• SELECT product., producent.naam AS
  producentNaamFROM product INNER JOIN producent
  ON product.producentID producent.ID
• ID naam producentID producentNaam3 tonijn 6
  unilever5 step 4 joops fiets

28
INNER JOIN (alternatief)

• Een andere manier om een inner join te maken.
• SELECT product., producent.naam AS
  producentNaamFROM product, producentWHERE
  product.producentID producent.ID
• ID naam producentID producentNaam3 tonijn 6
  unilever5 step 4 joops fiets

29
FULL OUTER JOIN

• Alle rijen uit zowel de linker als de rechter
  tabel. Ook als er geen koppeling is.
• SELECT product., producent.naam AS
  producentNaamFROM product FULL OUTER JOIN
  producent ON product.producentID
  producent.ID
• ID naam producentID producentNaam3 tonijn 6
  unilever5 step 4 joops fiets
• NULL NULL 9 Amalia
• 10 Karel en NULL NULL

30
90 van de koppelingen LEFT JOIN

• Selecteer data uit een primaire tabel en koppel
  data uit gerelateerde tabellen
• Bijv alle data uit product en koppel de
  producentnaam.
• SELECT product., producent.naam,
  producent.infoFROM product LEFT JOIN producent
  ON product.producentID producent.ID
• SELECT primaire_tabel. gerelateerdetabel.
• FROM primaire_tabel LEFT JOIN
  gerelateerde_tabel ON primaire_tabel.FK
  gerelateerde_tabel.PK

31
Vele wegen naar rome

• SELECT product. , producent.naam AS
  producentNaamFROM product LEFT JOIN producent
  ON product.producentID producent.ID
• SELECT product. , producent.naam AS
  producentNaamFROM producent RIGHT JOIN product
  ON product.producentID producent.ID

32
Meerdere gerelateerde tabellen

• SELECT product., producent.naam,
  categorie.naam, btwtarief.percentageFROM
  product LEFT JOIN producent ON
  product.producentID producent.ID LEFT JOIN
  categorie ON product.categorieID
  categorie.ID LEFT JOIN btwtarief ON
  product.btwtariefID btwtarief.ID

33
Verschillende soorten relaties naar kwantiteit

• Many-to-one ( product -geproduceerd doorgt
  producent )
• One-to-many ( klant -doetgt bestelling )
• Many-to-many ( bestelling besteldproductgt
  product )
• One-to-one ( man isgetrouwdmetgt vrouw )

34
One-to-one relaties

• Wanneer gebruik je een one-to-one?
• Natuurlijke 1 op 1 relaties
• Is_getrouwd_met
• Aparte tabel voor binaire of grootte data velden
• product.filmID film.ID
• SELECT product. , film.bindata FROM product
  LEFT JOIN film ON product.filmID film.ID

35
Many-to-manybestelling lt-gt product

• Een aparte tabel met bestellingID-productID
  paren

36
Selecteren via relaties

• Selecteer alle klanten die een bepaald product
  hebben besteld.

37
Stap 1 SELECT en JOIN de tabellen

• SELECT klant.
• FROM klant
• JOIN bestellingJOIN bestelling_productJOIN
  product
• WHERE product.ID 21

38
Stap 1 SELECT en JOIN de tabellen(en vergeet
niet de criteria voor deze relaties erbij te
vertellen)

• SELECT klant.
• FROM klant
• JOIN bestelling ON klant.ID bestelling.klantID
  JOIN bestelling_product ON bestelling.IDbestellin
  g_product.bestellingIDJOIN product ON
  bestelling_product.productID product.ID
• WHERE product.ID 21

39
Many-to-many

• Selecteer van een bestelling alle producten
• SELECT product.ID, product.naam, product.prijs,
• bestelling_product.aantal
• FROM bestelling_product INNER JOIN product
  ON bestelling_product.productID
  product.IDWHERE bestelling_product.bestellingID
  1
• Aantal ID naam prijs
• 30 1 Toveren 20
• 2 3 SQL 23

40
Many-to-manyvan product naar bestelling

• Selecteer van een product alle bestellingen
• SELECT bestelling_product.aantal,
  bestelling.datum, klant.naam
• FROM ( bestelling_product INNER JOIN bestelling
  ON bestelling_product.bestellingID
  bestelling.ID) INNER JOIN klant ON
  bestelling.klantID klant.IDWHERE
  bestelling_product.productID 1
• Aantal datum naam 30 1-9-2003 Bos 4 6-12-2
  003 Graaf

41
(No Transcript)
42
V2-projecten database
43
Vandaag geleerd

• Gerelateerde tabellen
• Primaire Key (PK) , Foreign Key (FK)
• JOIN ( tabellen samenvoegen )
• LEFT JOIN
• RIGHT JOIN
• INNER JOIN
• Soorten relaties
• Many-to-one
• One-to-many
• One-to-one
• Many-to-many ( gebruik een tussen tabel )

44
oefeningen

• Doe de oefeningen 2