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:
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.
Ist ein Join, bei dem zwei Tabellen über eine Gleichheitsbedingung (=) miteinander verknüpft werden.
ANSI-Syntax
SELECT e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;
Alte Oracle-Syntax (Theta-Join / impliziter Join)
SELECT e.last_name, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id;
Erkennungsmerkmale:
= - daher „equi“ON-Klausel kann bei gleichem Spaltennamen auch USING verwendet werden:SELECT e.last_name, d.department_name FROM employees e JOIN departments d USING (department_id);
Verknüpfung mit <, >, BETWEEN usw. (z.B. Gehalt liegt in einer Gehaltsklasse).
JOIN sal_grades sg ON e.salary BETWEEN sg.low_sal AND sg.high_sal
Ist ein Equijoin einer Tabelle mit sich selbst.
Beispiel:
-- 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;
Hier gibt es kein Join-Kriterium, es sind alle Kombinationen möglich (kartesisches Produkt, jede Zeile wird mit jeder kombiniert).
Beispiel:
SELECT e.LAST_NAME, d.DEPARTMENT_NAME FROM EMPLOYEES e CROSS JOIN DEPARTMENTS d;
Liefert alle Zeilen der Tabelle hinter dem FROM plus die Schnittmenge der Tabelle hinter dem LEFT OUTER JOIN.
Beispiel:
/* 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;
Bei einem RIGHT OUTER JOIN wird die Tabelle hinter dem FROM als rechte Tabelle definiert.
Ein NATURAL JOIN ist ein sehr einfacher aber auch u.U. recht ungenauer Join.
Hier werden alle gleichnamigen Spalten der Tabellen miteinander verglichen.
Beispiel:
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 … */
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:
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;
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.
Hier gelten die selben Alias-Regeln wie bei SELECT. Spaltennamen, die eindeutig einer Tabelle zuzuordnen sind, können ohne Alias geschrieben werden.
Beispiel:
SELECT last_name, department_name FROM employee e JOIN departments d ON e.department_id = d.department_id WHERE LOWER(department_name) <> 'toronto';
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.
Bei einem Inner Join werden Felder mit Null nicht ausgegeben. Es gilt die Regel:
1 = 1 = TRUE 1 = 2 = FALSE NULL = NULL = UNKNOWN NULL = 1 = UNKNOWN
Möchte man gezielt auch die NULL-Felder ausgeben, muss man explizit darauf prüfen.
ON (e.department_id = d.department_id) OR (e.department_id IS NULL AND d.department_id IS NULL)
Ausnahme ist der IS DISTINCT FROM Operator. Beispiel:
ON e.department_id IS NOT DISTINCT FROM d.department_id
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:
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
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 ineinander verschachtelte Joins sind möglich.
Beispiel:
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) … */