Agenda


  • inner join
  • left join
  • right join
  • semi join
  • anti join
  • full join

Case Study


  • details of customers who have placed orders and their order details
  • details of customers and their orders irrespective of whether a customer has placed orders or not.
  • get customer details for all orders
  • get customer data, if available, for all orders
  • details of customers who have not placed orders
  • details of all customers and all orders

Case Study




Libraries


Data: Orders


## # A tibble: 300 x 3
##       id order_date amount
##    <dbl> <chr>       <dbl>
##  1   368 7/2/2016     365.
##  2   286 11/2/2016   2064.
##  3    28 2/22/2017    432.
##  4   309 3/5/2017     480.
##  5     2 12/28/2016   235.
##  6    31 12/30/2016  2745.
##  7   179 12/21/2016  2358.
##  8   484 11/24/2016  1031.
##  9   115 9/9/2016    1218.
## 10   340 5/6/2017    1184.
## # ... with 290 more rows

Data: Customers


## # A tibble: 91 x 3
##       id first_name city      
##    <dbl> <chr>      <chr>     
##  1     1 Elbertine  California
##  2     2 Marcella   Colorado  
##  3     3 Daria      Florida   
##  4     4 Sherilyn   Distric...
##  5     5 Ketty      Texas     
##  6     6 Jethro     California
##  7     7 Jeremiah   California
##  8     8 Constancia Texas     
##  9     9 Muire      Idaho     
## 10    10 Abigail    Texas     
## # ... with 81 more rows

Example Data




Inner Join




Case Study


## # A tibble: 55 x 5
##       id first_name city       order_date amount
##    <dbl> <chr>      <chr>      <chr>       <dbl>
##  1     2 Marcella   Colorado   12/28/2016   235.
##  2     2 Marcella   Colorado   8/31/2016   1150.
##  3     5 Ketty      Texas      1/17/2017    346.
##  4     6 Jethro     California 1/27/2017   2317.
##  5     7 Jeremiah   California 6/21/2016    136.
##  6     7 Jeremiah   California 2/13/2017   1407.
##  7     7 Jeremiah   California 7/8/2016    1914.
##  8     8 Constancia Texas      11/5/2016   2461.
##  9     8 Constancia Texas      5/19/2017   2714.
## 10     9 Muire      Idaho      12/28/2016   187.
## # ... with 45 more rows

Left Join