FULL JOINs

Joining Data in SQL

Maham Faisal Khan

Senior Content Developer, DataCamp

FULL JOIN initial diagram

  • A FULL JOIN combines a LEFT JOIN and a RIGHT JOIN
    Diagram for a FULL JOIN ON the id field

A diagram showing two tables, left table and right table, with arrows pointing to the records that match on the id column in both tables. Records that are not of interest to full join have been faded out.

Joining Data in SQL

FULL JOIN diagram

A diagram showing two tables, left table and right table, and the result set after a full join is performed on these tables. Records that are not of interest to full join have been grayed out.

Joining Data in SQL

FULL JOIN syntax

SELECT left_table.id AS L_id,
       right_table.id AS R_id,
       left_table.val AS L_val,
       right_table.val AS R_val
FROM left_table
FULL JOIN right_table
USING (id);

Note. The keyword FULL OUTER JOIN can also be used.

Joining Data in SQL

FULL JOIN example using leaders database

SELECT p1.country AS country, prime_minister, president
Joining Data in SQL

FULL JOIN example using leaders database

SELECT p1.country AS country, prime_minister, president
FROM prime_ministers AS p1
Joining Data in SQL

FULL JOIN example using leaders database

SELECT p1.country AS country, prime_minister, president
FROM prime_ministers AS p1
FULL JOIN presidents AS p2
Joining Data in SQL

FULL JOIN example using leaders database

SELECT p1.country AS country, prime_minister, president
FROM prime_ministers AS p1
FULL JOIN presidents AS p2
ON p1.country = p2.country
LIMIT 10;
Joining Data in SQL

FULL JOIN example using leaders database

| country        | prime_minister   | president               |
|----------------|------------------|-------------------------|
| Egypt          | Mostafa Madbouly | Abdel Fattah el-Sisi    |
| Portugal       | Luís Montenegro  | Marcelo Rebelo de Sousa |
| Pakistan       | Shehbaz Sharif   | Asif Ali Zardari        |
| United Kingdom | Keir Starmer     | null                    |   
| USA            | null             | Joe Biden               |
| Uruguay        | null             | Luis Lacalle Pou        |   
| Chile          | null             | Gabriel Boric           |   
| India          | Narendra Modi    | Droupadi Murmu          |
| Norway         | Jonas Gahr Støre | null                    |
| Brunei         | Hassanal Bolkiah | null                    |
Joining Data in SQL

Let's practice!

Joining Data in SQL

Preparing Video For Download...