Joins – Join multiple tables

Subject: Information Technology – Database Systems School level: 11th grade – HTL Informatik Prerequisites: DQL – SELECT, WHERE, GROUP BY, SCOTT schema Author: HTL Pinkafeld – IF/IT

1. Why joins?

1.1 Data is distributed across multiple tables

Normalization splits data across multiple tables to avoid redundancy. For meaningful queries, these tables need to be rejoined:

-- EMP only shows DEPTNO = 10, 20, 30
SELECT ENAME, DEPTNO FROM EMP WHERE EMPNO = 7839;
-- KING   10

-- DEPT explains what Department 10 means
SELECT DNAME, LOC FROM DEPT WHERE DEPTNO = 10;
-- ACCOUNTING   NEW YORK

-- Mit JOIN: beide Informationen in einer Abfrage
SELECT ENAME, DNAME, LOC
FROM   EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
WHERE  EMPNO = 7839;
-- KING   ACCOUNTING   NEW YORK

1.2 Join types at a glance

Table A Table B

  1                   1
  2         ∩         2    INNER JOIN  → nur gemeinsame Zeilen
  3                   3
  4                        nur in A
             5             nur in B

LEFT  OUTER JOIN  → alle aus A + passende aus B
RIGHT OUTER JOIN  → passende aus A + alle aus B
FULL  OUTER JOIN  → alle aus A + alle aus B
CROSS JOIN        → jede Zeile aus A × jede Zeile aus B
SELF  JOIN        → Tabelle mit sich selbst verknüpft

1.3 The join condition

A join always requires a join condition - it determines which rows from table A belong to which rows from table B:

-- Join-Bedingung: EMP.DEPTNO muss gleich DEPT.DEPTNO sein
FROM EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
                   ─────────────────────────────
                        Join-Bedingung

2. Cartesian product – CROSS JOIN

2.1 What is a Cartesian product?

Without a join condition, every row of the first table is combined with every row of the second table:

-- 14 employees × 4 departments = 56 lines!
SELECT ENAME, DNAME
FROM   EMP CROSS JOIN DEPT;

-- Older Oracle syntax (no JOIN keyword, no WHERE):
SELECT ENAME, DNAME
FROM   EMP, DEPT;

2.2 When does it make sense?

The Cartesian product is almost always an error - it is usually caused by a forgotten join condition. Occasionally it is used intentionally, for example to create calendars or combination tables.

Note: With n rows in table A and m rows in table B, n × m rows are created. With large tables this can bring the server to a halt!

3. INNER JOIN – intersection

3.1 Basic principle

The INNER JOIN returns only those rows for which a matching row exists in both tables according to the join condition:

EMP (14 lines) DEPT (4 lines)
DEPTNO: 10,20,30,...     DEPTNO: 10,20,30,40

INNER JOIN:
→ Nur Zeilen wo EMP.DEPTNO in DEPT vorkommt
→ Department 40 (OPERATIONS) has no employees → is not output
→ All 14 EMP lines have a matching DEPT entry → all 14 remain

3.2 ANSI syntax (recommended)

-- Basic syntax
SELECT e.ENAME, e.JOB, e.SAL, d.DNAME, d.LOC
FROM   EMP e
       INNER JOIN DEPT d ON e.DEPTNO = d.DEPTNO;

-- INNER is optional – JOIN alone means INNER JOIN
SELECT e.ENAME, e.JOB, e.SAL, d.DNAME, d.LOC
FROM   EMP e
       JOIN DEPT d ON e.DEPTNO = d.DEPTNO;

Result (14 lines):

ENAME   JOB        SAL   DNAME       LOC
------  ---------  ----  ----------  --------
SMITH   CLERK       800  RESEARCH    DALLAS
ALLEN   SALESMAN   1600  SALES       CHICAGO
WARD    SALESMAN   1250  SALES       CHICAGO
...
KING    PRESIDENT  5000  ACCOUNTING  NEW YORK

3.3 Combine with WHERE

-- Only employees from DALLAS with salaries > 1500
SELECT e.ENAME, e.SAL, d.LOC
FROM   EMP e
       JOIN DEPT d ON e.DEPTNO = d.DEPTNO
WHERE  d.LOC = 'DALLAS'
  AND  e.SAL > 1500;

-- Just managers and their location
SELECT e.ENAME, e.JOB, d.DNAME, d.LOC
FROM   EMP e
       JOIN DEPT d ON e.DEPTNO = d.DEPTNO
WHERE  e.JOB = 'MANAGER'
ORDER BY d.DNAME;

3.4 NATURAL JOIN and USING

Oracle knows two short forms for the INNER JOIN:

-- NATURAL JOIN: automatically joins all columns with the same name
-- (Caution: links ALL common columns – may be unintentional!)
SELECT ENAME, DNAME
FROM   EMP NATURAL JOIN DEPT;

-- USING: explizit eine gemeinsame Spalte angeben
SELECT ENAME, DNAME, LOC
FROM   EMP JOIN DEPT USING (DEPTNO);
-- Advantage: DEPTNO cannot be qualified in SELECT (no e.DEPTNO)

Teaching Recommendation: Always use ON syntax - it is the clearest and most flexible.

4. Table aliases and column qualification

4.1 Why table aliases?

For joins, columns that exist in multiple tables must be qualified - otherwise Oracle won't know which table is meant:

-- ERROR: DEPTNO is in EMP and DEPT – which one is meant?
SELECT ENAME, DEPTNO, DNAME
FROM   EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
-- ORA-00918: column ambiguously defined

-- RICHTIG: Spalte qualifizieren
SELECT e.ENAME, e.DEPTNO, d.DNAME
FROM   EMP e JOIN DEPT d ON e.DEPTNO = d.DEPTNO;

4.2 Rules for table aliases

-- Alias direkt nach dem Tabellennamen (kein AS!)
FROM EMP e            -- Kurzform (empfohlen)
FROM EMP AS e         -- Mit AS (funktioniert in Oracle auch)

-- Sobald ein Alias vergeben ist, MUSS er verwendet werden
-- (The table name without an alias is then no longer valid)
SELECT EMP.ENAME ...  -- ERROR when alias 'e' is assigned
SELECT e.ENAME ...    -- RICHTIG

4.3 Make column names unique

-- If column name occurs in only one table: Qualification optional
SELECT e.ENAME,         -- only in EMP → e. optional but good style
       e.SAL,           -- only in EMP
       e.DEPTNO,        -- in EMP und DEPT → muss qualifiziert werden
       d.DNAME,         -- only in DEPT
       d.LOC            -- only in DEPT
FROM   EMP e JOIN DEPT d ON e.DEPTNO = d.DEPTNO;

5. JOIN across multiple tables

5.1 Link three tables

-- EMP + DEPT + SALGRADE: Employee with department and salary class
SELECT e.ENAME,
       e.SAL,
       d.DNAME,
       d.LOC,
       s.GRADE         AS gehaltsklasse
FROM   EMP     e
       JOIN DEPT     d ON e.DEPTNO = d.DEPTNO
       JOIN SALGRADE s ON e.SAL BETWEEN s.LOSAL AND s.HISAL
ORDER BY s.GRADE, e.SAL;

SALGRADE uses a range join (BETWEEN) because there is no shared key - more on this in Chapter 10 (NON-EQUIJOIN).

5.2 Order of joins

Oracle performs joins from left to right, but the optimizer can choose the order itself. The following applies to readability:

-- Recommendation: Main table first, then dependent tables
FROM  EMP e
      JOIN DEPT     d ON e.DEPTNO  = d.DEPTNO
      JOIN SALGRADE s ON e.SAL BETWEEN s.LOSAL AND s.HISAL

-- Mit WHERE und GROUP BY kombinieren
SELECT d.DNAME,
       s.GRADE,
       COUNT(*)       AS anzahl,
       AVG(e.SAL)     AS avg_gehalt
FROM   EMP     e
       JOIN DEPT     d ON e.DEPTNO = d.DEPTNO
       JOIN SALGRADE s ON e.SAL BETWEEN s.LOSAL AND s.HISAL
GROUP BY d.DNAME, s.GRADE
ORDER BY d.DNAME, s.GRADE;

6. LEFT OUTER JOIN

6.1 Basic principle

The LEFT OUTER JOIN returns all rows in the left table - even if there is no matching row in the right table. Missing values ​​are filled with NULL:

EMP (links)          DEPT (rechts)
─────────────        ─────────────
DEPTNO 10      →     DEPTNO 10  ✓ → Zeile wird ausgegeben
DEPTNO 20      →     DEPTNO 20  ✓ → Zeile wird ausgegeben
DEPTNO 30      →     DEPTNO 30  ✓ → Zeile wird ausgegeben
DEPTNO 40 → has no EMP entries

LEFT JOIN: alle EMP-Zeilen (14) + NULLs wo kein DEPT passt

6.2 Example: All departments, including empty ones

-- DEPT is on the left → all departments appear, even without employees
SELECT d.DEPTNO,
       d.DNAME,
       e.ENAME,
       e.SAL
FROM   DEPT d
       LEFT JOIN EMP e ON d.DEPTNO = e.DEPTNO
ORDER BY d.DEPTNO, e.ENAME;

Result (excerpt):

DEPTNO  DNAME       ENAME   SAL
------  ----------  ------  ----
    10  ACCOUNTING  CLARK   2450
    10  ACCOUNTING  KING    5000
    10  ACCOUNTING  MILLER  1300
    20  RESEARCH    ADAMS   1100
    ...
    40  OPERATIONS  NULL    NULL   ← Abteilung ohne Mitarbeiter!

6.3 Only lines without correspondence

-- Only departments WITHOUT employees
SELECT d.DEPTNO, d.DNAME
FROM   DEPT d
       LEFT JOIN EMP e ON d.DEPTNO = e.DEPTNO
WHERE  e.EMPNO IS NULL;

-- Ergebnis:
-- 40   OPERATIONS

7. RIGHT OUTER JOIN

7.1 Basic principle

The RIGHT OUTER JOIN returns all rows of the right table - even if there is no matching row in the left table:

-- All departments appear (DEPT is on the right)
SELECT e.ENAME,
       e.SAL,
       d.DEPTNO,
       d.DNAME
FROM   EMP e
       RIGHT JOIN DEPT d ON e.DEPTNO = d.DEPTNO
ORDER BY d.DEPTNO;

Note: A RIGHT JOIN can always be replaced by a LEFT JOIN with the table order swapped. In practice RIGHT JOIN is rarely used.

-- Diese beiden Abfragen liefern dasselbe Ergebnis:
FROM EMP  e RIGHT JOIN DEPT d ON e.DEPTNO = d.DEPTNO
FROM DEPT d LEFT  JOIN EMP  e ON d.DEPTNO = e.DEPTNO

8. FULL OUTER JOIN

8.1 Basic principle

The FULL OUTER JOIN returns all rows of both tables - regardless of whether a correspondence exists or not:

-- All employees AND all departments
-- Employee without department: DNAME is NULL
-- Departments without employees: ENAME is NULL
SELECT e.ENAME,
       e.DEPTNO  AS emp_deptno,
       d.DEPTNO  AS dept_deptno,
       d.DNAME
FROM   EMP  e
       FULL OUTER JOIN DEPT d ON e.DEPTNO = d.DEPTNO
ORDER BY d.DEPTNO NULLS LAST, e.ENAME;

8.2 Practical example: Data consistency check

-- Are there employees without a valid department OR departments without employees?
SELECT NVL(TO_CHAR(e.EMPNO), 'kein MA')   AS empno,
       NVL(e.ENAME, '–')                   AS mitarbeiter,
       NVL(TO_CHAR(d.DEPTNO), 'keine ABT') AS dept,
       NVL(d.DNAME, '–')                   AS abteilung,
       CASE
           WHEN e.EMPNO  IS NULL THEN 'Abteilung ohne Mitarbeiter'
           WHEN d.DEPTNO IS NULL THEN 'Mitarbeiter ohne Abteilung'
           ELSE 'OK'
       END AS status
FROM   EMP  e
       FULL OUTER JOIN DEPT d ON e.DEPTNO = d.DEPTNO
WHERE  e.EMPNO IS NULL OR d.DEPTNO IS NULL;

9. SELF JOIN – A table with itself

9.1 Why a self join?

The EMP table contains the superior's EMPNO in the MGR column - i.e. a foreign key to the same table. To display the manager's name, EMP must be linked to itself:

EMP line (employee): EMPNO=7369, ENAME='SMITH', MGR=7902
EMP-Zeile (Vorgesetzter):   EMPNO=7902, ENAME='FORD',   MGR=7566

9.2 Implementation

-- Employees with their superiors
-- Two aliases for the same table!
SELECT e.ENAME          AS mitarbeiter,
       e.JOB            AS beruf,
       m.ENAME          AS vorgesetzter
FROM   EMP e
       JOIN EMP m ON e.MGR = m.EMPNO
ORDER BY m.ENAME, e.ENAME;

Result (excerpt):

EMPLOYEE PROFESSION SUPERVISOR
-----------  ---------  ------------
FORD         ANALYST    JONES
SCOTT        ANALYST    JONES
SMITH        CLERK      FORD
ADAMS        CLERK      SCOTT
ALLEN        SALESMAN   BLAKE
...

Note: KING has no supervisor (MGR is ZERO). With INNER JOIN KING is not output. For all employees including KING → Use LEFT JOIN!

9.3 Self Join with LEFT JOIN for all employees

-- KING wird mit LEFT JOIN ebenfalls ausgegeben
SELECT e.ENAME                             AS mitarbeiter,
       NVL(m.ENAME, '-- kein Vorgesetzter --') AS vorgesetzter,
       e.SAL,
       m.SAL                              AS gehalt_vorgesetzter
FROM   EMP e
       LEFT JOIN EMP m ON e.MGR = m.EMPNO
ORDER BY m.ENAME NULLS LAST, e.ENAME;

9.4 Multiple levels of hierarchy

-- Employees, their direct supervisor and the supervisor's supervisor
SELECT e.ENAME    AS mitarbeiter,
       m.ENAME    AS vorgesetzter,
       mm.ENAME   AS chef_des_chefs
FROM   EMP e
       LEFT JOIN EMP m  ON e.MGR  = m.EMPNO
       LEFT JOIN EMP mm ON m.MGR  = mm.EMPNO
ORDER BY mm.ENAME NULLS LAST, m.ENAME NULLS LAST, e.ENAME;

10. NON-EQUIJOIN – Range join

10.1 What is a non-equijoin?

A Non-Equijoin uses another operator in the join condition (BETWEEN, <, >, <=, >=, !=) instead of the equality operator (=):

10.2 Determine salary class

The SALGRADE table does not contain a common key column with EMP. Instead, the range in which the salary falls is checked:

-- Pay grade for each employee
SELECT e.ENAME,
       e.SAL,
       s.GRADE     AS gehaltsklasse,
       s.LOSAL,
       s.HISAL
FROM   EMP e
       JOIN SALGRADE s ON e.SAL BETWEEN s.LOSAL AND s.HISAL
ORDER BY s.GRADE, e.SAL;

Result:

ENAME   SAL   GEHALTSKLASSE  LOSAL  HISAL
------  ----  -------------  -----  -----
SMITH    800              1    700   1200
JAMES    950              1    700   1200
ADAMS   1100              1    700   1200
WARD    1250              2   1201   1400
MARTIN  1250              2   1201   1400
MILLER  1300              2   1201   1400
...
KING    5000              5   3001   9999

10.3 Non-equijoin combined with INNER JOIN

-- Employee with department AND salary class
SELECT e.ENAME,
       d.DNAME,
       e.SAL,
       s.GRADE
FROM   EMP      e
       JOIN DEPT     d ON e.DEPTNO = d.DEPTNO
       JOIN SALGRADE s ON e.SAL BETWEEN s.LOSAL AND s.HISAL
WHERE  s.GRADE >= 3
ORDER BY s.GRADE DESC, e.SAL DESC;

11. Oracle's own JOIN syntax (+)

11.1 The old Oracle syntax

Before the SQL 99 standard, Oracle had its own JOIN syntax using the (+) operator. This is still used in many older scripts and documentation:

-- Alte Syntax (Oracle-spezifisch)
SELECT e.ENAME, d.DNAME
FROM   EMP e, DEPT d
WHERE  e.DEPTNO = d.DEPTNO;          -- INNER JOIN (alte Syntax)

-- LEFT OUTER JOIN (alt): (+) auf der Seite, die NULL-Werte liefern kann
SELECT e.ENAME, d.DNAME
FROM   EMP e, DEPT d
WHERE  e.DEPTNO = d.DEPTNO(+);       -- all EMP, even without DEPT
                           ───
                           Hier hat DEPT die fehlenden Werte → (+) rechts

-- RIGHT OUTER JOIN (alt): (+) auf der linken Seite
SELECT e.ENAME, d.DNAME
FROM   EMP e, DEPT d
WHERE  e.DEPTNO(+) = d.DEPTNO;      -- all DEPT, even without EMP

11.2 Comparison old / new

Recommendation: Always use ANSI syntax (JOIN ... ON) for new queries. Know the old syntax to be able to read existing scripts.

11.3 Limitations of the old syntax

-- (1) FULL OUTER JOIN is not possible with (+).
-- (2) (+) cannot appear on both sides
-- (3) Combination with OR is restricted
-- (4) Subqueries in the join condition are not possible

-- Self Join (alte Syntax)
SELECT e.ENAME, m.ENAME AS chef
FROM   EMP e, EMP m
WHERE  e.MGR = m.EMPNO(+);    -- LEFT JOIN (KING hat keinen Chef)

12. Summary and outlook

Overview of all join types

-- INNER JOIN: only shared rows
FROM A JOIN B ON A.id = B.id

-- LEFT OUTER JOIN: all from A, matching ones from B (otherwise NULL)
FROM A LEFT JOIN B ON A.id = B.id

-- RIGHT OUTER JOIN: matching from A (otherwise NULL), all from B
FROM A RIGHT JOIN B ON A.id = B.id

-- FULL OUTER JOIN: all from A and all from B
FROM A FULL OUTER JOIN B ON A.id = B.id

-- CROSS JOIN: kartesisches Produkt (jede × jede)
FROM A CROSS JOIN B

-- SELF JOIN: table with itself (two aliases!)
FROM EMP e JOIN EMP m ON e.MGR = m.EMPNO

-- NON-EQUIJOIN: kein = in der Bedingung
FROM EMP e JOIN SALGRADE s ON e.SAL BETWEEN s.LOSAL AND s.HISAL

Decision support

Which lines should appear?
│
├── Nur Zeilen mit Entsprechung in beiden Tabellen
│     → INNER JOIN
│
├── All rows of the first (left) table
│     → LEFT OUTER JOIN
│
├── All rows of the second (right) table
│     → RIGHT OUTER JOIN
│
├── All rows of both tables
│     → FULL OUTER JOIN
│
├── Table with itself (hierarchy, self-reference)
│     → SELF JOIN (zwei Aliase!)
│
└── Linking via area instead of equality
      → NON-EQUIJOIN (BETWEEN, <, >)

Checklist

Explain and avoid Cartesian product
INNER JOIN: Syntax, Bedeutung, Beispiel
Tabellenaliase korrekt einsetzen
Qualify columns in case of ambiguity
LEFT / RIGHT / FULL OUTER JOIN unterscheiden
Self Join mit zwei Aliasen für dieselbe Tabelle
Non-Equijoin mit BETWEEN (EMP + SALGRADE)
Be able to read old Oracle syntax (+).
Drei Tabellen mit JOIN verknüpfen
WHERE nach JOIN korrekt anwenden

Typical exam tasks

-- 1. Name, occupation, salary and department name of all employees
SELECT e.ENAME, e.JOB, e.SAL, d.DNAME
FROM   EMP e JOIN DEPT d ON e.DEPTNO = d.DEPTNO
ORDER BY d.DNAME, e.ENAME;

-- 2. All departments with number of employees (including empty departments)
SELECT d.DNAME, COUNT(e.EMPNO) AS anzahl
FROM   DEPT d LEFT JOIN EMP e ON d.DEPTNO = e.DEPTNO
GROUP BY d.DNAME
ORDER BY anzahl DESC;

-- 3. Employees with their supervisor (only employees who have one)
SELECT e.ENAME AS mitarbeiter, m.ENAME AS vorgesetzter
FROM   EMP e JOIN EMP m ON e.MGR = m.EMPNO
ORDER BY m.ENAME, e.ENAME;

-- 4. Name, salary, department name, and salary grade
SELECT e.ENAME, e.SAL, d.DNAME, s.GRADE
FROM   EMP e
       JOIN DEPT     d ON e.DEPTNO = d.DEPTNO
       JOIN SALGRADE s ON e.SAL BETWEEN s.LOSAL AND s.HISAL
ORDER BY s.GRADE, e.SAL;

-- 5. Employees whose salary is higher than that of their boss
SELECT e.ENAME AS mitarbeiter, e.SAL,
       m.ENAME AS vorgesetzter, m.SAL AS chef_sal
FROM   EMP e JOIN EMP m ON e.MGR = m.EMPNO
WHERE  e.SAL > m.SAL;

Outlook: Next topics

• Subqueries: Subqueries in WHERE, FROM and SELECT
• DML: INSERT, UPDATE, DELETE, MERGE
• DDL: CREATE TABLE, ALTER TABLE, Constraints
• PL/SQL: Procedures, functions, triggers

HTL Pinkafeld – IF/IT | Oracle SQL | 11th grade