LEFT and RIGHT JOINs

Joining Data in SQL

Maham Faisal Khan

Senior Content Developer, Data Science

INNER JOIN diagram

Diagram for an INNER JOIN ON the id field

A diagram repeated from a previous lesson, showing two tables, left_table and right_table, and the result set after an inner join is performed on the two tables. Records that are not of interest to inner join have been faded out.

Joining Data in SQL

LEFT JOIN initial diagram

  • LEFT JOIN will return all records in the left table, and those records in the right table that match on the joining field provided
    Diagram for a LEFT JOIN ON the id field

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

Joining Data in SQL

LEFT JOIN diagram

Diagram for a LEFT JOIN ON the id field

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

Joining Data in SQL

LEFT JOIN syntax

SELECT p1.country, prime_minister, president
FROM prime_ministers AS p1
LEFT JOIN presidents AS p2
USING(country);

Note. LEFT JOIN can also be written as LEFT OUTER JOIN.

| 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                    | 
| India          | Narendra Modi     | Droupadi Murmu          |
| Australia      | Anthony Albanese  | null                    | 
...
Joining Data in SQL

RIGHT JOIN

Diagram for a RIGHT JOIN ON the id field

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

Joining Data in SQL

RIGHT JOIN syntax

SELECT *
FROM left_table
RIGHT JOIN right_table
ON left_table.id = right_table.id;

Note. RIGHT JOIN can also be written as RIGHT OUTER JOIN.

Joining Data in SQL

RIGHT JOIN with presidents and prime ministers

SELECT p1.country, prime_minister, president
FROM prime_ministers AS p1
RIGHT JOIN presidents AS p2
USING(country);

| country        | prime_minister   | president               |
|----------------|------------------|-------------------------|
| Egypt          | Mostafa Madbouly | Abdel Fattah el-Sisi    |
| Portugal       | Luís Montenegro  | Marcelo Rebelo de Sousa |
| USA            | null             | Joe Biden               |
| Uruguay        | null             | Luis Lacalle Pou        |     
| Pakistan       | Shehbaz Sharif   | Asif Ali Zardari        |
| Chile          | null             | Gabriel Boric           |      
| India          | Narendra Modi    | Droupadi Murmu          |
Joining Data in SQL

LEFT JOIN or RIGHT JOIN?

  • RIGHT JOIN is less commonly used than LEFT JOIN
  • Any RIGHT JOIN can be re-written as a LEFT JOIN
  • LEFT JOIN feels more intuitive to users when typing from left to right
Joining Data in SQL

Let's practice!

Joining Data in SQL

Preparing Video For Download...