08_joins.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction to DataFrames\n",
    "**[Bogumił Kamiński](http://bogumilkaminski.pl/about/), February 13, 2023**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "using DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Joining DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preparing DataFrames for a join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>5×2 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">ID</th><th style = \"text-align: left;\">name</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"String\" style = \"text-align: left;\">String</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">Alice</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: left;\">Bob</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: right;\">3</td><td style = \"text-align: left;\">Conor</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: right;\">4</td><td style = \"text-align: left;\">Dave</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: left;\">Zed</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& ID & name\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & String\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & Alice \\\\\n",
       "\t2 & 2 & Bob \\\\\n",
       "\t3 & 3 & Conor \\\\\n",
       "\t4 & 4 & Dave \\\\\n",
       "\t5 & \\emph{missing} & Zed \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m5×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m ID      \u001b[0m\u001b[1m name   \u001b[0m\n",
       "     │\u001b[90m Int64?  \u001b[0m\u001b[90m String \u001b[0m\n",
       "─────┼─────────────────\n",
       "   1 │       1  Alice\n",
       "   2 │       2  Bob\n",
       "   3 │       3  Conor\n",
       "   4 │       4  Dave\n",
       "   5 │\u001b[90m missing \u001b[0m Zed"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x = DataFrame(ID=[1,2,3,4,missing], name = [\"Alice\", \"Bob\", \"Conor\", \"Dave\",\"Zed\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>5×2 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">id</th><th style = \"text-align: left;\">age</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">21</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">22</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: right;\">5</td><td style = \"text-align: right;\">23</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: right;\">6</td><td style = \"text-align: right;\">24</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: right;\">99</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& id & age\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 21 \\\\\n",
       "\t2 & 2 & 22 \\\\\n",
       "\t3 & 5 & 23 \\\\\n",
       "\t4 & 6 & 24 \\\\\n",
       "\t5 & \\emph{missing} & 99 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m5×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id      \u001b[0m\u001b[1m age   \u001b[0m\n",
       "     │\u001b[90m Int64?  \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼────────────────\n",
       "   1 │       1     21\n",
       "   2 │       2     22\n",
       "   3 │       5     23\n",
       "   4 │       6     24\n",
       "   5 │\u001b[90m missing \u001b[0m    99"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "y = DataFrame(id=[1,2,5,6,missing], age=[21,22,23,24,99])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rules for the `on` keyword argument:\n",
    "* a single `Symbol` or string if joining on one column with the same name, e.g. `on=:id`\n",
    "* a `Pair` of `Symbol`s or string if joining on one column with different names, e.g. `on=:id=>:id2`\n",
    "* a vector of `Symbol`s or strings if joining on multiple columns with the same name, e.g. `on=[:id1, :id2]`\n",
    "* a vector of `Pair`s of `Symbol`s or strings if joining on multiple columns with different names, e.g. `on=[:a1=>:a2, :b1=>:b2]`\n",
    "* a vector containing a combination of `Symbol`s or strings or `Pair` of `Symbol`s or strings, e.g. `on=[:a1=>:a2, :b1]`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Standard joins: inner, left, right, outer, semi, anti"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "ename": "LoadError",
     "evalue": "ArgumentError: missing values in key columns are not allowed when matchmissing == :error",
     "output_type": "error",
     "traceback": [
      "ArgumentError: missing values in key columns are not allowed when matchmissing == :error",
      "",
      "Stacktrace:",
      " [1] DataFrames.DataFrameJoiner(dfl::DataFrame, dfr::DataFrame, on::Pair{Symbol, Symbol}, matchmissing::Symbol, kind::Symbol)",
      "   @ DataFrames C:\\Users\\bogum\\.julia\\packages\\DataFrames\\LteEl\\src\\join\\composer.jl:84",
      " [2] _join(df1::DataFrame, df2::DataFrame; on::Pair{Symbol, Symbol}, kind::Symbol, makeunique::Bool, indicator::Nothing, validate::Tuple{Bool, Bool}, left_rename::typeof(identity), right_rename::typeof(identity), matchmissing::Symbol, order::Symbol)",
      "   @ DataFrames C:\\Users\\bogum\\.julia\\packages\\DataFrames\\LteEl\\src\\join\\composer.jl:497",
      " [3] kwcall(::NamedTuple{(:on, :kind, :makeunique, :indicator, :validate, :left_rename, :right_rename, :matchmissing, :order), Tuple{Pair{Symbol, Symbol}, Symbol, Bool, Nothing, Tuple{Bool, Bool}, typeof(identity), typeof(identity), Symbol, Symbol}}, ::typeof(DataFrames._join), df1::DataFrame, df2::DataFrame)",
      "   @ DataFrames C:\\Users\\bogum\\.julia\\packages\\DataFrames\\LteEl\\src\\join\\composer.jl:479",
      " [4] #innerjoin#659",
      "   @ C:\\Users\\bogum\\.julia\\packages\\DataFrames\\LteEl\\src\\join\\composer.jl:761 [inlined]",
      " [5] kwcall(::NamedTuple{(:on,), Tuple{Pair{Symbol, Symbol}}}, ::typeof(innerjoin), df1::DataFrame, df2::DataFrame)",
      "   @ DataFrames C:\\Users\\bogum\\.julia\\packages\\DataFrames\\LteEl\\src\\join\\composer.jl:750",
      " [6] top-level scope",
      "   @ In[4]:1"
     ]
    }
   ],
   "source": [
    "innerjoin(x, y, on=:ID=>:id) # missing is not allowed to join-on by default"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>3×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">ID</th><th style = \"text-align: left;\">name</th><th style = \"text-align: left;\">age</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"String\" style = \"text-align: left;\">String</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">Alice</td><td style = \"text-align: right;\">21</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: left;\">Bob</td><td style = \"text-align: right;\">22</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: left;\">Zed</td><td style = \"text-align: right;\">99</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& ID & name & age\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & String & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & Alice & 21 \\\\\n",
       "\t2 & 2 & Bob & 22 \\\\\n",
       "\t3 & \\emph{missing} & Zed & 99 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m ID      \u001b[0m\u001b[1m name   \u001b[0m\u001b[1m age   \u001b[0m\n",
       "     │\u001b[90m Int64?  \u001b[0m\u001b[90m String \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼────────────────────────\n",
       "   1 │       1  Alice      21\n",
       "   2 │       2  Bob        22\n",
       "   3 │\u001b[90m missing \u001b[0m Zed        99"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "innerjoin(x, y, on=:ID=>:id, matchmissing=:equal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>5×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">ID</th><th style = \"text-align: left;\">name</th><th style = \"text-align: left;\">age</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"String\" style = \"text-align: left;\">String</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">Alice</td><td style = \"text-align: right;\">21</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: left;\">Bob</td><td style = \"text-align: right;\">22</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: left;\">Zed</td><td style = \"text-align: right;\">99</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: right;\">3</td><td style = \"text-align: left;\">Conor</td><td style = \"font-style: italic; text-align: right;\">missing</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: right;\">4</td><td style = \"text-align: left;\">Dave</td><td style = \"font-style: italic; text-align: right;\">missing</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& ID & name & age\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & String & Int64?\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & Alice & 21 \\\\\n",
       "\t2 & 2 & Bob & 22 \\\\\n",
       "\t3 & \\emph{missing} & Zed & 99 \\\\\n",
       "\t4 & 3 & Conor & \\emph{missing} \\\\\n",
       "\t5 & 4 & Dave & \\emph{missing} \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m5×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m ID      \u001b[0m\u001b[1m name   \u001b[0m\u001b[1m age     \u001b[0m\n",
       "     │\u001b[90m Int64?  \u001b[0m\u001b[90m String \u001b[0m\u001b[90m Int64?  \u001b[0m\n",
       "─────┼──────────────────────────\n",
       "   1 │       1  Alice        21\n",
       "   2 │       2  Bob          22\n",
       "   3 │\u001b[90m missing \u001b[0m Zed          99\n",
       "   4 │       3  Conor  \u001b[90m missing \u001b[0m\n",
       "   5 │       4  Dave   \u001b[90m missing \u001b[0m"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "leftjoin(x, y, on=\"ID\"=>\"id\", matchmissing=:equal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>5×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">ID</th><th style = \"text-align: left;\">name</th><th style = \"text-align: left;\">age</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Union{Missing, String}\" style = \"text-align: left;\">String?</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">Alice</td><td style = \"text-align: right;\">21</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: left;\">Bob</td><td style = \"text-align: right;\">22</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: left;\">Zed</td><td style = \"text-align: right;\">99</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: right;\">5</td><td style = \"font-style: italic; text-align: left;\">missing</td><td style = \"text-align: right;\">23</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: right;\">6</td><td style = \"font-style: italic; text-align: left;\">missing</td><td style = \"text-align: right;\">24</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& ID & name & age\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & String? & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & Alice & 21 \\\\\n",
       "\t2 & 2 & Bob & 22 \\\\\n",
       "\t3 & \\emph{missing} & Zed & 99 \\\\\n",
       "\t4 & 5 & \\emph{missing} & 23 \\\\\n",
       "\t5 & 6 & \\emph{missing} & 24 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m5×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m ID      \u001b[0m\u001b[1m name    \u001b[0m\u001b[1m age   \u001b[0m\n",
       "     │\u001b[90m Int64?  \u001b[0m\u001b[90m String? \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼─────────────────────────\n",
       "   1 │       1  Alice       21\n",
       "   2 │       2  Bob         22\n",
       "   3 │\u001b[90m missing \u001b[0m Zed         99\n",
       "   4 │       5 \u001b[90m missing \u001b[0m    23\n",
       "   5 │       6 \u001b[90m missing \u001b[0m    24"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rightjoin(x, y, on=:ID=>:id, matchmissing=:equal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>7×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">ID</th><th style = \"text-align: left;\">name</th><th style = \"text-align: left;\">age</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Union{Missing, String}\" style = \"text-align: left;\">String?</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">Alice</td><td style = \"text-align: right;\">21</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: left;\">Bob</td><td style = \"text-align: right;\">22</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: left;\">Zed</td><td style = \"text-align: right;\">99</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: right;\">3</td><td style = \"text-align: left;\">Conor</td><td style = \"font-style: italic; text-align: right;\">missing</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: right;\">4</td><td style = \"text-align: left;\">Dave</td><td style = \"font-style: italic; text-align: right;\">missing</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: right;\">5</td><td style = \"font-style: italic; text-align: left;\">missing</td><td style = \"text-align: right;\">23</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: right;\">6</td><td style = \"font-style: italic; text-align: left;\">missing</td><td style = \"text-align: right;\">24</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& ID & name & age\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & String? & Int64?\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & Alice & 21 \\\\\n",
       "\t2 & 2 & Bob & 22 \\\\\n",
       "\t3 & \\emph{missing} & Zed & 99 \\\\\n",
       "\t4 & 3 & Conor & \\emph{missing} \\\\\n",
       "\t5 & 4 & Dave & \\emph{missing} \\\\\n",
       "\t6 & 5 & \\emph{missing} & 23 \\\\\n",
       "\t7 & 6 & \\emph{missing} & 24 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m7×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m ID      \u001b[0m\u001b[1m name    \u001b[0m\u001b[1m age     \u001b[0m\n",
       "     │\u001b[90m Int64?  \u001b[0m\u001b[90m String? \u001b[0m\u001b[90m Int64?  \u001b[0m\n",
       "─────┼───────────────────────────\n",
       "   1 │       1  Alice         21\n",
       "   2 │       2  Bob           22\n",
       "   3 │\u001b[90m missing \u001b[0m Zed           99\n",
       "   4 │       3  Conor   \u001b[90m missing \u001b[0m\n",
       "   5 │       4  Dave    \u001b[90m missing \u001b[0m\n",
       "   6 │       5 \u001b[90m missing \u001b[0m      23\n",
       "   7 │       6 \u001b[90m missing \u001b[0m      24"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "outerjoin(x, y, on=:ID=>:id, matchmissing=:equal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>3×2 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">ID</th><th style = \"text-align: left;\">name</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"String\" style = \"text-align: left;\">String</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">Alice</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: left;\">Bob</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: left;\">Zed</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& ID & name\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & String\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & Alice \\\\\n",
       "\t2 & 2 & Bob \\\\\n",
       "\t3 & \\emph{missing} & Zed \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m ID      \u001b[0m\u001b[1m name   \u001b[0m\n",
       "     │\u001b[90m Int64?  \u001b[0m\u001b[90m String \u001b[0m\n",
       "─────┼─────────────────\n",
       "   1 │       1  Alice\n",
       "   2 │       2  Bob\n",
       "   3 │\u001b[90m missing \u001b[0m Zed"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "semijoin(x, y, on=:ID=>:id, matchmissing=:equal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>2×2 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">ID</th><th style = \"text-align: left;\">name</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"String\" style = \"text-align: left;\">String</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">3</td><td style = \"text-align: left;\">Conor</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">4</td><td style = \"text-align: left;\">Dave</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& ID & name\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & String\\\\\n",
       "\t\\hline\n",
       "\t1 & 3 & Conor \\\\\n",
       "\t2 & 4 & Dave \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m2×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m ID     \u001b[0m\u001b[1m name   \u001b[0m\n",
       "     │\u001b[90m Int64? \u001b[0m\u001b[90m String \u001b[0m\n",
       "─────┼────────────────\n",
       "   1 │      3  Conor\n",
       "   2 │      4  Dave"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "antijoin(x, y, on=:ID=>:id, matchmissing=:equal)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cross join"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(here no `on` argument)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>6×2 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">x</th><th style = \"text-align: left;\">y</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"String\" style = \"text-align: left;\">String</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">a</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">b</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">c</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: right;\">2</td><td style = \"text-align: left;\">a</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: right;\">2</td><td style = \"text-align: left;\">b</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: right;\">2</td><td style = \"text-align: left;\">c</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& x & y\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & String\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & a \\\\\n",
       "\t2 & 1 & b \\\\\n",
       "\t3 & 1 & c \\\\\n",
       "\t4 & 2 & a \\\\\n",
       "\t5 & 2 & b \\\\\n",
       "\t6 & 2 & c \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m6×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m x     \u001b[0m\u001b[1m y      \u001b[0m\n",
       "     │\u001b[90m Int64 \u001b[0m\u001b[90m String \u001b[0m\n",
       "─────┼───────────────\n",
       "   1 │     1  a\n",
       "   2 │     1  b\n",
       "   3 │     1  c\n",
       "   4 │     2  a\n",
       "   5 │     2  b\n",
       "   6 │     2  c"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crossjoin(DataFrame(x=[1,2]), DataFrame(y=[\"a\",\"b\",\"c\"]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Complex cases of joins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>6×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">id1</th><th style = \"text-align: left;\">id2</th><th style = \"text-align: left;\">name</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"String\" style = \"text-align: left;\">String</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">Alice</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">11</td><td style = \"text-align: left;\">Bob</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: left;\">Conor</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">21</td><td style = \"text-align: left;\">Dave</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: left;\">Zed</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: right;\">99</td><td style = \"text-align: left;\">Zoe</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id1 & id2 & name\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & Int64? & String\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & Alice \\\\\n",
       "\t2 & 1 & 11 & Bob \\\\\n",
       "\t3 & 2 & 2 & Conor \\\\\n",
       "\t4 & 2 & 21 & Dave \\\\\n",
       "\t5 & \\emph{missing} & \\emph{missing} & Zed \\\\\n",
       "\t6 & \\emph{missing} & 99 & Zoe \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m6×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id1     \u001b[0m\u001b[1m id2     \u001b[0m\u001b[1m name   \u001b[0m\n",
       "     │\u001b[90m Int64?  \u001b[0m\u001b[90m Int64?  \u001b[0m\u001b[90m String \u001b[0m\n",
       "─────┼──────────────────────────\n",
       "   1 │       1        1  Alice\n",
       "   2 │       1       11  Bob\n",
       "   3 │       2        2  Conor\n",
       "   4 │       2       21  Dave\n",
       "   5 │\u001b[90m missing \u001b[0m\u001b[90m missing \u001b[0m Zed\n",
       "   6 │\u001b[90m missing \u001b[0m      99  Zoe"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x = DataFrame(id1=[1,1,2,2,missing,missing],\n",
    "              id2=[1,11,2,21,missing,99],\n",
    "              name = [\"Alice\", \"Bob\", \"Conor\", \"Dave\",\"Zed\", \"Zoe\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>6×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">id1</th><th style = \"text-align: left;\">id2</th><th style = \"text-align: left;\">age</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">11</td><td style = \"text-align: right;\">21</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">22</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">31</td><td style = \"text-align: right;\">23</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">24</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: right;\">99</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: right;\">999</td><td style = \"text-align: right;\">100</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id1 & id2 & age\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & Int64? & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 11 & 21 \\\\\n",
       "\t2 & 1 & 1 & 22 \\\\\n",
       "\t3 & 3 & 31 & 23 \\\\\n",
       "\t4 & 3 & 3 & 24 \\\\\n",
       "\t5 & \\emph{missing} & \\emph{missing} & 99 \\\\\n",
       "\t6 & \\emph{missing} & 999 & 100 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m6×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id1     \u001b[0m\u001b[1m id2     \u001b[0m\u001b[1m age   \u001b[0m\n",
       "     │\u001b[90m Int64?  \u001b[0m\u001b[90m Int64?  \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼─────────────────────────\n",
       "   1 │       1       11     21\n",
       "   2 │       1        1     22\n",
       "   3 │       3       31     23\n",
       "   4 │       3        3     24\n",
       "   5 │\u001b[90m missing \u001b[0m\u001b[90m missing \u001b[0m    99\n",
       "   6 │\u001b[90m missing \u001b[0m     999    100"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "y = DataFrame(id1=[1,1,3,3,missing,missing],\n",
    "              id2=[11,1,31,3,missing,999],\n",
    "              age = [21,22,23,24,99, 100])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>3×4 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">id1</th><th style = \"text-align: left;\">id2</th><th style = \"text-align: left;\">name</th><th style = \"text-align: left;\">age</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"String\" style = \"text-align: left;\">String</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">11</td><td style = \"text-align: left;\">Bob</td><td style = \"text-align: right;\">21</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">Alice</td><td style = \"text-align: right;\">22</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: left;\">Zed</td><td style = \"text-align: right;\">99</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& id1 & id2 & name & age\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & Int64? & String & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 11 & Bob & 21 \\\\\n",
       "\t2 & 1 & 1 & Alice & 22 \\\\\n",
       "\t3 & \\emph{missing} & \\emph{missing} & Zed & 99 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×4 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id1     \u001b[0m\u001b[1m id2     \u001b[0m\u001b[1m name   \u001b[0m\u001b[1m age   \u001b[0m\n",
       "     │\u001b[90m Int64?  \u001b[0m\u001b[90m Int64?  \u001b[0m\u001b[90m String \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼─────────────────────────────────\n",
       "   1 │       1       11  Bob        21\n",
       "   2 │       1        1  Alice      22\n",
       "   3 │\u001b[90m missing \u001b[0m\u001b[90m missing \u001b[0m Zed        99"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "innerjoin(x, y, on=[:id1, :id2], matchmissing=:equal) # joining on two columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>12×6 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">id1</th><th style = \"text-align: left;\">id2</th><th style = \"text-align: left;\">name</th><th style = \"text-align: left;\">id2_1</th><th style = \"text-align: left;\">age</th><th style = \"text-align: left;\">source</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Union{Missing, String}\" style = \"text-align: left;\">String?</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"String\" style = \"text-align: left;\">String</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">Alice</td><td style = \"text-align: right;\">11</td><td style = \"text-align: right;\">21</td><td style = \"text-align: left;\">both</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">11</td><td style = \"text-align: left;\">Bob</td><td style = \"text-align: right;\">11</td><td style = \"text-align: right;\">21</td><td style = \"text-align: left;\">both</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">Alice</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">22</td><td style = \"text-align: left;\">both</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">11</td><td style = \"text-align: left;\">Bob</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">22</td><td style = \"text-align: left;\">both</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: left;\">Zed</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: right;\">99</td><td style = \"text-align: left;\">both</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: right;\">99</td><td style = \"text-align: left;\">Zoe</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: right;\">99</td><td style = \"text-align: left;\">both</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: left;\">Zed</td><td style = \"text-align: right;\">999</td><td style = \"text-align: right;\">100</td><td style = \"text-align: left;\">both</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: right;\">99</td><td style = \"text-align: left;\">Zoe</td><td style = \"text-align: right;\">999</td><td style = \"text-align: right;\">100</td><td style = \"text-align: left;\">both</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: left;\">Conor</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: left;\">left_only</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">21</td><td style = \"text-align: left;\">Dave</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: left;\">left_only</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">11</td><td style = \"text-align: right;\">3</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"font-style: italic; text-align: left;\">missing</td><td style = \"text-align: right;\">31</td><td style = \"text-align: right;\">23</td><td style = \"text-align: left;\">right_only</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">12</td><td style = \"text-align: right;\">3</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"font-style: italic; text-align: left;\">missing</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">24</td><td style = \"text-align: left;\">right_only</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccccc}\n",
       "\t& id1 & id2 & name & id2\\_1 & age & source\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & Int64? & String? & Int64? & Int64? & String\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & Alice & 11 & 21 & both \\\\\n",
       "\t2 & 1 & 11 & Bob & 11 & 21 & both \\\\\n",
       "\t3 & 1 & 1 & Alice & 1 & 22 & both \\\\\n",
       "\t4 & 1 & 11 & Bob & 1 & 22 & both \\\\\n",
       "\t5 & \\emph{missing} & \\emph{missing} & Zed & \\emph{missing} & 99 & both \\\\\n",
       "\t6 & \\emph{missing} & 99 & Zoe & \\emph{missing} & 99 & both \\\\\n",
       "\t7 & \\emph{missing} & \\emph{missing} & Zed & 999 & 100 & both \\\\\n",
       "\t8 & \\emph{missing} & 99 & Zoe & 999 & 100 & both \\\\\n",
       "\t9 & 2 & 2 & Conor & \\emph{missing} & \\emph{missing} & left\\_only \\\\\n",
       "\t10 & 2 & 21 & Dave & \\emph{missing} & \\emph{missing} & left\\_only \\\\\n",
       "\t11 & 3 & \\emph{missing} & \\emph{missing} & 31 & 23 & right\\_only \\\\\n",
       "\t12 & 3 & \\emph{missing} & \\emph{missing} & 3 & 24 & right\\_only \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m12×6 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id1     \u001b[0m\u001b[1m id2     \u001b[0m\u001b[1m name    \u001b[0m\u001b[1m id2_1   \u001b[0m\u001b[1m age     \u001b[0m\u001b[1m source     \u001b[0m\n",
       "     │\u001b[90m Int64?  \u001b[0m\u001b[90m Int64?  \u001b[0m\u001b[90m String? \u001b[0m\u001b[90m Int64?  \u001b[0m\u001b[90m Int64?  \u001b[0m\u001b[90m String     \u001b[0m\n",
       "─────┼─────────────────────────────────────────────────────────\n",
       "   1 │       1        1  Alice         11       21  both\n",
       "   2 │       1       11  Bob           11       21  both\n",
       "   3 │       1        1  Alice          1       22  both\n",
       "   4 │       1       11  Bob            1       22  both\n",
       "   5 │\u001b[90m missing \u001b[0m\u001b[90m missing \u001b[0m Zed     \u001b[90m missing \u001b[0m      99  both\n",
       "   6 │\u001b[90m missing \u001b[0m      99  Zoe     \u001b[90m missing \u001b[0m      99  both\n",
       "   7 │\u001b[90m missing \u001b[0m\u001b[90m missing \u001b[0m Zed          999      100  both\n",
       "   8 │\u001b[90m missing \u001b[0m      99  Zoe          999      100  both\n",
       "   9 │       2        2  Conor   \u001b[90m missing \u001b[0m\u001b[90m missing \u001b[0m left_only\n",
       "  10 │       2       21  Dave    \u001b[90m missing \u001b[0m\u001b[90m missing \u001b[0m left_only\n",
       "  11 │       3 \u001b[90m missing \u001b[0m\u001b[90m missing \u001b[0m      31       23  right_only\n",
       "  12 │       3 \u001b[90m missing \u001b[0m\u001b[90m missing \u001b[0m       3       24  right_only"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "outerjoin(x, y, on=:id1, makeunique=true, indicator=:source, matchmissing=:equal) # with duplicates all combinations are produced"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "ename": "LoadError",
     "evalue": "ArgumentError: Merge key(s) are not unique in both df1 and df2. df1 contains 3 duplicate keys: NamedTuple{(:id1,), Tuple{Union{Missing, Int64}}}((1,)), ..., NamedTuple{(:id1,), Tuple{Union{Missing, Int64}}}((missing,)). df2 contains 3 duplicate keys: NamedTuple{(:id1,), Tuple{Union{Missing, Int64}}}((1,)), ..., NamedTuple{(:id1,), Tuple{Union{Missing, Int64}}}((missing,)).",
     "output_type": "error",
     "traceback": [
      "ArgumentError: Merge key(s) are not unique in both df1 and df2. df1 contains 3 duplicate keys: NamedTuple{(:id1,), Tuple{Union{Missing, Int64}}}((1,)), ..., NamedTuple{(:id1,), Tuple{Union{Missing, Int64}}}((missing,)). df2 contains 3 duplicate keys: NamedTuple{(:id1,), Tuple{Union{Missing, Int64}}}((1,)), ..., NamedTuple{(:id1,), Tuple{Union{Missing, Int64}}}((missing,)).",
      "",
      "Stacktrace:",
      " [1] _join(df1::DataFrame, df2::DataFrame; on::Symbol, kind::Symbol, makeunique::Bool, indicator::Nothing, validate::Tuple{Bool, Bool}, left_rename::typeof(identity), right_rename::typeof(identity), matchmissing::Symbol, order::Symbol)",
      "   @ DataFrames C:\\Users\\bogum\\.julia\\packages\\DataFrames\\LteEl\\src\\join\\composer.jl:561",
      " [2] kwcall(::NamedTuple{(:on, :kind, :makeunique, :indicator, :validate, :left_rename, :right_rename, :matchmissing, :order), Tuple{Symbol, Symbol, Bool, Nothing, Tuple{Bool, Bool}, typeof(identity), typeof(identity), Symbol, Symbol}}, ::typeof(DataFrames._join), df1::DataFrame, df2::DataFrame)",
      "   @ DataFrames C:\\Users\\bogum\\.julia\\packages\\DataFrames\\LteEl\\src\\join\\composer.jl:479",
      " [3] #innerjoin#659",
      "   @ C:\\Users\\bogum\\.julia\\packages\\DataFrames\\LteEl\\src\\join\\composer.jl:761 [inlined]",
      " [4] kwcall(::NamedTuple{(:on, :makeunique, :validate, :matchmissing), Tuple{Symbol, Bool, Tuple{Bool, Bool}, Symbol}}, ::typeof(innerjoin), df1::DataFrame, df2::DataFrame)",
      "   @ DataFrames C:\\Users\\bogum\\.julia\\packages\\DataFrames\\LteEl\\src\\join\\composer.jl:750",
      " [5] top-level scope",
      "   @ In[16]:2"
     ]
    }
   ],
   "source": [
    "# you can force validation of uniqueness of key on which you join\n",
    "innerjoin(x, y, on=:id1, makeunique=true, validate=(true,true), matchmissing=:equal)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "mixed `on` argument for joining on multiple columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>6×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">id1</th><th style = \"text-align: left;\">id2</th><th style = \"text-align: left;\">x1</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Char\" style = \"text-align: left;\">Char</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">a</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: left;\">b</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">c</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: right;\">4</td><td style = \"text-align: right;\">2</td><td style = \"text-align: left;\">d</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: right;\">5</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">e</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: right;\">6</td><td style = \"text-align: right;\">2</td><td style = \"text-align: left;\">f</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id1 & id2 & x1\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64 & Char\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & a \\\\\n",
       "\t2 & 2 & 2 & b \\\\\n",
       "\t3 & 3 & 1 & c \\\\\n",
       "\t4 & 4 & 2 & d \\\\\n",
       "\t5 & 5 & 1 & e \\\\\n",
       "\t6 & 6 & 2 & f \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m6×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id1   \u001b[0m\u001b[1m id2   \u001b[0m\u001b[1m x1   \u001b[0m\n",
       "     │\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Char \u001b[0m\n",
       "─────┼────────────────────\n",
       "   1 │     1      1  a\n",
       "   2 │     2      2  b\n",
       "   3 │     3      1  c\n",
       "   4 │     4      2  d\n",
       "   5 │     5      1  e\n",
       "   6 │     6      2  f"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x = DataFrame(id1=1:6, id2=[1,2,1,2,1,2], x1 = 'a':'f')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>6×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">id1</th><th style = \"text-align: left;\">ID2</th><th style = \"text-align: left;\">x2</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Char\" style = \"text-align: left;\">Char</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">a</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: left;\">b</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">3</td><td style = \"text-align: left;\">c</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: right;\">4</td><td style = \"text-align: right;\">4</td><td style = \"text-align: left;\">d</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: right;\">5</td><td style = \"text-align: right;\">5</td><td style = \"text-align: left;\">e</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: right;\">6</td><td style = \"text-align: right;\">6</td><td style = \"text-align: left;\">f</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id1 & ID2 & x2\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64 & Char\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & a \\\\\n",
       "\t2 & 2 & 2 & b \\\\\n",
       "\t3 & 3 & 3 & c \\\\\n",
       "\t4 & 4 & 4 & d \\\\\n",
       "\t5 & 5 & 5 & e \\\\\n",
       "\t6 & 6 & 6 & f \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m6×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id1   \u001b[0m\u001b[1m ID2   \u001b[0m\u001b[1m x2   \u001b[0m\n",
       "     │\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Char \u001b[0m\n",
       "─────┼────────────────────\n",
       "   1 │     1      1  a\n",
       "   2 │     2      2  b\n",
       "   3 │     3      3  c\n",
       "   4 │     4      4  d\n",
       "   5 │     5      5  e\n",
       "   6 │     6      6  f"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "y = DataFrame(id1=1:6, ID2=1:6, x2 = 'a':'f')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>2×4 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">id1</th><th style = \"text-align: left;\">id2</th><th style = \"text-align: left;\">x1</th><th style = \"text-align: left;\">x2</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Char\" style = \"text-align: left;\">Char</th><th title = \"Char\" style = \"text-align: left;\">Char</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">a</td><td style = \"text-align: left;\">a</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: left;\">b</td><td style = \"text-align: left;\">b</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& id1 & id2 & x1 & x2\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64 & Char & Char\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & a & a \\\\\n",
       "\t2 & 2 & 2 & b & b \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m2×4 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id1   \u001b[0m\u001b[1m id2   \u001b[0m\u001b[1m x1   \u001b[0m\u001b[1m x2   \u001b[0m\n",
       "     │\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Char \u001b[0m\u001b[90m Char \u001b[0m\n",
       "─────┼──────────────────────────\n",
       "   1 │     1      1  a     a\n",
       "   2 │     2      2  b     b"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "innerjoin(x, y, on=[:id1, :id2=>:ID2])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "joining more than two data frames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5-element Vector{DataFrame}:\n",
       " \u001b[1m6×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id    \u001b[0m\u001b[1m v1    \u001b[0m\n",
       "     │\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼──────────────\n",
       "   1 │     1     11\n",
       "   2 │     2     12\n",
       "   3 │     3     13\n",
       "   4 │     4     14\n",
       "   5 │     5     15\n",
       "   6 │     6     16\n",
       " \u001b[1m6×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id    \u001b[0m\u001b[1m v2    \u001b[0m\n",
       "     │\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼──────────────\n",
       "   1 │     1     21\n",
       "   2 │     2     22\n",
       "   3 │     3     23\n",
       "   4 │     4     24\n",
       "   5 │     5     25\n",
       "   6 │     6     26\n",
       " \u001b[1m6×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id    \u001b[0m\u001b[1m v3    \u001b[0m\n",
       "     │\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼──────────────\n",
       "   1 │     1     31\n",
       "   2 │     2     32\n",
       "   3 │     3     33\n",
       "   4 │     4     34\n",
       "   5 │     5     35\n",
       "   6 │     6     36\n",
       " \u001b[1m6×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id    \u001b[0m\u001b[1m v4    \u001b[0m\n",
       "     │\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼──────────────\n",
       "   1 │     1     41\n",
       "   2 │     2     42\n",
       "   3 │     3     43\n",
       "   4 │     4     44\n",
       "   5 │     5     45\n",
       "   6 │     6     46\n",
       " \u001b[1m6×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id    \u001b[0m\u001b[1m v5    \u001b[0m\n",
       "     │\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼──────────────\n",
       "   1 │     1     51\n",
       "   2 │     2     52\n",
       "   3 │     3     53\n",
       "   4 │     4     54\n",
       "   5 │     5     55\n",
       "   6 │     6     56"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xs = [DataFrame(\"id\"=>1:6, \"v$i\"=>((1:6) .+ 10i)) for i in 1:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>6×6 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">id</th><th style = \"text-align: left;\">v1</th><th style = \"text-align: left;\">v2</th><th style = \"text-align: left;\">v3</th><th style = \"text-align: left;\">v4</th><th style = \"text-align: left;\">v5</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">11</td><td style = \"text-align: right;\">21</td><td style = \"text-align: right;\">31</td><td style = \"text-align: right;\">41</td><td style = \"text-align: right;\">51</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">12</td><td style = \"text-align: right;\">22</td><td style = \"text-align: right;\">32</td><td style = \"text-align: right;\">42</td><td style = \"text-align: right;\">52</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">13</td><td style = \"text-align: right;\">23</td><td style = \"text-align: right;\">33</td><td style = \"text-align: right;\">43</td><td style = \"text-align: right;\">53</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: right;\">4</td><td style = \"text-align: right;\">14</td><td style = \"text-align: right;\">24</td><td style = \"text-align: right;\">34</td><td style = \"text-align: right;\">44</td><td style = \"text-align: right;\">54</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: right;\">5</td><td style = \"text-align: right;\">15</td><td style = \"text-align: right;\">25</td><td style = \"text-align: right;\">35</td><td style = \"text-align: right;\">45</td><td style = \"text-align: right;\">55</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: right;\">6</td><td style = \"text-align: right;\">16</td><td style = \"text-align: right;\">26</td><td style = \"text-align: right;\">36</td><td style = \"text-align: right;\">46</td><td style = \"text-align: right;\">56</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccccc}\n",
       "\t& id & v1 & v2 & v3 & v4 & v5\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64 & Int64 & Int64 & Int64 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 11 & 21 & 31 & 41 & 51 \\\\\n",
       "\t2 & 2 & 12 & 22 & 32 & 42 & 52 \\\\\n",
       "\t3 & 3 & 13 & 23 & 33 & 43 & 53 \\\\\n",
       "\t4 & 4 & 14 & 24 & 34 & 44 & 54 \\\\\n",
       "\t5 & 5 & 15 & 25 & 35 & 45 & 55 \\\\\n",
       "\t6 & 6 & 16 & 26 & 36 & 46 & 56 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m6×6 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id    \u001b[0m\u001b[1m v1    \u001b[0m\u001b[1m v2    \u001b[0m\u001b[1m v3    \u001b[0m\u001b[1m v4    \u001b[0m\u001b[1m v5    \u001b[0m\n",
       "     │\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼──────────────────────────────────────────\n",
       "   1 │     1     11     21     31     41     51\n",
       "   2 │     2     12     22     32     42     52\n",
       "   3 │     3     13     23     33     43     53\n",
       "   4 │     4     14     24     34     44     54\n",
       "   5 │     5     15     25     35     45     55\n",
       "   6 │     6     16     26     36     46     56"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "innerjoin(xs..., on=:id) # also for outerjoin and crossjoin"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### matchmissing keyword argument"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In general you have three options how `missing` values are handled in joins that are handled by `matchmisssing` kewyowrd argument value as follows:\n",
    "* `:error`: throw an error if missings are encountered (this is the default)\n",
    "* `:equal`: assume `misssing` values are equal to themselves\n",
    "* `:notequal`: assume `misssing` values are not equal to themselves (not available for `outerjoin`)\n",
    "\n",
    "Here are some examples comparing the options:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>3×2 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">id</th><th style = \"text-align: left;\">x</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">2</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: right;\">3</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& id & x\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 \\\\\n",
       "\t2 & 2 & 2 \\\\\n",
       "\t3 & \\emph{missing} & 3 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id      \u001b[0m\u001b[1m x     \u001b[0m\n",
       "     │\u001b[90m Int64?  \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼────────────────\n",
       "   1 │       1      1\n",
       "   2 │       2      2\n",
       "   3 │\u001b[90m missing \u001b[0m     3"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = DataFrame(id=[1, 2, missing], x=1:3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>3×2 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">id</th><th style = \"text-align: left;\">y</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: right;\">2</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">3</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& id & y\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 \\\\\n",
       "\t2 & \\emph{missing} & 2 \\\\\n",
       "\t3 & 3 & 3 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id      \u001b[0m\u001b[1m y     \u001b[0m\n",
       "     │\u001b[90m Int64?  \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼────────────────\n",
       "   1 │       1      1\n",
       "   2 │\u001b[90m missing \u001b[0m     2\n",
       "   3 │       3      3"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = DataFrame(id=[1, missing, 3], y=1:3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "ename": "LoadError",
     "evalue": "ArgumentError: missing values in key columns are not allowed when matchmissing == :error",
     "output_type": "error",
     "traceback": [
      "ArgumentError: missing values in key columns are not allowed when matchmissing == :error",
      "",
      "Stacktrace:",
      " [1] DataFrames.DataFrameJoiner(dfl::DataFrame, dfr::DataFrame, on::Symbol, matchmissing::Symbol, kind::Symbol)",
      "   @ DataFrames C:\\Users\\bogum\\.julia\\packages\\DataFrames\\LteEl\\src\\join\\composer.jl:84",
      " [2] _join(df1::DataFrame, df2::DataFrame; on::Symbol, kind::Symbol, makeunique::Bool, indicator::Nothing, validate::Tuple{Bool, Bool}, left_rename::typeof(identity), right_rename::typeof(identity), matchmissing::Symbol, order::Symbol)",
      "   @ DataFrames C:\\Users\\bogum\\.julia\\packages\\DataFrames\\LteEl\\src\\join\\composer.jl:497",
      " [3] kwcall(::NamedTuple{(:on, :kind, :makeunique, :indicator, :validate, :left_rename, :right_rename, :matchmissing, :order), Tuple{Symbol, Symbol, Bool, Nothing, Tuple{Bool, Bool}, typeof(identity), typeof(identity), Symbol, Symbol}}, ::typeof(DataFrames._join), df1::DataFrame, df2::DataFrame)",
      "   @ DataFrames C:\\Users\\bogum\\.julia\\packages\\DataFrames\\LteEl\\src\\join\\composer.jl:479",
      " [4] #innerjoin#659",
      "   @ C:\\Users\\bogum\\.julia\\packages\\DataFrames\\LteEl\\src\\join\\composer.jl:761 [inlined]",
      " [5] kwcall(::NamedTuple{(:on,), Tuple{Symbol}}, ::typeof(innerjoin), df1::DataFrame, df2::DataFrame)",
      "   @ DataFrames C:\\Users\\bogum\\.julia\\packages\\DataFrames\\LteEl\\src\\join\\composer.jl:750",
      " [6] top-level scope",
      "   @ In[24]:1"
     ]
    }
   ],
   "source": [
    "innerjoin(df1, df2, on=:id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>2×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">id</th><th style = \"text-align: left;\">x</th><th style = \"text-align: left;\">y</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">2</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id & x & y\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & Int64 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & 1 \\\\\n",
       "\t2 & \\emph{missing} & 3 & 2 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m2×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id      \u001b[0m\u001b[1m x     \u001b[0m\u001b[1m y     \u001b[0m\n",
       "     │\u001b[90m Int64?  \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼───────────────────────\n",
       "   1 │       1      1      1\n",
       "   2 │\u001b[90m missing \u001b[0m     3      2"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "innerjoin(df1, df2, on=:id, matchmissing=:equal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>1×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">id</th><th style = \"text-align: left;\">x</th><th style = \"text-align: left;\">y</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id & x & y\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & Int64 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & 1 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m1×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id     \u001b[0m\u001b[1m x     \u001b[0m\u001b[1m y     \u001b[0m\n",
       "     │\u001b[90m Int64? \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼──────────────────────\n",
       "   1 │      1      1      1"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "innerjoin(df1, df2, on=:id, matchmissing=:notequal)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since DataFrames.jl 1.3 you can do an efficient left join of two data frames in-place. This means that the left data frame gets updated with new columns, but the columns that exist in it are not affected. This operation requires that there are no duplicates of keys in the right data frame that match keys in left data frame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>3×2 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">id</th><th style = \"text-align: left;\">x</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">2</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: right;\">3</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& id & x\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 \\\\\n",
       "\t2 & 2 & 2 \\\\\n",
       "\t3 & \\emph{missing} & 3 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id      \u001b[0m\u001b[1m x     \u001b[0m\n",
       "     │\u001b[90m Int64?  \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼────────────────\n",
       "   1 │       1      1\n",
       "   2 │       2      2\n",
       "   3 │\u001b[90m missing \u001b[0m     3"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>3×2 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">id</th><th style = \"text-align: left;\">y</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: right;\">2</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">3</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& id & y\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 \\\\\n",
       "\t2 & \\emph{missing} & 2 \\\\\n",
       "\t3 & 3 & 3 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id      \u001b[0m\u001b[1m y     \u001b[0m\n",
       "     │\u001b[90m Int64?  \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼────────────────\n",
       "   1 │       1      1\n",
       "   2 │\u001b[90m missing \u001b[0m     2\n",
       "   3 │       3      3"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>3×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">id</th><th style = \"text-align: left;\">x</th><th style = \"text-align: left;\">y</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"font-style: italic; text-align: right;\">missing</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: right;\">3</td><td style = \"font-style: italic; text-align: right;\">missing</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id & x & y\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & Int64 & Int64?\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & 1 \\\\\n",
       "\t2 & 2 & 2 & \\emph{missing} \\\\\n",
       "\t3 & \\emph{missing} & 3 & \\emph{missing} \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id      \u001b[0m\u001b[1m x     \u001b[0m\u001b[1m y       \u001b[0m\n",
       "     │\u001b[90m Int64?  \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Int64?  \u001b[0m\n",
       "─────┼─────────────────────────\n",
       "   1 │       1      1        1\n",
       "   2 │       2      2 \u001b[90m missing \u001b[0m\n",
       "   3 │\u001b[90m missing \u001b[0m     3 \u001b[90m missing \u001b[0m"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "leftjoin!(df1, df2, on=:id, matchmissing=:notequal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>3×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">id</th><th style = \"text-align: left;\">x</th><th style = \"text-align: left;\">y</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"font-style: italic; text-align: right;\">missing</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: right;\">3</td><td style = \"font-style: italic; text-align: right;\">missing</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id & x & y\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & Int64 & Int64?\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & 1 \\\\\n",
       "\t2 & 2 & 2 & \\emph{missing} \\\\\n",
       "\t3 & \\emph{missing} & 3 & \\emph{missing} \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id      \u001b[0m\u001b[1m x     \u001b[0m\u001b[1m y       \u001b[0m\n",
       "     │\u001b[90m Int64?  \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Int64?  \u001b[0m\n",
       "─────┼─────────────────────────\n",
       "   1 │       1      1        1\n",
       "   2 │       2      2 \u001b[90m missing \u001b[0m\n",
       "   3 │\u001b[90m missing \u001b[0m     3 \u001b[90m missing \u001b[0m"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### order keyword argument"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can specify order of rows in the resulting data frame using the `order` keyword argument."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>3×2 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">a</th><th style = \"text-align: left;\">id1</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">2</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">3</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& a & id1\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 \\\\\n",
       "\t2 & 2 & 2 \\\\\n",
       "\t3 & 3 & 3 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m a     \u001b[0m\u001b[1m id1   \u001b[0m\n",
       "     │\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼──────────────\n",
       "   1 │     1      1\n",
       "   2 │     2      2\n",
       "   3 │     3      3"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = DataFrame(a=[1, 2, 3], id1=1:3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>3×2 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">a</th><th style = \"text-align: left;\">id2</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">4</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">2</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">3</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& a & id2\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 4 & 1 \\\\\n",
       "\t2 & 3 & 2 \\\\\n",
       "\t3 & 2 & 3 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m a     \u001b[0m\u001b[1m id2   \u001b[0m\n",
       "     │\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼──────────────\n",
       "   1 │     4      1\n",
       "   2 │     3      2\n",
       "   3 │     2      3"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = DataFrame(a=[4, 3, 2], id2=:1:3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>4×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">a</th><th style = \"text-align: left;\">id1</th><th style = \"text-align: left;\">id2</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"font-style: italic; text-align: right;\">missing</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">3</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">2</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: right;\">4</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: right;\">1</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& a & id1 & id2\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64? & Int64?\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & \\emph{missing} \\\\\n",
       "\t2 & 2 & 2 & 3 \\\\\n",
       "\t3 & 3 & 3 & 2 \\\\\n",
       "\t4 & 4 & \\emph{missing} & 1 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m4×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m a     \u001b[0m\u001b[1m id1     \u001b[0m\u001b[1m id2     \u001b[0m\n",
       "     │\u001b[90m Int64 \u001b[0m\u001b[90m Int64?  \u001b[0m\u001b[90m Int64?  \u001b[0m\n",
       "─────┼─────────────────────────\n",
       "   1 │     1        1 \u001b[90m missing \u001b[0m\n",
       "   2 │     2        2        3\n",
       "   3 │     3        3        2\n",
       "   4 │     4 \u001b[90m missing \u001b[0m       1"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "outerjoin(df1, df2, on=:a, order=:left)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>4×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">a</th><th style = \"text-align: left;\">id1</th><th style = \"text-align: left;\">id2</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">4</td><td style = \"font-style: italic; text-align: right;\">missing</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">2</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">3</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"font-style: italic; text-align: right;\">missing</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& a & id1 & id2\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64? & Int64?\\\\\n",
       "\t\\hline\n",
       "\t1 & 4 & \\emph{missing} & 1 \\\\\n",
       "\t2 & 3 & 3 & 2 \\\\\n",
       "\t3 & 2 & 2 & 3 \\\\\n",
       "\t4 & 1 & 1 & \\emph{missing} \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m4×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m a     \u001b[0m\u001b[1m id1     \u001b[0m\u001b[1m id2     \u001b[0m\n",
       "     │\u001b[90m Int64 \u001b[0m\u001b[90m Int64?  \u001b[0m\u001b[90m Int64?  \u001b[0m\n",
       "─────┼─────────────────────────\n",
       "   1 │     4 \u001b[90m missing \u001b[0m       1\n",
       "   2 │     3        3        2\n",
       "   3 │     2        2        3\n",
       "   4 │     1        1 \u001b[90m missing \u001b[0m"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "outerjoin(df1, df2, on=:a, order=:right)"
   ]
  }
 ],
 "metadata": {
  "@webio": {
   "lastCommId": null,
   "lastKernelId": null
  },
  "kernelspec": {
   "display_name": "Julia 1.9.0",
   "language": "julia",
   "name": "julia-1.9"
  },
  "language_info": {
   "file_extension": ".jl",
   "mimetype": "application/julia",
   "name": "julia",
   "version": "1.9.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}