5  JOINs

5.1 Introduction

In this chapter, we will learn to:

  • combine rows from two tables with INNER JOIN
  • keep unmatched rows with LEFT JOIN
  • avoid the SQLite RIGHT / FULL OUTER JOIN trap
  • do the same joins from R with dbplyr inner_join() / left_join()
  • filter with semi_join() and anti_join()

We will use the following R packages:

All the data sets used in this chapter can be found here and code can be run from code/ch5-joins.R in this repo.

5.2 Set Up

So far we have worked with a single table. Real databases keep related data in separate tables. Our data/ecom.sqlite file holds two small related tables besides ecom:

  • customers: 20 rows, one per customer (customer_id primary key, country)
  • orders: 20 rows, one per order (order_id primary key, customer_id foreign key into customers, amount)

Six customers (4, 16–20) have placed no orders, and one order points at a customer_id (99) that does not exist. These deliberate mismatches make the difference between join types visible.

Let us connect directly to the SQLite file:

con <- DBI::dbConnect(RSQLite::SQLite(), "data/ecom.sqlite")

A quick look at both tables:

SELECT * FROM customers LIMIT 5
5 records
customer_id country
1 United States
2 United Kingdom
3 Germany
4 France
5 Canada 
SELECT * FROM orders LIMIT 5
5 records
order_id customer_id amount
101 1 459.11
102 1 469.80
103 2 157.35
104 3 418.61
105 3 328.04

5.3 INNER JOIN

INNER JOIN returns only the rows that match in both tables. Customers without orders, and orders without customers, are dropped.

SELECT c.customer_id, c.country, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_id
LIMIT 6
6 records
customer_id country order_id amount
1 United States 101 459.11
1 United States 102 469.80
2 United Kingdom 103 157.35
2 United Kingdom 118 76.39
3 Germany 104 418.61
3 Germany 105 328.04

How many of the 20 orders survive? Nineteen – order 120 belongs to the non-existent customer 99 and is excluded:

SELECT COUNT(*) AS n FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
1 records
n
19

5.4 LEFT JOIN

LEFT JOIN keeps every row from the left table and fills NULL where the right table has no match. This is how you find customers who never ordered:

SELECT c.customer_id, c.country, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL
ORDER BY c.customer_id
6 records
customer_id country order_id amount
4 France NA NA
16 Italy NA NA
17 Portugal NA NA
18 Greece NA NA
19 Ireland NA NA
20 Belgium NA NA

The full left join has 25 rows: 19 matched order rows plus one NULL row for each of the 6 customers without orders.

5.5 The SQLite RIGHT / FULL Trap

Some tutorials teach RIGHT JOIN or FULL OUTER JOIN. Do not use them with RSQLite: the SQLite versions bundled with RSQLite (including recent ones) reject both with a syntax error such as near "RIGHT": syntax error. Postgres and other production databases accept them, so queries written against SQLite will fail silently only when you move them.

The portable workaround is to swap the tables and use LEFT JOIN:

  • instead of A RIGHT JOIN B, write B LEFT JOIN A
  • instead of A FULL OUTER JOIN B, union a LEFT JOIN in each direction

When in doubt, LEFT JOIN is the only outer join you need for this book.

5.6 JOINs with dbplyr

The same joins work from R without writing SQL. Reference both tables and use the dplyr join verbs:

customers <- dplyr::tbl(con, "customers")
orders <- dplyr::tbl(con, "orders")

inner_join(customers, orders, by = "customer_id")
# A query:  ?? x 4
# Database: sqlite 3.51.2 [F:\R\ebooks\rdbsql\data\ecom.sqlite]
   customer_id country        order_id amount
         <int> <chr>             <int>  <dbl>
 1           1 United States       101  459. 
 2           1 United States       102  470. 
 3           2 United Kingdom      103  157. 
 4           2 United Kingdom      118   76.4
 5           3 Germany             104  419. 
 6           3 Germany             105  328. 
 7           3 Germany             106  269. 
 8           5 Canada              107  374. 
 9           5 Canada              119  248  
10           6 Australia           108   84.6
11           7 Brazil              109  335. 
12           8 Japan               110  358. 
13           9 Mexico              111  240. 
14          10 Netherlands         112  365. 
15          11 Sweden              113  469. 
16          12 Norway              114  143. 
17          13 Denmark             115  242. 
18          14 Finland             116  471. 
19          15 Poland              117  490.

Use show_query() to see the SQL dbplyr generates – it is the INNER JOIN you just wrote by hand:

q_inner <- inner_join(customers, orders, by = "customer_id")
dplyr::show_query(q_inner)
## <SQL>
## SELECT `customers`.*, `order_id`, `amount`
## FROM `customers`
## INNER JOIN `orders`
##   ON (`customers`.`customer_id` = `orders`.`customer_id`)

The left join works the same way:

q_left <- left_join(customers, orders, by = "customer_id")
dplyr::show_query(q_left)
## <SQL>
## SELECT `customers`.*, `order_id`, `amount`
## FROM `customers`
## LEFT JOIN `orders`
##   ON (`customers`.`customer_id` = `orders`.`customer_id`)

5.7 semi_join and anti_join

Two filtering joins have no direct SQL keyword but are everyday tools:

  • semi_join() keeps rows of customers that have a match in orders (14 customers). The generated SQL uses WHERE EXISTS.
  • anti_join() keeps rows of customers that lack a match in orders (the same 6 customers from the LEFT JOIN check above). The generated SQL uses WHERE NOT EXISTS.
q_semi <- semi_join(customers, orders, by = "customer_id")
dplyr::show_query(q_semi)
## <SQL>
## SELECT `customers`.*
## FROM `customers`
## WHERE EXISTS (
##   SELECT 1 FROM `orders`
##   WHERE (`customers`.`customer_id` = `orders`.`customer_id`)
## )
dplyr::collect(q_semi)
## # A tibble: 14 × 2
##    customer_id country       
##          <int> <chr>         
##  1           1 United States 
##  2           2 United Kingdom
##  3           3 Germany       
##  4           5 Canada        
##  5           6 Australia     
##  6           7 Brazil        
##  7           8 Japan         
##  8           9 Mexico        
##  9          10 Netherlands   
## 10          11 Sweden        
## 11          12 Norway        
## 12          13 Denmark       
## 13          14 Finland       
## 14          15 Poland
q_anti <- anti_join(customers, orders, by = "customer_id")
dplyr::show_query(q_anti)
## <SQL>
## SELECT `customers`.*
## FROM `customers`
## WHERE NOT EXISTS (
##   SELECT 1 FROM `orders`
##   WHERE (`customers`.`customer_id` = `orders`.`customer_id`)
## )
dplyr::collect(q_anti)
## # A tibble: 6 × 2
##   customer_id country 
##         <int> <chr>   
## 1           4 France  
## 2          16 Italy   
## 3          17 Portugal
## 4          18 Greece  
## 5          19 Ireland 
## 6          20 Belgium

Rule of thumb: use semi_join() / anti_join() when you want to filter one table by membership in another without adding the other table’s columns.

dbDisconnect(con)

5.8 Exercises

  1. List the customer_id and country of customers who placed no orders. Write it both as a LEFT JOIN ... IS NULL query and as an anti_join().
  2. Compute total order amount per customer (highest first) with an INNER JOIN + GROUP BY. Which customer spent the most?
  3. Find the orphan order: which order_id points at a non-existent customer?
Solutions

  1. SELECT c.customer_id, c.country FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_id IS NULL — customers 4, 16, 17, 18, 19, 20. Same result: anti_join(customers, orders, by = "customer_id").

  2. SELECT c.customer_id, c.country, SUM(o.amount) AS total FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.country ORDER BY total DESC — customer 3 (Germany, 1015.82).

  3. SELECT o.order_id, o.customer_id FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id WHERE c.customer_id IS NULL — order 120 (customer 99).