Multiple joins

Joining Data in SQL

Maham Faisal Khan

Senior Content Developer, DataCamp

Joins on joins

SELECT *
FROM left_table
INNER JOIN right_table
ON left_table.id = right_table.id

INNER JOIN another_table
ON left_table.id = another_table.id;

Note. Depending on the use case, left_table or right_table can be used in the ON clause.

Joining Data in SQL

Joins on joins

The prime_minister_terms table from our database of world leaders

The prime_minister_terms table, showing two columns: the name of the prime_minister and the year they assumed office.

Joining Data in SQL

What to join first?

SELECT p1.country, p1.continent, 
       president, prime_minister
FROM prime_ministers as p1
INNER JOIN presidents as p2
USING(country);

| country  | continent     | president               | prime_minister   |
| -------- | ------------- | ----------------------- | ---------------- |
| Egypt    | Africa        | Abdel Fattah el-Sisi    | Mostafa Madbouly |
| Portugal | Europe        | Marcelo Rebelo de Sousa | Luís Montenegro  |
| Pakistan | Asia          | Asif Ali Zardari        | Shehbaz Sharif   |
| India    | Asia          | Droupadi Murmu          | Narendra Modi    |
Joining Data in SQL

Chaining joins

A diagram showing the result set of the merge from the previous slide (presidents and prime_ministers) with a new table, prime_minister_terms. The result consists of five columns: country, continent, president, prime_minister, and pm_start.

Joining Data in SQL

Chaining joins

-- SQL query for chaining inner joins
SELECT 
    p1.country, 
    p1.continent, 
    president, 
    prime_minister, 
    pm_start
FROM prime_ministers as p1
INNER JOIN presidents as p2
USING(country)

INNER JOIN prime_minister_terms as p3
USING(prime_minister);
Joining Data in SQL

What are we joining ON?

SELECT * 
FROM left_table
INNER JOIN right_table
ON left_table.id = right_table.id;
Diagram for an INNER JOIN ON the id field

A diagram showing that when matching on one column, sometimes the joining field from left table finds multiple matches in the joining field from right table. The result set has multiple records for each id in the right table that matched with the left table.

Joining Data in SQL

Joining on multiple keys

SELECT * 
FROM left_table
INNER JOIN right_table
ON left_table.id = right_table.id
    AND left_table.date = right_table.date;
Diagram for an INNER JOIN ON the id AND date field

A diagram showing that the number of records returned in the result set can be reduced if multiple joining fields are used. Here, both id and date are used to join the two tables.

Joining Data in SQL

Let's practice!

Joining Data in SQL

Preparing Video For Download...