prog:sql_join_syntax
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende ÜberarbeitungNächste Überarbeitung | Vorhergehende Überarbeitung | ||
prog:sql_join_syntax [2016/05/21 16:40] – [Self Non Equi Join] gpipperr | prog:sql_join_syntax [2016/06/02 17:07] (aktuell) – [Left Outer Join, Left Join - Right Outer Join, Right Join] gpipperr | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
+ | =====Oracle ANSI-89 Standard versus dem ANSI-92 - The SQL Join Battle ===== | ||
+ | |||
+ | Warum sollte ein Entwickler auf der Datenbank einen Oracle SQL 89 Schreibstil auf SQL [[http:// | ||
+ | |||
+ | Der ANSI 89 Stil wird ja noch gut von Oracle unterstützt und nach Jahren mit der Oracle Datenbank ist man es einfach so gewöhnt zu arbeiten. | ||
+ | |||
+ | |||
+ | |||
+ | === Unterschied === | ||
+ | |||
+ | |||
+ | ANSI-89 Standard - Join Syntax: | ||
+ | |||
+ | <code sql> | ||
+ | select * | ||
+ | from tab1, tab2 | ||
+ | where tab1.id = tab2.id; | ||
+ | </ | ||
+ | |||
+ | Die Verknüpfungsbedingung wird im Where Bereich des Statmenets wie eine Filter Bedingung definiert. | ||
+ | |||
+ | Vorteil: | ||
+ | * Auf den ersten Blick weniger Tipp Arbeit | ||
+ | |||
+ | Nachteil: | ||
+ | * Keine Unterscheidung zwischen der eigentlich nur für den Join notwendingen verknüpfung und dem Filtern von Daten | ||
+ | * Gefahr von Cross Joins, wenn die Verknüpfungen nicht aufgehen oder gar nicht angeben wurden | ||
+ | | ||
+ | Prinzip - ANSI-92 - Join Syntax | ||
+ | <code sql> | ||
+ | SELECT * | ||
+ | FROM tab1 [ INNER | LEFT OUTER | RIGHT OUTER | FULL OUTER | NATURAL | CROSS ] JOIN Tab2 | ||
+ | ON T1.id = T2.id | ||
+ | | ||
+ | | ||
+ | </ | ||
+ | |||
+ | Vorteil: | ||
+ | * Es ist deutlich klarer, welche Bedingung im SQL Statement die Verknüpfung der Tabellen definiert und welche Bedingung nur eine reine Filter Bedingung ist | ||
+ | * Out Join deutlich klarer als die (+) Oracle Syntax | ||
+ | * Full Outer Join nur mit dieser Syntax überhaupt möglich | ||
+ | * Im Rahmen des Standard mehr Plattform/ | ||
+ | * Das ist aber mehr theoretisch, | ||
+ | |||
+ | Nachteil: | ||
+ | * Je nach Schreibstiel etwas unübersichtlicher bei vielen Tabellen | ||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== Übersicht über die verschiedenen Join Typen ==== | ||
+ | |||
+ | ===Inner Join = Equivalent Join - on () === | ||
+ | |||
+ | Verbindet Datensätze aus zwei Tabellen, sobald ein Feld, je ein einer der beiden Tabellen, den gleiche Werte auf beiden Seiten der Verknüfpung enthält. | ||
+ | |||
+ | SQL 92: | ||
+ | <code sql> | ||
+ | SELECT * FROM EMPLOYEES | ||
+ | </ | ||
+ | |||
+ | SQL 89: | ||
+ | <code sql> | ||
+ | SELECT * FROM EMPLOYEES e, DEPARTMENTS d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID | ||
+ | </ | ||
+ | |||
+ | Kann eine der Seiten NULL Werte enthalten, können leicht unerwartete Ergebnisse auftreten, auch sollten die Datentypen übereinstimmen. | ||
+ | |||
+ | Über NVL und Casting auf den richtigen Datentyp können unschöne Überraschungen vermieden werden: | ||
+ | <code sql> | ||
+ | select count(*) from EMPLOYEES e inner join DEPARTMENTS d on ( NVL(e.DEPARTMENT_ID, | ||
+ | </ | ||
+ | |||
+ | |||
+ | ===Natural Join - using () === | ||
+ | |||
+ | Verknüpft die beiden Tabellen über die gleichlautenden Spaltennamen auf beiden Seiten. | ||
+ | |||
+ | Gleichlautende Spalten werden im Ergebnis nur einmal angezeigt. | ||
+ | |||
+ | Haben die Tabellen keine gleichlautenden Spalten, wird der Natural Join zum Cross Join. | ||
+ | |||
+ | Gibt es nur eine gleichlautende Spalte, so ist der Natural Join ein Inner Join mit anschließender Projektion, bei der gleichnamige Spalten ausgeblendet werden. | ||
+ | |||
+ | Für den Natural Join gibt es keinen speziellen SQL92-Befehl. Er wird bei Bedarf aus einem Inner Join mit anschließender Projektion erzeugt | ||
+ | |||
+ | |||
+ | |||
+ | SQL 92: | ||
+ | |||
+ | Die gleichen Spaltennamen der Join Bedingung werden in der Ergebnisliste nicht doppelt angezeigt! | ||
+ | |||
+ | |||
+ | Alle gleichlautenden | ||
+ | <code sql> | ||
+ | select * from EMPLOYEES NATURAL join DEPARTMENTS; | ||
+ | |||
+ | --Plan: | ||
+ | Predicate Information (identified by operation id): | ||
+ | --------------------------------------------------- | ||
+ | |||
+ | 1 - access(" | ||
+ | AND " | ||
+ | 2 - filter(" | ||
+ | |||
+ | </ | ||
+ | |||
+ | Angabe der Verknüpfungsspalte: | ||
+ | |||
+ | <code sql> | ||
+ | select * from EMPLOYEES join DEPARTMENTS using (DEPARTMENT_ID); | ||
+ | |||
+ | Predicate Information (identified by operation id): | ||
+ | --------------------------------------------------- | ||
+ | |||
+ | 4 - access(" | ||
+ | | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | SQL 89: | ||
+ | Da die Join Bedingung als solches gar nicht erkennbar ist, wird die DEPARTMENT_ID Spalte auch zweimal ausgeben: | ||
+ | <code sql> | ||
+ | SELECT * FROM EMPLOYEES e, DEPARTMENTS d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID | ||
+ | Predicate Information (identified by operation id): | ||
+ | --------------------------------------------------- | ||
+ | 4 - access(" | ||
+ | | ||
+ | | ||
+ | </ | ||
+ | |||
+ | |||
+ | ===Left Outer Join, Left Join - Right Outer Join, Right Join === | ||
+ | |||
+ | Mit einem Left Join / Right Join wird eine sogenannte linke/ | ||
+ | |||
+ | Die Inklusionsverknüpfungen schließen alle Datensätze aus der ersten/ | ||
+ | |||
+ | SQL 92 | ||
+ | <code sql> | ||
+ | SELECT * | ||
+ | FROM EMPLOYEES LEFT JOIN DEPARTMENTS ON ( e.DEPARTMENT_ID = d.DEPARTMENT_ID ) | ||
+ | </ | ||
+ | |||
+ | SQL 89 | ||
+ | <code sql> | ||
+ | SELECT * FROM EMPLOYEES e, DEPARTMENTS d WHERE e.DEPARTMENT_ID (+) = d.DEPARTMENT_ID | ||
+ | </ | ||
+ | |||
+ | <note important> | ||
+ | Wo kommt das (+) hin? Merkspruch: Immer auf die Seite, da wo WENIGER DATEN! | ||
+ | </ | ||
+ | |||
+ | !OR und IN Operator werden nicht unterstützt! | ||
+ | |||
+ | Einschränkungen für Outer Joins ORA-01417 : <fc # | ||
+ | |||
+ | Lösung über eine Innerview: | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | select e.ename, | ||
+ | | ||
+ | , (select e.ename, | ||
+ | from emp e ,bonus b | ||
+ | where e.ename = b.ename (+)) e | ||
+ | where e.deptno (+) =d.deptno; | ||
+ | |||
+ | </ | ||
+ | |||
+ | ===Anti Join === | ||
+ | |||
+ | Ziel ist es alle Datensätze aus der Tabelle zu filtern, die NICHT in der anderen Tabelle vorkommen. | ||
+ | |||
+ | Beispiel: | ||
+ | <code sql> | ||
+ | select * | ||
+ | from EMPLOYEES | ||
+ | LEFT JOIN DEPARTMENTS ON ( e.DEPARTMENT_ID = d.DEPARTMENT_ID ) | ||
+ | where d.DEPARTMENT_ID is null; | ||
+ | </ | ||
+ | |||
+ | Klassisch wäre die Lösung mit " ID not in (select id from xxxxx)" | ||
+ | |||
+ | |||
+ | |||
+ | ===Full Outer Join, Full Join=== | ||
+ | |||
+ | Eine Kombination von Left Outer Join und Right Outer Join. | ||
+ | |||
+ | In der älteren Schreibweise (Oracle) kann ein Full Outer Join nicht mit (+)-Zeichen auf beiden Seiten erstellt werden, sondern wird über die Vereinigung (Union) eines Left Outer Join und eines Right Outer Join zusammengesetzt. | ||
+ | |||
+ | |||
+ | SQL 92: | ||
+ | <code sql> | ||
+ | SELECT * | ||
+ | FROM EMPLOYEES FULL JOIN DEPARTMENTS | ||
+ | ON (EMPLOYEES.LAST_NAME = DEPARTMENTS.LAST_NAME | ||
+ | AND EMPLOYEES.Vorname = DEPARTMENTS.Vorname) | ||
+ | </ | ||
+ | |||
+ | |||
+ | ===Union Join === | ||
+ | |||
+ | Ähnlich dem Full Outer Join werden Datensätze beider Tabellen aufgenommen. Sie werden aber nicht über eine Bedingung verknüpft (aber auch kein kartesisches Produkt!) | ||
+ | |||
+ | Unter Oracle steht der Union Join steht nicht immer zur Verfügung, da er zum SQL 92 Intermediate Level gehört | ||
+ | |||
+ | ===Semi Join === | ||
+ | |||
+ | Bei einem Semi Join kommen die Ergebnisse nur von einem Join-Partner | ||
+ | |||
+ | Die zwei Tabellen werden über eine Natural/ | ||
+ | |||
+ | |||
+ | ===Theta Join, Non-Equivalent Join === | ||
+ | |||
+ | Der Theta Join ist eine Verallgemeinerung des Inner Join. | ||
+ | |||
+ | Während beim Inner Join die Gleichheit des Inhalts zweier Attribute verglichen wird, wird beim Theta Join der Inhalt der Attribute i und j mit einer beliebigen Formel Theta(i,j) verglichen. | ||
+ | |||
+ | * etwa i = j (i gleich j; Inner Join), | ||
+ | * i < j (i kleiner j), | ||
+ | * i <= j (i kleiner oder gleich j), | ||
+ | * i > j (i größer j) usw. | ||
+ | |||
+ | |||
+ | SQL 92: | ||
+ | <code sql> | ||
+ | SELECT * FROM EMPLOYEES e1 INNER JOIN DEPARTMENTS e2 ON e1.DEPARTMENT_ID > e2.DEPARTMENT_ID | ||
+ | </ | ||
+ | Was kein so sinnvolles Ergebnis ergibt .-) | ||
+ | |||
+ | |||
+ | ===Self Join=== | ||
+ | |||
+ | Ein Self Join ist ein Join, bei dem eine Tabelle mit sich selbst verknüpft wird. | ||
+ | |||
+ | SQL 92: | ||
+ | <code sql> | ||
+ | SELECT * FROM EMPLOYEES e1 INNER JOIN EMPLOYEES e2 ON e1.EMPLOYEE_ID = e2.EMPLOYEE_ID | ||
+ | </ | ||
+ | |||
+ | |||
+ | ===Self Non Equi Join === | ||
+ | |||
+ | Aufgabe: | ||
+ | Alle Abteilungen sollen gegeneinander Handball spielen | ||
+ | |||
+ | |||
+ | SQL 89: | ||
+ | <code sql> | ||
+ | select D1.dname TEAM1, | ||
+ | from dept D1, dept D2 | ||
+ | where D1.deptno < D2.deptno | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ===New Feature 12c LATERAL Inline Views, CROSS APPLY und OUTER APPLY Joins=== | ||
+ | |||
+ | Siehe dazu => | ||
+ | |||
+ | * https:// | ||
+ | * http:// | ||
+ | |||
+ | |||
+ | |||
+ | <note tip> | ||
+ | ====Quellen ==== | ||
+ | |||
+ | |||
+ | Oracle 12c | ||
+ | |||
+ | * https:// | ||
+ | |||
+ | |||
+ | |||
+ | Allgemein: | ||
+ | |||
+ | * https:// | ||
+ | * https:// | ||
+ | |||
prog/sql_join_syntax.txt · Zuletzt geändert: 2016/06/02 17:07 von gpipperr