SQL Joins & R

SQL
Databases
dplyr


Introduction

SQL joins are vital to relational database operations, which allow users to combine data from multiple tables based on shared keys. In this example, we will use R as an interface to extract data from Adventure Works and process a range of joins similar to someone using a SQL GUI.

In R, the dplyr package provides an intuitive syntax for performing these operations. It always made the most sense for me to extract the data into the platform that I’m creating analytical reports and data visualizations.


Load Libraries


Code 
library(DBI)       # Unified DB Interface
library(odbc)      # Defines ODBC driver  
library(dplyr)     # Grammar of Data Manipulation


Connect to the SQL Database


Even though I’m technically using Quarto and R, I’m using:

  • AdventureWorks2017 Database
  • SQL Server 2019


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

Data Exploration



Sales Header

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


Code 
sales_header |> reactable::reactable(
    searchable = TRUE,
    filterable = TRUE,
    highlight = TRUE,
    striped = TRUE,
    defaultPageSize = 10
  )


Sales Detail

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


Code 
sales_detail |> reactable::reactable(
    searchable = TRUE,
    filterable = TRUE,
    highlight = TRUE,
    striped = TRUE,
    defaultPageSize = 10
  )

Data Joins

Now that we have the sample data saved. We will run through a variety of joins to serve as examples and illustrate the range of different outputs generated. Only the dimensiosn are printed below using the dim() function.

  • Output: Rows & Columns


Inner Join

Code 
sales_header |>
  inner_join(sales_detail, 
             by = "SalesOrderID") |> 
  dim()
[1] 121317     36


  • Inner join means only the rows where SalesOrderID exists in both tables will be kept.
  • If a SalesOrderID is missing from either table, that record is excluded from the result.


Left Join

Code 
sales_header |>
  left_join(sales_detail,
            by = "SalesOrderID") |> 
  dim()
[1] 121317     36


  • Left join means all rows from sales_header are kept, even if there’s no matching SalesOrderID in sales_detail.
  • If there’s no match in sales_detail, the columns from sales_detail will be filled with NA for that row.


Right Join

Code 
sales_header |>
  right_join(sales_detail,
             by = "SalesOrderID") |> 
  dim()
[1] 121317     36


Full Join

Code 
sales_header |>
  full_join(sales_detail, 
            by = "SalesOrderID") |> 
  dim()
[1] 121317     36


  • Full join means all rows from both sales_header and sales_detail are kept, whether or not there’s a match.
  • If a SalesOrderID is missing from one side, the missing side’s columns are filled with NA.



Semi Join

Code 
sales_header |>
  semi_join(sales_detail, 
            by = "SalesOrderID") |> 
  dim()
[1] 31465    26


  • Semi join keeps only the rows from sales_header that have a matching SalesOrderID in sales_detail.
  • No columns from sales_detail are added — it’s just a filter on sales_header.

Anti Join


Code 
# Rows in header not in detail
sales_header |>
  anti_join(sales_detail, 
            by = "SalesOrderID") |> 
  nrow()
[1] 0


  • Anti join keeps only the rows from sales_header that do not have a matching SalesOrderID in sales_detail.
  • It’s like a filter for non-matches, and no columns from sales_detail are added.


Anti Join Swap

Code 
# Rows in detail not in header
sales_detail |>
  anti_join(sales_header, 
            by = "SalesOrderID") |> 
  nrow() 
[1] 0
  • Anti join keeps only the rows from sales_detail that do not have a matching SalesOrderID in sales_header.
  • It filters for sales_detail rows that are missing from sales_header, with no columns from sales_header added.


  • By swapping the tables in the anti_join, it lets us check both directions for missing stuff. Sometimes a SalesOrderID might exist in one table but not the other.


Cross Join

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


  • Here we attempt a full join between sales_header and sales_detail, but not by SalesOrderID — will will create a dummy variable using mutate().
  • A full join on a constant (dummy = 1) creates a cartesian product, meaning every row from sales_header is matched with every row from sales_detail.
  • This full_join tried to generate 3,817,239,405 rows, which is 1.67 billion rows more than R’s maximum data frame size of 2,147,483,647. Calculation: (\(3817239405-2147483647 = 1669755758\))


Cartesian Joins

A Cartesian product (also called a Cartesian join) happens when you join two tables without matching keys — meaning every row from the first table is matched with every row from the second table.

  • If Table A has n rows and Table B has m rows, the result will have n × m rows.
  • Even small tables can explode fast. For example:
    • \(1,000 rows × 1,000 rows = 1 million rows\)
    • \(10,000 rows × 10,000 rows = 100 million rows\)

When you create a full join on a constant value (like dummy = 1), you are forcing a Cartesian product because every row is considered a match.


Why Cartesian Joins Can Be Dangerous

  • Memory Explosion: Since rows grow multiplicatively, a careless cartesian join can quickly create billions of rows and crash R (or your whole machine).
  • Meaningless Matches: Without a real matching key, most row combinations are usually not meaningful.
  • Performance: Even if it doesn’t crash, cartesian joins are super slow and heavy.


Warning

Cartesian joins should always be filtered or limited before joining large tables.


How to Handle Cartesian Joins Safely

  • Filter first: Always narrow down your tables before joining.
  • Limit matches: Use conditions (like date windows, numeric thresholds, etc.) instead of joining everything.
  • Use crossing() from tidyr: If you actually want all combinations but need it cleaner.
  • Use fuzzyjoin or nested_loop_join() carefully: These libraries allow controlled, smarter matching.

When Would You Actually Want a Cartesian Join?

Sometimes a cartesian join is exactly what you need, which is to create all possible combinations between two sets of data.

Real-world examples:

  • Product Recommendations:
    • You have a list of customers and a list of products.
    • You want to create a table of every possible customer-product pair to predict purchase likelihood.
  • Scheduling Problems:
    • You have a list of employees and a list of shifts.
    • You generate all possible employee-shift combinations before applying scheduling rules.
  • Simulations and Scenarios:
    • You have a set of assumptions (interest rates, inflation rates, etc.) and a set of years.
    • You want every scenario combination to model projections under different conditions.
  • Matching or Ranking Exercises:
    • You want to rank every player against every other player, or every investment opportunity against every portfolio strategy.


Joins Takeaways

inner_join()

  • Keeps only matching rows between two tables.
  • Both sides must match on the key.

Real-world examples:

  • Finding customers who placed orders.
  • Matching employees to completed performance reviews.
  • Joining student names to submitted assignments only.


left_join()

  • Keeps all rows from the left table.
  • Fills NAs if no match found on the right table.

Real-world examples:

  • List of customers, showing latest order (even if no order yet).
  • Employee list with optional training completions.
  • Product catalog with optional inventory records.


right_join()

  • Keeps all rows from the right table.
  • Fills NAs if no match found on the left table.

Real-world examples:

  • List of sales transactions, even if the salesperson info is missing.
  • Show all support tickets, even if no assigned agent yet.


full_join()

  • Keeps all rows from both tables.
  • Fills NAs wherever matches are missing on either side.

Real-world examples:

  • Merging two different customer lists from old systems.
  • Combining two surveys where some respondents answered only one survey.


semi_join()

  • Filters left table to only rows with a match in the right table.
  • No new columns added from the right table.

Real-world examples:

  • Find customers who made a purchase.
  • Get a list of employees who completed onboarding.


anti_join()

  • Filters left table to rows with no match in the right table.
  • Useful for finding missing data.

Real-world examples:

  • Customers who never placed an order.
  • Products not yet sold this month.
  • Students who didn’t submit an assignment.


Cartesian Product

  • All combinations of rows from both tables.
  • Grows n × m rows, so can get massive.

Real-world examples:

  • All possible customer-product combinations for recommendations.
  • All possible team matchups in a tournament bracket.
  • Generating every pricing scenario for modeling sensitivity to variables.


Disconnect DB

Don’t forget to disconnect from DB


Code 
dbDisconnect(con) # Disconnect db