====== Joins ======
Joins vergleichen Spaltenwerte von zwei oder mehr Tabellen.\\
Wie viele Tabellen miteinander verglichen werden ist egal, allerdings sinkt mit der Anzahl der Tabellen die Lesbarkeit des Codes und die Performance der DB leidet.\\
**Beispiel:**\\
<code sql>
SELECT a.id, b.id    -- Liefert die Felder ID der Tabellen mit den Aliasnamen 'a' und 'b'.
FROM tabelle1 a      -- TABELLE1 hat den Alias Namen 'a'
JOIN tabelle2 b      -- TABELLE2 hat den Alias Namen 'b'
    ON a.id = b.id   -- Suchoperation des Joins, in diesem Fall ein Vergleich.
</code>

===== Arten von Joins =====

==== Equijoin ====
Ist ein Join, bei dem zwei Tabellen über eine Gleichheitsbedingung (''='') miteinander verknüpft werden.\\
**ANSI-Syntax**
<code sql>
SELECT e.last_name, d.department_name
FROM employees e
JOIN departments d 
    ON e.department_id = d.department_id;
</code>
**Alte Oracle-Syntax (Theta-Join / impliziter Join)**
<code sql>
SELECT e.last_name, d.department_name
FROM employees e, departments d
    WHERE e.department_id = d.department_id;
</code>

**Erkennungsmerkmale:**\\
  * Die Verknüpfungsbedingung verwendet ''='' - daher "equi"
  * Es werden die Zeilen zurückgegeben, die in beiden Tabellen übereinstimmen
  * Zeilen ohne passenden Partner werden **nicht** ausgegeben (das wäre ein Outer-Join)
  * Alternativ zur ''ON''-Klausel kann bei gleichem Spaltennamen auch ''USING'' verwendet werden:
<code sql>
    SELECT e.last_name, d.department_name
    FROM employees e
    JOIN departments d 
        USING (department_id);
</code>

==== Non-Equijoin ====
Verknüpfung mit ''<'', ''>'', ''BETWEEN'' usw. (z.B. Gehalt liegt in einer Gehaltsklasse).
<code sql>
JOIN sal_grades sg 
    ON e.salary BETWEEN sg.low_sal AND sg.high_sal
</code>

==== Self-Join ====
Ist ein Equijoin einer Tabelle mit sich selbst.\\
Beispiel:
<code sql>
-- Zeigt Welcher Mitarbeiter mit welcher ID welchen Vorgesetzten mit seiner ID hat.
SELECT e.last_name "Employee", e.EMPLOYEE_ID "Emp#", m.last_name "Manager", e.MANAGER_ID "Mgn#"
FROM EMPLOYEES e
JOIN EMPLOYEES m 
    ON e.MANAGER_ID = m.EMPLOYEE_ID;
</code>

==== Cross-Join ====
Hier gibt es kein Join-Kriterium, es sind alle Kombinationen möglich (kartesisches Produkt, jede Zeile wird mit jeder kombiniert).\\
Beispiel:
<code sql>
SELECT e.LAST_NAME, d.DEPARTMENT_NAME
FROM EMPLOYEES e
CROSS JOIN DEPARTMENTS d;
</code>

==== (left) Outer-Join ====
Liefert alle Zeilen der Tabelle hinter dem ''FROM'' plus die Schnittmenge der Tabelle hinter dem ''LEFT OUTER JOIN''.\\
Beispiel:
<code sql>
/*
    Zeigt Welcher Mitarbeiter mit welcher ID welchen Vorgesetzten mit seiner ID hat.
    Aber hier wird auch der Chef ohne manager_id mit angezeigt.
*/
SELECT e.last_name "Employee", e.EMPLOYEE_ID "Emp#", m.last_name "Manager", e.MANAGER_ID "Mgn#"
FROM EMPLOYEES e      -- gilt als linke Tabelle
LEFT JOIN EMPLOYEES m -- gilt als rechte Tabelle
    ON e.MANAGER_ID = m.EMPLOYEE_ID;
</code>

Bei einem ''RIGHT OUTER JOIN'' wird die Tabelle hinter dem ''FROM'' als rechte Tabelle definiert.

==== Natural-Join ====
Ein ''NATURAL JOIN'' ist ein sehr einfacher aber auch u.U. recht ungenauer Join.\\
Hier werden **alle** gleichnamigen Spalten der Tabellen miteinander verglichen.\\
Beispiel:
<code sql>
select * from LOCATIONS  fetch first 1 rows only;
/*
   LOCATION_ID STREET_ADDRESS          POSTAL_CODE    CITY    STATE_PROVINCE    COUNTRY_ID    
______________ _______________________ ______________ _______ _________________ _____________ 
          1000 1297 Via Cola di Rie    00989          Roma                      IT
*/

select * from COUNTRIES fetch first 1 rows only;
/*
COUNTRY_ID    COUNTRY_NAME       REGION_ID 
_____________ _______________ ____________ 
AR            Argentina                  2
*/

SELECT LOCATION_ID, STREET_ADDRESS, CITY, STATE_PROVINCE, country_name
FROM LOCATIONS
NATURAL JOIN countries;
/*
   LOCATION_ID STREET_ADDRESS             CITY         STATE_PROVINCE      COUNTRY_NAME                
______________ __________________________ ____________ ___________________ ___________________________ 
          1000 1297 Via Cola di Rie       Roma                             Italy                       
          1100 93091 Calle della Testa    Venice                           Italy                       
          1200 2017 Shinjuku-ku           Tokyo        Tokyo Prefecture    Japan                       
          1300 9450 Kamiya-cho            Hiroshima                        Japan                       
          1400 2014 Jabberwocky Rd        Southlake    Texas               United States of America
…
*/
</code>

----

===== Weitere Info zu Joins =====

==== Anwendung der Aliase ====
Grundsätzlich gilt, wenn ein Feld eindeutig zu einer Tabelle zuordenbar ist, kann man den Alias Feldnamen weg lassen. Man muss es aber nicht.\\
Beispiel:
<code sql>
SELECT e.last_name, d.department_name
FROM employee e
JOIN departments d 
    ON e.department_id = d.department_id;

-- vs.

SELECT last_name, department_name
FROM employee e
JOIN departments d 
    ON e.department_id = d.department_id;
</code>

Da die Spaltennamen im ''SELECT'' eindeutig einer Tabelle zuordenbar sind, kann man hier den Alias auch weg lassen.\\
Dies gilt aber nicht für ''JOIN … ON …'', da hier die Spalte department_id in beiden Tabellen vorkommt.\\
Alternativ könnte man statt ''JOIN … ON …'' auch ''JOIN … USING(department_id)'' verwenden. Hier **MUSS** dann der Alias weg gelassen werden, weil man mit einem ''USING'' den Spaltennamen angibt, der in beiden Tabellen vorkommt.

=== Filtern mit ''WHERE'' ===
Hier gelten die selben Alias-Regeln wie bei ''SELECT''. Spaltennamen, die eindeutig einer Tabelle zuzuordnen sind, können ohne Alias geschrieben werden.\\
Beispiel:
<code sql>
SELECT last_name, department_name
FROM employee e
JOIN departments d 
    ON e.department_id = d.department_id
    WHERE LOWER(department_name) <> 'toronto';
</code>

=== Grundsätzliche Regel ===
Lieber den Alias mitnehmen, auch wenn es ohne Alias semantisch korrekt wäre. Das macht im Zweifel den Code lesbarer, vor allem, wenn es sich um ein komplexes DB-Schema handelt.

==== NULL Felder ====
Bei einem **Inner Join** werden Felder mit Null nicht ausgegeben. Es gilt die Regel:\\
<code>
1 = 1       = TRUE
1 = 2       = FALSE
NULL = NULL = UNKNOWN
NULL = 1    = UNKNOWN
</code>
Möchte man gezielt auch die ''NULL''-Felder ausgeben, muss man explizit darauf prüfen.\\
<code sql>
ON (e.department_id = d.department_id)
OR (e.department_id IS NULL AND d.department_id IS NULL)
</code>

Ausnahme ist der ''IS DISTINCT FROM'' Operator. Beispiel:
<code sql>
ON e.department_id IS NOT DISTINCT FROM d.department_id
</code>

Bei einem **Outer Join** werden ''NULL''-Felder dann mitgegeben, wenn sie zur "Outer-Seite" gehören. Wenn es sich also um ein ''LEFT OUTER JOIN'' handelt, dann werden die ''NULL''-Felder der linken Tabelle ausgegeben, nicht aber die der rechten (oder alle Anderen) Tabellen.\\
Beispiel:
<code sql>
SELECT e.last_name "Employee", e.EMPLOYEE_ID "Emp#", m.last_name "Manager", e.MANAGER_ID "Mgn#"
FROM EMPLOYEES e
JOIN EMPLOYEES m 
    ON e.MANAGER_ID = m.EMPLOYEE_ID 
    where e.LAST_NAME = 'King';

Employee       Emp# Manager        Mgn# 
___________ _______ ___________ _______ 
King            156 Partners        146

-- vs.

SELECT e.last_name "Employee", e.EMPLOYEE_ID "Emp#", m.last_name "Manager", e.MANAGER_ID "Mgn#"
FROM EMPLOYEES e
LEFT JOIN EMPLOYEES m 
    ON e.MANAGER_ID = m.EMPLOYEE_ID 
    where e.LAST_NAME = 'King';

Employee       Emp# Manager        Mgn# 
___________ _______ ___________ _______ 
King            156 Partners        146 
King            100
</code>
Es gibt zwei Mitarbeiter mit dem Namen "King", aber einer von Beiden hat keine Mgn# was ''NULL''-Felder erzeugt, die bei einem Inner Join nicht angezeigt werden.

==== Mehrfach Joins ====
Mehrfach ineinander verschachtelte Joins sind möglich.\\
Beispiel:
<code sql>
SELECT * from EMPLOYEES FETCH FIRST 1 ROWS ONLY;
/*
   EMPLOYEE_ID FIRST_NAME    LAST_NAME    EMAIL    PHONE_NUMBER    HIRE_DATE    JOB_ID        SALARY    COMMISSION_PCT    MANAGER_ID    DEPARTMENT_ID 
______________ _____________ ____________ ________ _______________ ____________ __________ _________ _________________ _____________ ________________ 
           100 Steven        King         SKING    515.123.4567    17.06.87     AD_PRES        24000                                               90
*/

SELECT * from DEPARTMENTS FETCH FIRST 1 ROWS ONLY;
/*
   DEPARTMENT_ID DEPARTMENT_NAME       MANAGER_ID    LOCATION_ID 
________________ __________________ _____________ ______________ 
              10 Administration               200           1700
*/

SELECT * from LOCATIONS FETCH FIRST 1 ROWS ONLY;
/*
   LOCATION_ID STREET_ADDRESS          POSTAL_CODE    CITY    STATE_PROVINCE    COUNTRY_ID    
______________ _______________________ ______________ _______ _________________ _____________ 
          1000 1297 Via Cola di Rie    00989          Roma                      IT
*/

-- Erstelle eine Liste, die den Namen eines Mitarbeiters zusammen mit dem Land und der Stadt seines Departments auflistet:
SELECT e.first_name || ' ' || e.last_name "Employee Name", l.city || ' (' || l.country_id || ')' "Department Location"
FROM EMPLOYEES e
JOIN DEPARTMENTS d 
    USING (DEPARTMENT_ID)
JOIN LOCATIONS l 
    USING (LOCATION_ID);
/*
Employee Name        Department Location    
____________________ ______________________ 
Jennifer Whalen      Seattle (US)           
Michael Hartstein    Toronto (CA)
…
*/
</code>

[[opendocks:oracle-sql-nutshell-doku:nutshell-doku-03|Zurück]] - [[opendocks:oracle-sql-nutshell-doku:nutshell-doku-05|Weiter]]