Gunther Oracle APEX und Datenbank Wiki

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Zuletzt angesehen:
prog:sql_join_syntax

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
prog:sql_join_syntax [2016/05/21 16:41] – [New Feature 12c LATERAL Inline Views, CROSS APPLY und OUTER APPLY Joins] gpipperrprog: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://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: 
 +
 +<code sql>
 +select *
 +  from tab1, tab2
 + where tab1.id = tab2.id;
 +</code>
 +
 +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
 + where 
 +   tab1.filter = filter_bedingung
 +</code>
 +
 +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:
 +<code sql>
 +SELECT * FROM EMPLOYEES  e INNER JOIN DEPARTMENTS d  ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
 +</code>
 +
 +SQL 89:
 +<code sql>
 +SELECT * FROM EMPLOYEES e, DEPARTMENTS d  WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID        
 +</code>
 +
 +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,-1) = to_number(d.DEPARTMENT_ID))
 +</code>
 +
 +
 +===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**:
 +<code sql>
 +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)
 +   
 +</code>
 +
 +Angabe der Verknüpfungsspalte:
 +
 +<code sql>
 +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")
 +       
 +</code>
 +
 +
 +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("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
 +       filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
 +          
 +</code>
 +
 +
 +===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
 +<code sql>
 +SELECT *   
 + FROM EMPLOYEES LEFT JOIN DEPARTMENTS ON ( e.DEPARTMENT_ID = d.DEPARTMENT_ID )
 +</code>
 +
 +SQL 89
 +<code sql>
 +SELECT * FROM EMPLOYEES e, DEPARTMENTS d  WHERE e.DEPARTMENT_ID (+) = d.DEPARTMENT_ID
 +</code>
 +
 +<note important>
 +Wo kommt das (+) hin? Merkspruch: Immer auf die Seite, da wo WENIGER DATEN!
 +</note>
 +
 +!OR und IN Operator werden nicht unterstützt!
 +
 +Einschränkungen für Outer Joins ORA-01417 : <fc #800000>Nur je zwei Tabelle können verknüpft werden!</fc>
 +
 +Lösung über eine Innerview:
 +
 +<code sql>
 +
 +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;
 +
 +</code>
 +
 +===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;
 +</code>
 +
 +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:
 +<code sql>
 +SELECT * 
 +  FROM EMPLOYEES FULL JOIN DEPARTMENTS 
 +    ON (EMPLOYEES.LAST_NAME = DEPARTMENTS.LAST_NAME 
 +        AND EMPLOYEES.Vorname = DEPARTMENTS.Vorname) 
 +</code>
 +
 +
 +===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:
 +<code sql>
 +SELECT * FROM EMPLOYEES e1 INNER JOIN DEPARTMENTS e2 ON e1.DEPARTMENT_ID > e2.DEPARTMENT_ID
 +</code>
 +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
 +</code>
 +
 +
 +===Self Non Equi Join ===
 +
 +Aufgabe:
 +Alle Abteilungen sollen gegeneinander Handball spielen
 +
 +
 +SQL 89:
 +<code sql>
 +select D1.dname TEAM1,D2.dname TEAM2 ,sysdate+rownum Spieltag  
 +  from dept D1, dept D2
 + where D1.deptno < D2.deptno
 +</code>
 +
 +
 +
 +
 +
 +===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/
 +
 +
 +
 +<note tip>Demnächst mehr</note>
 +====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
 +
  
prog/sql_join_syntax.txt · Zuletzt geändert: 2016/06/02 17:07 von gpipperr