1 / 44

SQL 3 : JOIN

SQL 3 : JOIN. Het selecteren van data uit gekoppelde tabellen. Vorige week. Tabel: rijen en kolommen gevuld met ‘data’ Datatype: typologie op basis van mogelijk operaties Selecteren uit een enkele tabel ( SELECT ). Basis datatype. Type informatie in een veld

dandre
Download Presentation

SQL 3 : JOIN

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. SQL 3 : JOIN Het selecteren van data uit gekoppelde tabellen Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  2. Vorige week • Tabel: rijen en kolommen gevuld met ‘data’ • Datatype: typologie op basis van mogelijk operaties • Selecteren uit een enkele tabel ( SELECT ) Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  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 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  4. Syntax van een eenvoudige SELECT • SELECTID,naam, prijsFROMproductWHEREcategorie = ‘boek’ORDER BYprijs • ID naam prijs8 groentensoep 2,157 appelsap 2,959 blik tonijn 3,15 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  5. Berekeningen in SELECT (numeriek) • Selecteer de naam, prijs en prijs_plus_btw • SELECT ID, naam, prijs, prijs + prijs* btw_tarief/100AS prijs_met_btwFROM product • ID Naam prijs prijs_met_btw1 fiets 100 1202 brood 1 1,05 5 boek 30 36 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  6. Statistieken (aggregaties) • SELECTCOUNT(*) 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 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  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 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  8. SELECT DISTINCT • SELECT DISTINCT categorieFROM product • categorievervoervoedselboek Dit is hetzelfde als: • SELECT categorieFROM productGROUP BY categorie Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  9. En.. • Hoe ging het huiswerk? Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  10. vandaag • Gegevens selecteren uit meerdere ‘gekoppelde’ tabellen. • Eigenlijk heel simpel, maar je moet het effe door hebben. Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  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 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  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 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  13. Entiteit Een op zichzelf staand ‘iets’ dat bestaat en kenmerken heeft. Bijvoorbeeld: • Product • Producent • Schrijver • Boek • Leerling • Vak • Bestelling • Opleiding Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  14. Relatie tussen product en producent Foreign Key (FK) Primairy Key (PK) Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  15. Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  16. Relatie tussen product en producent Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  17. Gekoppelde tabellen Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  18. Verschillende soorten relaties naar kwantiteit • Many-to-one ( product -geproduceerd door> producent ) • One-to-many ( klant -doet> bestelling ) • One-to-one ( man –isgetrouwdmet> vrouw ) • Many-to-many ( bestelling –besteldproduct> product ) Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  19. JOIN:SELECT 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 unilever4 kip 6 unilever5 step 4 joop’s fiets… … … … Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  20. JOIN:SELECT 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 step 4 … … … Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  21. JOIN:SELECT 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 unilever4 kip 6 unilever5 step 4 joop’s fiets… … … … Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  22. JOIN:SELECT 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.IDJOIN btwtarief ON product.btwtariefID = btwtarief.ID • ID naam producentID producent.Naam percentage3 tonijn 6 unilever 5 4 kip 6 unilever 55 step 4 joop’s fiets 20… … … … Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  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. Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  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 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  25. LEFT JOIN Alle rijen uit de linker tabel, plus de gekoppelde rijen uit de rechter tabel • SELECT product.*, producent.naam AS producentNaamFROM productLEFT JOIN producent ON product.producentID = producent.ID • ID naam … producentID producentNaam3 tonijn 6 unilever5 step 4 joop’s fiets10 Karel en… NULL NULL Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  26. RIGHT JOIN Alle rijen uit de rechter tabel, plus de gekoppelde rijen uit de linker tabel • SELECT product.*, producent.naam AS producentNaamFROM productRIGHT JOIN producent ON product.producentID = producent.ID • ID naam … producentID producentNaam3 tonijn 6 unilever5 step 4 joop’s fietsNULL NULL 9 Amalia Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  27. INNER JOIN Alleen rijen die aan elkaar gekoppeld zijn. • SELECT product.*, producent.naam AS producentNaamFROM productINNER JOIN producent ON product.producentID = producent.ID • ID naam … producentID producentNaam3 tonijn 6 unilever5 step 4 joop’s fiets Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  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 joop’s fiets Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  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 productFULL OUTER JOIN producent ON product.producentID = producent.ID • ID naam … producentID producentNaam3 tonijn 6 unilever5 step 4 joop’s fiets • NULL NULL 9 Amalia • 10 Karel en… NULL NULL Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  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 productLEFT JOIN producent ON product.producentID = producent.ID SELECTprimaire_tabel.* gerelateerdetabel.* FROMprimaire_tabelLEFT JOINgerelateerde_tabelONprimaire_tabel.FK = gerelateerde_tabel.PK Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  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 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  32. Meerdere gerelateerde tabellen • SELECT product.*, producent.naam, categorie.naam, btwtarief.percentageFROM productLEFT JOIN producent ON product.producentID = producent.IDLEFT JOIN categorie ON product.categorieID = categorie.IDLEFT JOIN btwtarief ON product.btwtariefID = btwtarief.ID Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  33. Verschillende soorten relaties naar kwantiteit • Many-to-one ( product -geproduceerd door> producent ) • One-to-many ( klant -doet> bestelling ) • Many-to-many ( bestelling –besteldproduct> product ) • One-to-one ( man –isgetrouwdmet> vrouw ) Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  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 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  35. Many-to-manybestelling <-> product • Een aparte tabel met bestellingID-productID paren Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  36. Selecteren via relaties Selecteer alle klanten die een bepaald product hebben besteld. Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  37. Stap 1: SELECT en JOIN de tabellen: SELECT klant.* FROM klant JOIN bestellingJOIN bestelling_productJOIN product WHERE product.ID = 21 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  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.klantIDJOIN bestelling_product ON bestelling.ID=bestelling_product.bestellingIDJOIN product ON bestelling_product.productID = product.ID WHERE product.ID = 21 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  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 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  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-2003 Graaf Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  41. Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  42. V2-projecten database Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  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’ ) Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  44. oefeningen • Doe de oefeningen 2 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

More Related