Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:sql_join_syntax

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
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

Quellen

Cookies helfen bei der Bereitstellung von Inhalten. Durch die Nutzung dieser Seiten erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Rechner gespeichert werden. Weitere Information
"Autor: Gunther Pipperr"
prog/sql_join_syntax.txt · Zuletzt geändert: 2016/06/02 17:07 von Gunther Pippèrr