The ins and outs of INNER JOIN

Joining Data in SQL

Maham Faisal Khan

Senior Content Developer, DataCamp

The ins and outs of INNER JOINs

A diagram showing two tables: left table and right table. Matching id values in left and right table are displayed using the same colors.

Joining Data in SQL

The ins and outs of INNER JOINs

  • INNER JOIN looks for records in both tables which match on a given field
Diagram for an INNER JOIN on the id field

A diagram showing two tables: left_table and right_table. Matching id values in the left and right table are displayed using the same colors and are connected with arrows.

Joining Data in SQL

The ins and outs of INNER JOINs

Diagram for an INNER JOIN ON the id field

A diagram showing two tables: left_table and right_table. Matching id values in left and right table are displayed using the same colors and connected with arrows. Records that are not of interest to INNER JOIN have been faded out.

Joining Data in SQL

The ins and outs of INNER JOINs

Diagram for an INNER JOIN ON the id field

A diagram showing two tables: left_table and right_table. The result of joining the two queries is shown on the right, returning only the records that matched on the id column for both tables.

Joining Data in SQL

The leadership database schema

World leaders up to 2024: A SQL schema showing three tables in the leadership database: presidents, prime_ministers, and prime_minister_terms. presidents and prime_minister can be joined on the country field, whereas prime_ministers and prime_minister terms can be joined on the prime_minister field.

Joining Data in SQL

At the presidents table

SELECT * 
FROM presidents;

| country  | continent     | president               |
|----------|---------------|-------------------------|
| Egypt    | Africa        | Abdel Fattah el-Sisi    |
| Portugal | Europe        | Marcelo Rebelo de Sousa | 
| USA      | North America | Joe Biden               |
| Uruguay  | South America | Luis Lacalle Pou        |
| Pakistan | Asia          | Asif Ali Zardari        |
| Chile    | South America | Gabriel Boric           |
| India    | Asia          | Droupadi Murmu          |
Joining Data in SQL

Meet the prime ministers

The prime_ministers table

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

Joining Data in SQL

Prime ministers, meet the presidents

The presidents table

Two tables shown side-by-side: presidents and prime_minister. Matching columns are colored in the two tables.

The prime_ministers table

Two tables shown side-by-side: presidents and prime_minister. Matching columns are colored in the two tables.

Joining Data in SQL

Our first INNER JOIN

--Inner join of presidents and prime_ministers, joining on country

SELECT prime_ministers.country, prime_ministers.continent, prime_minister, president

FROM presidents

INNER JOIN prime_ministers

ON  presidents.country = prime_ministers.country;

Note. The table.column_name format must be used when selecting columns that exist in both tables to avoid a SQL error.

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

Aliasing tables

--Inner join of presidents and prime_ministers, joining on country

SELECT p2.country, p2.continent, prime_minister, president

FROM presidents AS p1
INNER JOIN prime_ministers AS p2

ON p1.country = p2.country;

| country        | continent | prime_minister   | president               |
|----------------|-----------|------------------|-------------------------|
| Egypt          | Africa    | Mostafa Madbouly | Abdel Fattah el-Sisi    |
| Portugal       | Europe    | Luís Montenegro  | Marcelo Rebelo de Sousa |
| Pakistan       | Asia      | Shehbaz Sharif   | Asif Ali Zardari        |
| India          | Asia      | Narendra Modi    | Ram Nath Kovind         |

Aliases can be used in the table.column_name syntax in SELECT and ON clauses.

Joining Data in SQL

Using USING

--Inner join of presidents and prime_ministers, joining on country
SELECT p2.country, p2.continent, prime_minister, president
FROM presidents AS p1
INNER JOIN prime_ministers AS p2
USING(country);

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

Let's practice!

Joining Data in SQL

Preparing Video For Download...