Introduction

SQL joins are vital to relational database operations, which allow users to combine data from multiple tables based on shared keys.

In R, the dplyr package provides an intuitive syntax for performing these operations.

Load Libraries

Connect to the Database

In this example, I’m using:

AdventureWorks2017
SQL Server 2019

Code

# Connect to the Database
con <- DBI::dbConnect(odbc::odbc(), "AdventureWorks", timeout = 15)

Data

AdventureWorks

Sales Header

SalesOrderHeader

Code

sales_header <- dbGetQuery(con,
                            "SELECT * 
                            FROM Sales.SalesOrderHeader"
                            )

Sales Detail

SalesOrderDetail

Code

sales_detail <- dbGetQuery(con,
                            "SELECT *
                            FROM Sales.SalesOrderDetail"
                            )

Joins

Output: Rows & Columns

Inner Join

Code

sales_header |>
  inner_join(sales_detail, 
             by = "SalesOrderID") |> 
  dim()

[1] 121317     36

Left Join

Code

sales_header |>
  left_join(sales_detail,
            by = "SalesOrderID") |> 
  dim()

[1] 121317     36

Right Join

Code

sales_header |>
  right_join(sales_detail,
             by = "SalesOrderID") |> 
  dim()

[1] 121317     36

Code

sales_header |>
  full_join(sales_detail, by = "SalesOrderID") |> 
  dim()

[1] 121317     36

Full Join

Code

sales_header |>
  full_join(sales_detail, 
            by = "SalesOrderID") |> 
  dim()

[1] 121317     36

Cross Join

Code

# Not Executed 
sales_header |>
  mutate(dummy = 1) |>
  full_join(sales_detail |> mutate(dummy = 1), by = "dummy") |>
  select(-dummy) |> dim()

This full_join generated 3817239405 rows, which is 1.67 billion rows more than R can handle.

$3817239405-2147483647 = 1669755758$

Semi Join

Code

sales_header |>
  semi_join(sales_detail, 
            by = "SalesOrderID")|> 
  dim()

[1] 31465    26

Anti Join

Code

# Rows in header not in detail
sales_header |>
  anti_join(sales_detail, 
            by = "SalesOrderID") |> 
  nrow()

[1] 0

Code

# Rows in detail not in header
sales_detail |>
  anti_join(sales_header, 
            by = "SalesOrderID") |> 
  nrow()

[1] 0

Disconnect DB

Code

dbDisconnect(con) # Disconnect db