SQL Basis

Hoofdstuk 12 - Joins

  • Gegevens zijn vaak verdeeld over meerdere tabellen als gevolg van normaliseren
  • Met behulp van een JOIN (samenvoegen) kunnen query's ook gegevens opvragen uit meerdere tabellen
  • Om de gegevens van een tabel uit te breiden met de gegevens uit een tweede tabel heb je een match nodig om ze met elkaar te kunnen verbinden
  • Deze match wordt meestal gevormd door een foreign key in de ene tabel die verwijst naar de primary key in de andere tabel
  • Maak gebruik van het datamodel om een goede join te kunnen schrijven

Inner Join

  • Een JOIN levert dus een uitbreiding vanuit de hoofdtabel met kolommen uit de JOIN-tabel
  • Er zijn verschillende soorten Joins; Inner Joins & Outer Joins (outer joins volgende hoofdstuk)
  • De Inner Join kan op twee manieren geschreven worden

-- Deze wordt ook wel equijoin genoemd / testen van equality
SELECT <kolom1>, <kolom2>
FROM   <tabel1>, <tabel2>
WHERE  <tabel1.sleutel1> = <tabel2.<sleutel2>	
of
SELECT <kolom1>, <kolom2>
FROM   <tabel1>
       INNER JOIN <tabel2> ON <tabel1.sleutel1> = <tabel2.sleutel2>	
  • De algemene standaard is methode 2; INNER JOIN wordt expliciet benoemd


Selecteer alle productnamen, productprijzen en de bijbehorende leveranciersnaam

-- Oracle / MySQL / SQL Server
SELECT Vendors.vend_name,
       Products.prod_name,
       Products.prod_price
FROM   Vendors
       INNER JOIN Products ON Vendors.vend_id = Products.vend_id;
of
-- Oracle / MySQL / SQL Server	
SELECT Vendors.vend_name,
       Products.prod_name,
       Products.prod_price
FROM   Products
       INNER JOIN Vendors ON Products.vend_id = Vendors.vend_id;
vend_nameprod_nameprod_price
Doll House Inc.Fish bean bag toy3.49
Doll House Inc.Bird bean bag toy3.49
Doll House Inc.Rabbit bean bag toy3.49
Bears R Us8 inch teddy bear5.99
Bears R Us12 inch teddy bear8.99
Bears R Us18 inch teddy bear11.99
Doll House Inc.Raggedy Ann4.99
Fun and GamesKing doll9.49
Fun and GamesQueen doll9.49
  • Zonder het koppelen van de primary key en foreign key krijg je een 'Cartesian Product'
  • Een Cartesian Product zijn 2 samengevoegde tabellen waarbij elke rij van tabel1 wordt gecombineerd met elke rij van tabel 2
  • Deze query geeft een Cartesian Product van 54 rijen
SELECT Vendors.vend_name,
       Products.prod_name,
       Products.prod_price
FROM   Products, Vendors;
  • 6 rijen uit Vendors X 9 rijen uit Products
  • Een ander woord voor Cartesian Product is ook wel 'Cross Joins'
  • Een Cartesian Product is nooit wat je wilt
  • SQL heeft geen beperkingen voor het aantal te koppelen tabellen
  • Het gebruikte DBMS heeft waarschijnlijk wel een beperking voor het aantal te koppelen tabellen
-- Oracle / MySQL / SQL Server
SELECT Customers.cust_name AS KlantNaam,
       Customers.cust_contact AS KlantContact,
       Products.prod_name AS ProductNaam,
       Vendors.vend_name AS Leverancier
FROM   Customers
       INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
       INNER JOIN OrderItems ON Orders.order_num = OrderItems.order_num
       INNER JOIN Products ON OrderItems.prod_id = Products.prod_id
       INNER JOIN Vendors ON Products.vend_id = Vendors.vend_id
WHERE  OrderItems.prod_id = 'RGAN01';

KlantNaamKlantContactProductNaamLeverancier
Fun4AllDenise L. StephensRaggedy AnnDoll House Inc.
The Toy StoreKim HowardRaggedy AnnDoll House Inc.

  1. Selecteer de landen van de leveranciers en de productnamen (9)


  1. Selecteer alle orderdatums en het totaal aantal bestelde producten (5)


  1. Selecteer de namen van de klanten die, per orderregel, minstens 100 stuks van een artikel besteld hebben (2)


  1. Selecteer de namen van de klanten en de namen van de producten die zij besteld hebben (18)


  1. Selecteer de klantnamen en de totaalprijs van hun bestelde producten mits ze boven de 2000,- zijn (1)

TIP

  1. Selecteer o.a. de klantnamen
  2. Maak een INNER JOIN Orders
  3. Maak een tweede INNER JOIN op OrderItems
  4. Voeg aan de SELECT, naast de klantnamen, ook een totaal van de bestellingen toe - SUM(quantity * item_price)
  5. Plaats een GROUP BY op de klantnamen
  6. Gebruik een HAVING om te filteren op alles groter dan 2000