SQL Basis

• Het typen van volledig gekwalificeerde kolomnamen is veel werk

SELECT Products.prod_id,
       Products.prod_name
FROM   Products; 

• Een alias voorkomt dat men telkens de tabelnaam moet toevoegen bij het aanspreken van een kolom
• Als een alias gedefinieerd wordt moet je hem gebruiken

SELECT P.prod_id,
       P.prod_name
FROM   Products AS P; 

• Bij het gebruik van joins is een tabel alias aanzienlijk minder typewerk

SELECT C.cust_name AS KlantNaam,
       C.cust_contact AS KlantContact,
       P.prod_name AS ProductNaam,
       V.vend_name AS Leverancier
FROM   Customers AS C
       INNER JOIN Orders       AS O   ON C.cust_id = O.cust_id
       INNER JOIN OrderItems   AS OI  ON O.order_num = OI.order_num
       INNER JOIN Products     AS P   ON OI.prod_id = P.prod_id
       INNER JOIN Vendors      AS V   ON P.vend_id = V.vend_id
WHERE  OI.prod_id = 'RGAN01';							

• Bij Oracle is het Keyword AS, voor het benoemen van een tabel-alias, niet toegestaan
• Bij de meeste DBMS-en is het AS keyword niet verplicht

-- Oracle / MySQL / SQL Server
SELECT C.cust_name KlantNaam,
       C.cust_contact KlantContact,
       P.prod_name ProductNaam,
       V.vend_name Leverancier
FROM   Customers C
       INNER JOIN Orders O ON C.cust_id = O.cust_id
       INNER JOIN OrderItems OI ON O.order_num = OI.order_num
       INNER JOIN Products P ON OI.prod_id = P.prod_id
       INNER JOIN Vendors V ON P.vend_id = V.vend_id
WHERE  OI.prod_id = 'RGAN01';							

SQL pro's gebruiken geen AS

• De Self Join is een Join op 1 tabel
• Het gebruik van aliasses is nodig
• Kan ook met een subquery

Selecteer alle klantcontacten die voor het gelijknamige bedrijf werken als 'Jim Jones'

Met een Subquery

SELECT   cust_id,
         cust_name,
         cust_contact
FROM     Customers
WHERE    cust_name = ( SELECT cust_name
                       FROM   Customers
                       WHERE  cust_contact = 'Jim Jones' ) ;

Met een Self Join

SELECT   c1.cust_id, c1.cust_name, c1.cust_contact
FROM     Customers c1, Customers c2
WHERE    c1.cust_name = c2.cust_name AND c2.cust_contact = 'Jim Jones';	

Inner join

• Het resultaat van de Inner Join is waar de sleutels overeenkomen

SELECT <kolom1>, <kolom2>
FROM   <tabel1>
       INNER JOIN <tabel2> ON <tabel1>.<sleutel> = <tabel2>.<sleutel>	

• Het resultaat van de Left Outer Join is waar de sleutels overeenkomen + alle data uit de eerst genoemde tabel in de FROM clausule

SELECT <kolom1>, <kolom2>
FROM   <tabelA> LEFT OUTER JOIN <tabelB> ON <tabelA>.<sleutel> = <tabelB>.<sleutel>	

• Het resultaat van de Right Outer Join is waar de sleutels overeenkomen + alle data uit de tweede genoemde tabel in de FROM clausule

SELECT <kolom1>, <kolom2>
FROM   <tabelA> RIGHT OUTER JOIN <tabelB> ON <tabelA>.<sleutel> = <tabelB>.<sleutel>	

• De namen LEFT & RIGHT komen dus van de tabel-benoemings-volgorde in de FROM clausule
• Het DBMS SQLite ondersteunt geen RIGHT OUTER JOIN. Dat hoeft geen probleem te zijn want als je de volgorde van de tabellen omdraait in de FROM heb je hetzelfde resultaat met de LEFT OUTER JOIN

SELECT   <kolom1>,
         <kolom2>	
FROM     <tabelA> LEFT OUTER JOIN <tabelB> ON <tabelA>.<sleutel> = <tabelB>.<sleutel> 			

SELECT   <kolom1>,
         <kolom2>	
FROM     <tabelB> RIGHT OUTER JOIN <tabelA> ON <tabelA>.<sleutel> = <tabelB>.<sleutel> 			

• In beide bovenstaande gevallen is het resultaat hetzelfde. Alle rijen waar de sleutels overeenkomen zijn onderdeel van het resultaat + alle niet overeenkomende rijen uit tabel A

Selecteer de namen van de leveranciers die producten leveren van minder dan 6,-

-- Oracle / MySQL / SQL Server
SELECT	DISTINCT V.vend_name
FROM	Vendors V
	INNER JOIN Products P ON V.vend_id = P.vend_id
WHERE	P.prod_price < 6;

-- Oracle / MySQL / SQL Server	
SELECT	DISTINCT V.vend_name
FROM	Vendors V
	LEFT OUTER JOIN Products P ON V.vend_id = P.vend_id
WHERE	P.prod_price < 6;

Selecteer de namen van de klanten die wel en geen bestelling geplaatst hebben en de bijbehorende ordernummers.

-- Oracle / MySQL / SQL Server
SELECT C.cust_name,
       O.order_num
FROM   Customers C
       LEFT OUTER JOIN Orders O ON C.cust_id = O.cust_id;

• Het resultaat van de Full Outer Join is waar de sleutels overeenkomen + alle andere data uit beide tabellen
• De Full Outer Join wordt weinig gebruikt
• De Full Outer Join wordt niet ondersteund door MySQL - MariaDB - SQLite - MS Access

SELECT <kolom1>, <kolom2>
FROM   <tabelA> FULL OUTER JOIN <tabelB> ON <tabelA>.<sleutel> = <tabelB>.<sleutel>	

• Bekijk goed welke Join je nodig hebt. Meer dan gemiddeld is een INNER JOIN voldoende / goedkoper
• Koppel de sleutels op de juiste manier anders krijg je een cartesian product (crossjoin)
• Begin met kleine delen van de query voordat je meteen 1 complexe samenstelt
• De woorden INNER & OUTER kunnen weggelaten worden bij het schrijven van een query
  • Een JOIN is altijd een INNER JOIN
  • Een LEFT JOIN is altijd een LEFT OUTER JOIN

SELECT Cursist.naam,
       Training.onderwerp
FROM   Cursist
       JOIN Training ON Cursist.id = Training.user_id	

SELECT Cursist.naam,
       Training.onderwerp
FROM   Cursist
       LEFT JOIN Training ON Cursist.id = Training.user_id	

SELECT Cursist.naam,
       Training.onderwerp
FROM   Cursist
       RIGHT JOIN Training ON Cursist.id = Training.user_id	

SELECT Cursist.naam,
       Training.onderwerp
FROM   Cursist
       FULL JOIN Training ON Cursist.id = Training.user_id	

56. Selecteer alle klanten en hoeveel orders ze hebben geplaatst (5)

57. Selecteer de namen en de aantallen van de bestelde en niet bestelde producten (9)

58. Selecteer alle klantnummers en klantnamen en per klant het totaalbedrag van hun bestellingen (5)

59. Selecteer alle leveranciers die geen producten aanbieden (3)

60. Selecteer alle leveranciers en de hoeveelheid producten die ze aanbieden (6)