Self joins

Joining Data in SQL

Maham Faisal Khan

Senior Content Developer, DataCamp

Self joins

Self joins are tables joined with themselves
They can be used to compare parts of the same table

The prime_ministers table from the world leaders database

The prime_ministers table from the world leaders database, containing three columns: country, continent and prime_minister.

Prime minister, meet prime minister

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;

Prime minister, meet prime minister

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

Prime minister, meet prime minister

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;

The self joined prime ministers table

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

Let's practice!

Joining Data in SQL