Self Joins

Daten in SQL verbinden (JOIN)

Maham Faisal Khan

Senior Content Developer, DataCamp

Self Joins

Self Joins sind Tabellen, die mit sich selbst verbunden sind.
Sie können verwendet werden, um Teile derselben Tabelle zu vergleichen.

Die Tabelle prime_ministers aus der Datenbank der Staatsoberhäupter

Die Tabelle „prime_ministers“ aus der Datenbank „world leaders“ mit drei Spalten: Land, Kontinent und Premierminister.

Premierminister treffen Premierminister

SELECT 
    p1.country AS country1, 
    p2.country AS country2, 
    p1.continent
FROM prime_ministers AS p1
INNER JOIN prime_ministers AS p2

ON p1.continent = p2.continent
LIMIT 10;

Premierminister treffen Premierminister

| country1   | country2   | continent     |
|------------|------------|---------------|
| Egypt      | Egypt      | Africa        |
| Portugal   | Spain      | Europe        |
| Portugal   | Norway     | Europe        |
| Portugal   | Portugal   | Europe        |
| Vietnam    | Oman       | Asia          |
| Vietnam    | Brunei     | Asia          |
| Vietnam    | India      | Asia          |
| Vietnam    | Vietnam    | Asia          |
| Haiti      | Haiti      | North America |
| India      | Oman       | Asia          |
| India      | Brunei     | Asia          |

Premierminister treffen Premierminister

SELECT 
    p1.country AS country1, 
    p2.country AS country2, 
    p1.continent
FROM prime_ministers AS p1
INNER JOIN prime_ministers AS p2
ON p1.continent = p2.continent 

    AND p1.country <> p2.country;

Ein Self Join auf der Tabelle mit den Premierministern

|------------|------------|-------------|
| country1   | country2   | continent   |
|------------|------------|-------------|
| Portugal   | Spain      | Europe      |
| Portugal   | Norway     | Europe      |
| Vietnam    | Oman       | Asia        |
| Vietnam    | Brunei     | Asia        |
| Vietnam    | India      | Asia        |
| India      | Oman       | Asia        |
| India      | Brunei     | Asia        |
| India      | Vietnam    | Asia        |
| Norway     | Spain      | Europe      |
| Norway     | Portugal   | Europe      |
|------------|------------|-------------|

Lass uns üben!

Daten in SQL verbinden (JOIN)