=====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://savage.net.au/SQL/sql-99.bnf.html|ANSI-92]] ändern? 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: 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 SELECT * FROM tab1 [ INNER | LEFT OUTER | RIGHT OUTER | FULL OUTER | NATURAL | CROSS ] JOIN Tab2 ON T1.id = T2.id where tab1.filter = filter_bedingung 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/DB übergreifenden einsetzbar * Das ist aber mehr theoretisch, sobald auch Funktionen wie "to_date" verwendet werden ist das SQL nicht mehr plattform unabhänig.diese Funktionen sind leider selbst in SQL 2011 nicht 100% standardisert 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: SELECT * FROM EMPLOYEES e INNER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID SQL 89: 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: select count(*) from EMPLOYEES e inner join DEPARTMENTS d on ( NVL(e.DEPARTMENT_ID,-1) = to_number(d.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 Spalten mit einander verknüpfen mit dem Key Wort **NATURAL JOIN**: select * from EMPLOYEES NATURAL join DEPARTMENTS; --Plan: Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID" AND "EMPLOYEES"."MANAGER_ID"="DEPARTMENTS"."MANAGER_ID") 2 - filter("DEPARTMENTS"."MANAGER_ID" IS NOT NULL) Angabe der Verknüpfungsspalte: select * from EMPLOYEES join DEPARTMENTS using (DEPARTMENT_ID); Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID") filter("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID") SQL 89: Da die Join Bedingung als solches gar nicht erkennbar ist, wird die DEPARTMENT_ID Spalte auch zweimal ausgeben: SELECT * FROM EMPLOYEES e, DEPARTMENTS d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") ===Left Outer Join, Left Join - Right Outer Join, Right Join === Mit einem Left Join / Right Join wird eine sogenannte linke/rechte Inklusionsverknüpfung erstellen. Die Inklusionsverknüpfungen schließen alle Datensätze aus der ersten/zweiten (linken/rechten) Tabelle mit ein, auch wenn keine entsprechenden Werte für die Verknüpfung der Datensätze in der zweiten Tabelle existiert. SQL 92 SELECT * FROM EMPLOYEES LEFT JOIN DEPARTMENTS ON ( e.DEPARTMENT_ID = d.DEPARTMENT_ID ) SQL 89 SELECT * FROM EMPLOYEES e, DEPARTMENTS d WHERE e.DEPARTMENT_ID (+) = d.DEPARTMENT_ID 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 : Nur je zwei Tabelle können verknüpft werden! Lösung über eine Innerview: select e.ename,d.loc,e.comm from dept d , (select e.ename,b.comm,e.deptno 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: 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)" das kann aber recht langsam werden! ===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: 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/inner Join mit anschließender Projektion auf die Attribute der ersten Tabelle verknüpft ===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: 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: 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: select D1.dname TEAM1,D2.dname TEAM2 ,sysdate+rownum Spieltag 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://oracle-base.com/articles/12c/lateral-inline-views-cross-apply-and-outer-apply-joins-12cr1 * http://db-oriented.com/2015/11/13/write-less-with-more-part-7/ Demnächst mehr ====Quellen ==== Oracle 12c * https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52352 Allgemein: * https://www.hdm-stuttgart.de/~riekert/lehre/db-kelz/chap7.htm * https://en.wikipedia.org/wiki/SQL