Inhaltsverzeichnis
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 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
!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 ⇒