Die allgemeine Form des Equi Join lautet:
Transcrição
Die allgemeine Form des Equi Join lautet:
SQL Inner und Outer Join 1 Equi Join und Inner Join Die allgemeine Form des Equi Join lautet: 2 beliebige Felder in zwei Tabellen in denen ein Feld (Feld2) gleiche Einträge hat SELECT Tabelle1.Feld1, Tabelle2.Feld3 FROM Tabelle1, Tabelle2 WHERE Tabelle1.Feld2 = Tabelle2.Feld2 Am Beispiel der Tabellen Kunde und Kauf (Datenbank Backwaren.mdb) select kunde.vorname, kunde.name, kauf.datum from kunde, kauf where kunde.knr=kauf.knr; ergibt dieser Equi Join: Dasselbe Ergebnis erzielen Sie in dem Fall mit dem Inner Join: select kunde.vorname, kunde.name, kauf.datum from kunde inner join kauf on kunde.knr=kauf.knr; Eine einschränkende where-Klausel kann in beiden Fällen verwendet werden: select kunde.vorname, kunde.name, kauf.datum from kunde, kauf where kunde.knr=kauf.knr and kunde.name="Münch"; Beachten Sie dabei, dass im zweiten Fall, also beim Inner Join, die where-Klausel erst in der letzten Zeile kommt, außerdem gibt es dabei dann kein and: select kunde.vorname, kunde.name, kauf.datum from kunde inner join kauf on kunde.knr=kauf.knr where kunde.name="Münch"; also ist die allgemeine Form des Inner Join: SELECT Tabelle1.Feld1, Tabelle2.Feld3 FROM Tabelle1 inner Join Tabelle2 on Tabelle1.Feld2 = Tabelle2.Feld2 1 SQL Inner und Outer Join 2 Outer Join Wenn Sie die Tabellen Kunde und das Ergebnis des Join (ohne einschränkende Bedingung) vergleichen, dann sehen Sie, dass zur Kundin Petra Hammer kein Kauf verzeichnet ist. Wenn man alle Kaufdaten sehen will, aber außerdem erkennen können will, wer nichts gekauft hat, dann wird ein Outer Join verwendet. Die Fragestellung ist dabei gewissermaßen: Zeige, was außerhalb der Verbindung der beiden Tabellen liegt“ Beispiel: select kunde.vorname, kunde.name, kauf.datum from kunde left outer join kauf on kunde.knr=kauf.knr In Access-SQL kommt übrigens dasselbe Ergebnis heraus, wenn man das Wort outer weglässt. Zum besseren Verständnis ist es aber sinnvoll, denn tatsächlich wird ja etwas gesucht, was außerhalb der Verbindung der Daten liegt. Die Information, die über die left outer-Verbindung gesucht wurde, ist noch völlig plausibel, bzw. sie ist kein Widerspruch zur Datenintegrität. Natürlich können in der Tabelle Kunden gespeichert sein, für die kein Kauf verzeichnet ist. Der umgekehrte Fall läge aber vor, wenn in der Tabelle Kauf Kundennummern (KNR) stehen, die nicht in der Tabelle Kunde vorkommen. Dies ist ein Verstoß gegen die referentielle Integrität! Trotzdem kommen natürlich in nicht konsequent angelegten Datenbanken vieler solcher Verstöße vor, es gibt „verwaiste“ Datensätze. Aufgabe: Öffnen Sie das Beziehungsfenster und nehmen Sie die referentielle Integrität zwischen Kunde und Kauf heraus! Tragen Sie dann die beiden Datensätze unten zusätzlich ein (KNR 7 und 8 ist nicht in der Tabelle Kunde vorhanden). Jetzt kann der right outer join die Daten suchen, die in der rechts stehenden Tabelle vorkommen, aber kein Gegenstück in der links stehenden Tabelle haben. Sie sehen im Ergebnis, dass es zwei Kauf- Einträge gibt, zu denen kein Kunde zuzuordnen ist. SELECT kunde.vorname, kunde.name, kauf.datum FROM kunde right outer JOIN kauf ON kunde.knr=kauf.knr; 2 SQL Inner und Outer Join 3 Inner Join über drei Tabellen Angenommen, es wird eine Gegenüberstellung von verkauften Artikeln und Kundennummern (knr) in folgender Form gewünscht: In der Access-Entwurfsansicht sähe diese Abfrage so aus: Dann würde dies als Equi Join mit folgender Syntax gelöst: select a.bezeichnung, ka.knr from artikel a, kaufposition kp, kauf ka where a.artnr=kp.artnr and kp.kaufnr=ka.kaufnr ab hier verwenden wir Alias-Namen, damit die Ausdrücke übersichtlich gehalten werden können) Hier wird als Inner Join ein geschachtelter Ausdruck benötigt: select a.bezeichnung, ka.knr from kauf ka inner join (artikel a inner join kaufposition kp on a.artnr = kp.artnr) on ka.kaufnr = kp.kaufnr; Dabei muss man sich zunächst die Verbindung über die in Klammern gesetzten Ausdrücke vorstellen: artikel kaufposition ... from artikel a inner join kaufposition kp on a.artnr = kp.artnr kaufposition kauf Von der Tabelle Kaufposition zur Tabelle Kauf wäre es diese Verbindung: ....from kauf ka inner join kaufposition kp on ka.kaufnr = kp.kaufnr; Zusammengesetzt ergibt sich daraus der geklammerte Ausdruck oben. 3 SQL Inner und Outer Join Verglichen mit dem Equi Join ist dies ziemlich kompliziert. Diese Syntax wird aber benötigt, wenn mehr als zwei Tabellen verwendet werden und dabei eine Aussage in der Art benötigt wird, wie Sie sie beim right outer Join kennengelernt haben. Dabei hatten wir zum Beispiel die Frage gestellt: Zeige die Kaufdaten, zu denen kein Kunde zugeordnet werden kann. Über mehr als zwei Tabellen sähe die Frage dann beispielsweise so aus: Zeige die Artikel, die verkauft wurden, und zu welchem Kauf wir keinen Kunden zuordnen können. Aufgabe: Lösen Sie jetzt folgende Abfragen in der Schreibweise eines Inner Join: 1. Zeige Bezeichnung, verkaufte Stückzahl und Stückpreis aller Artikel, die verkauft wurden. 2. Zu dieser Abfrage 1 sollen zusätzlich die Summen der Artikel gebildet werden, sowie die Gruppierung 3. Datum und Kaufnr der Einkäufe von Gosling bzw. Faber 4. Die Bezeichnung des Artikels, der mit der Pos. Nr 5 (Tabelle Kaufposition) verkauft wurde 5. Die Artikel, die an Kundennummer 4 verkauft wurden 6. Die Artikelnummer und Bezeichnung, die am 03.07.03 verkauft wurden 7. Artikelnummer und Bezeichnung und die Namen der Kunden, die den jeweiligen Artikel gekauft haben. 4 SQL Inner und Outer Join Lös. 1. select a.bezeichnung, a.stückpreis, kp.stückzahl from artikel a inner join kaufposition kp on a.artnr=kp.artnr; Lös 2. select a.bezeichnung, sum(kp.stückzahl) as "Summe verkaufte Artikel" from artikel a inner join kaufposition kp on a.artnr=kp.artnr group by a.bezeichnung; Lös. 3 SELECT ka.kaufnr, ku.name from kunde ku inner join kauf ka on ku.knr=ka.knr where ku.name="Gosling" or ku.name="Faber"; Lös. 4 SELECT a.bezeichnung FROM artikel a INNER JOIN kaufposition kp ON a.artnr=kp.artnr where kp.pos=5; Lös. 5 SELECT a.bezeichnung FROM artikel a INNER JOIN (kaufposition kp inner join kauf ka on kp.kaufnr = ka.kaufnr) on a.artnr=kp.artnr where ka.knr=4; Lös. 6 SELECT a.artnr, a.bezeichnung FROM artikel a INNER JOIN (kaufposition kp inner join kauf ka on kp.kaufnr = ka.kaufnr) on a.artnr=kp.artnr where ka.datum=#2003-07-03#; Lös. 7 select a.artnr, a.bezeichnung, ku.name from kunde ku NNER JOIN (kauf ka INNER JOIN (artkel a INNER JOIN kaufposition kp on a.artnr = kp.artnr) on ka. kaufnr = kp.kaufnr) on ku.knr.knr = ka.knr; 5 SQL Inner und Outer Join 4 Join innerhalb einer Tabelle Eine Tabelle kann mit sich selbst verbunden werden. Zunächst sollen Sie sehen, wie das funktioniert, bevor wir uns ansehen, wozu das gut ist. Aufgabe: Erstellen Sie eine neue Abfrage und geben Sie ein: SELECT * from Artikel a, artikel b Das Ergebnis ist: ... usw, 16 Zeilen bei 4 Datensätzen! Um zu sehen, welchen Sinn diese Verknüpfung macht, stellen Sie sich bitte vor, dass versehentlich eine ArtNr für zwei verschiedene Artikel verwendet wurde. 6