The Data Professor - Right Join

What is a right join?

A right join keeps all the rows from the right table and inserts NULLs on the left when there is not a matching row found in the left table.

This Venn diagram illustrates which rows are included in a right join.

Note

A right join is the mirror of a left join.

How does a right join work?

A right join visits every row in the right table and then checks if there is a matching row in the left table. If there is, it combines the matching left and right row. If there is not, it places nulls on the left side.

Notice how the last row (id=-8, role="frenzied monster drummer", wage_per_hour=45.75) in the right table does not have a match in the left table. So, nulls are inserted into the left columns of the result.

The Data School provides a nice animation of the row by row operations for a right join.

How many records end up in the result of an inner join?

\(L =\) # of records in the left table

\(R =\) # of records in the right table

The number of records in the final left join is \(R\).

Implementing a right join in different tools

Here is how you perform a right join in R, Python, Power BI, and Excel.

R

library(dplyr)

employees %>% 
  right_join(positions, by = "muppet_id")

# A tibble: 5 × 4
  muppet_id names           role                     wage_per_hour
      <dbl> <chr>           <chr>                            <dbl>
1         1 Kermit the Frog pragmatic leader                  75  
2         2 Miss Piggy      diva pig                          85  
3         3 Fozzie the Bear stand-up comic                    25.2
4         4 Gonzo           stunt performer                   54.5
5         8 <NA>            frenzied monster drummer          45.8

Python

import pandas as pd

pd.merge(employees, positions, how='right')

   muppet_id            names                      role  wage_per_hour
0        1.0  Kermit the Frog          pragmatic leader          75.00
1        2.0       Miss Piggy                  diva pig          85.00
2        3.0  Fozzie the Bear            stand-up comic          25.25
3        4.0            Gonzo           stunt performer          54.50
4        8.0              NaN  frenzied monster drummer          45.75

Power BI

In Power BI, we invoke Power Query through the Home >> Transform data menu item.

Excel

In native Excel, there is not a true right join. As demonstrated in the left join post, we can use VLOOKUP() repeatedly to “join” multiples columns in a “left join” manner. Since a right join mirrors a left join; to perform a left join, we can simply swap the left and right tables then perform a left join with VLOOKUP(). It’s not ideal, but it illustrates how similar interchangeable left and right joins are.

Note

You can invoke Power Query in Excel (through the Data >> Get Data menu item) to perform a true right join.