Section 04 Joining Tables/17 MySQL LEFT JOIN.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "75a9774c-7aee-4e7c-aeb9-c2f1f4ec5c3e",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## **[MySQL LEFT JOIN](https://www.mysqltutorial.org/mysql-left-join.aspx)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4e21351-a8fa-4788-ad6b-c56f33fc3819",
   "metadata": {},
   "source": [
    "Learn about MySQL LEFT JOIN clause and how to apply it to query data from two or more tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "15b2aaa1-9351-4f35-89e0-60be7af2d673",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.image as mpimg\n",
    "from IPython.display import Image, SVG\n",
    "\n",
    "from sqlalchemy_utils import database_exists, create_database\n",
    "from sqlalchemy import create_engine, inspect, MetaData, text\n",
    "from sqlalchemy_schemadisplay import create_schema_graph\n",
    "import pymysql\n",
    "\n",
    "pd.set_option(\n",
    "    'display.max_columns', None,\n",
    "    'expand_frame_repr', True,\n",
    "    'display.max_colwidth', None,\n",
    "    'display.max_rows', 10,\n",
    ")\n",
    "\n",
    "pd.set_option('display.width', 65)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "76282848-583c-4f1a-9516-4e444183488f",
   "metadata": {},
   "outputs": [],
   "source": [
    "connect_args={'ssl':{'fake_flag_to_enable_tls': True}}\n",
    "\n",
    "engine = create_engine('mysql+pymysql://namlq:abc123@localhost/classicmodels',\n",
    "                       connect_args=connect_args, echo=False\n",
    "                         )\n",
    "inspector = inspect(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47a648e4-05f2-499d-aeca-91f56bbbc9da",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Introduction to MySQL LEFT JOIN clause"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ee2024a-2b60-41da-ab75-6fff5a0427ec",
   "metadata": {},
   "source": [
    "syntax:"
   ]
  },
  {
   "cell_type": "raw",
   "id": "fd1e876b-b8f4-4fbb-9355-2830554a5b99",
   "metadata": {},
   "source": [
    "SELECT \n",
    "    select_list\n",
    "FROM\n",
    "    t1\n",
    "LEFT JOIN t2 ON \n",
    "    join_condition;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "922888e5-e319-4cc6-917c-f4fdd63d03c3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAeQAAAEnCAYAAAB40jDjAAAgfUlEQVR42uydLWwUTxiHKxAVFQgEAlGBqCgJAoFAVCAQFYgKBKICgahAIBAkiIqKExXIigoE4kQFAoE4gaioIASBqEAgKhCIioqK+e+zaZPL/u+OftzezsfzJL8EUujd7s5v3p133pmZCyIiItI5c0FEREQmYEAWEREpBgOyiIjIRAzIIiIixWBAFhERmYgBWUREpBgMyCIiIhMxIIuIiBSDAVlERGQiBmQREZFiMCCLiIhMxIAsIiJSDAbkgjk6Ogpfv34Nnz59Cru7u7XevXtXa2NjI6yvr4dnz56FlZWVWouLi7UWFhbC3NxcmJ+f5++In9daW1ur/9/Lly/5PYjfi+rPGQwG9eeKxIq+kBEYkOX6HB8fh2/fvoWPHz/SCdQdyYMHD847j87E59+/f7/+Pm/fvq2/38HBQfj7928Q0Rf6okQMyJnw+/fv+k17e3u7fgt//PhxuH37NiZPTnxvRhVcR6/Xq6/r169fQURf6IucMSAnCukt3p4x59LSUhQdRssiBUjaj+s2vSet+eLmzZuMlgnetLdab968YSRNYCfNzGeQZq51eHhIYKxH3XBycsLfET9H/Pv6/71//57fw+iX34vqz3n48CGfqy8Kx4CcCKSs9vb2wqtXr8Ly8nIUAbJr0eHS8dIR/fnzJ0h5XNUXN27cqP/906dPw+vXr8POzg7zxp23Iz5/f3+f78NLAN+P78m8tL4oAANypPC2/fnzZzoL5pSiCICxq7pPdMx00M65ZcplfUEge/ToEcVYjG5J89Yj2hSpvjfXznVwPYysmXPWFxlhQI6Iyiy8GdOB8AYfRZBLWdV9JEXoKCFxLuMLfs48K2lhUsWkj3Pm9PSUkT3Xy3XzAqIvEsaA3C0Yird2lkVgpigCWW6ikyb11+/3s++gc+GivuDZMv9KepfRIyPokqF9DwYD7gf3hfujLxLCgNwRLGUgjXTr1q0oglYp4n4zv8aoQuJjyBemYKef4tcXkWNAniEswdja2iqmKjp23b17N2xubrp0ZCzx+KIKJsydWkV8/aIx7iNV5PoiQgzI7aeQWO5AAUYUQUiNnVdjnrL4lGcI8fjizp07der1x48fQabPz58/WX7FfdYXkWBAbq8Qhbf+ZDchKFXVWlAKZEyFduQLqoZZU/vly5cgs4N55xcvXnD/9cUYDMgJUjVYGi4NOIoAo64cmJmnNEU6A19QXPTkyZPw4cMHi4s6hvvP+uXV1VWei74YxoCcDlUDpaFaKZ2ZqufJc3U+bfq+YK7SeeGIOZtv5jnpCzAgx0+1WJ/qRANx5qpGC6RSk91UokGnvqCDZ/6YpU0SPzynfr9PYZ2+MCDHSVUQQUN0A4/CdL52k5OC5HK+oENnqZKkS/X8KPTSFwbkOKg6HBpeFMFBdSragRXAF/AFO0ixyYfkw2Aw4LnqCwNyJ1D2zxIMR8SqOTJgLq3Y6tNJvqBQi45b8uXg4IAArC8MyDODNA1r9aIIACpOsYyHKuGSGOMLU5cFwvN+/vw5gbh4XxiQ2ytM4S0/ig5fpaFqfi37dN2ZL4q8drFtGJBnC2vx2MHGyml11TQ2adzsdjca8oWjIJkEVdlkT4rwhQG5RSg+WVxcjKJjV2mLDolNFnJghC/oYDmX13lC+Vd9Qba+MCC3AwvcrZ5WrYj9mqlCTpFxvuBoPwp6RC5QgU/KOitfGJBbpNfrmZ5Wbaex2Toyqc0wRvmCrRM5bEDkkjCtwfRG8r4wILe3NZxFW2rmRV8cNxgz43zB4QP8TOQae5qzHIpAnJwvggG5NTiI26VMqhNxGDyHxsfIKF9wdu7+/n4QmeL6ZaY9kvFFjQG5lX1ZSZG4wYfqWhS8RJOqG/JFtN9R8sI2V3hAPjo6opAgis5YqVhSdWe+cLQiXcDZ18wtR+eLJgbk6T/0KDphpRrBb/Rez3aGUgjVSyF1C9H4YiQGZFPUqhxR7EJ7rTBdKEWytbVFm2z6opg2mXVANkWtUlNVQMXaX1PUUizjCgvxRe5kG5Crh0onE0Unq9QlxFpfTkmamS8IzgRpkVgYsfQOX2R/eliWAZl5h4WFhSg6V6WuuJEI+wG34Qs3ZZBkOE9hN32RK9kF5N3dXeeLVRaiHW9vb7fiCwq5KOgSiR2yOrTXpi9yJKuA3Ov1ouhIlZryeuVp+oJDItxDWJKC+eOlpaX/+SI3sgnIVSVeFJ2nUm1ofX2d1PK1fbG8vOx8sSQJ7ZYleQ1fZDXlknxA5mGsra1F0Wkq1aI4dYkj7a7qCzozz6KVpKH9rq6uNn2RTbtOOiDzEFZWVqLoLJWagQiqVKBe2hcE55OTkyCSOrxsMjJu+CKLg0+SDchV+oK1aVF0kkrNUMyl0f4v7IuNjY2s0noiwBxywxfJT8ckGZCrghRufhSdo1IdiKpTfPBPX2xubgaRXKHauuGLpAsWkwvIh4eH7kmtVKPzwRfNpSE7OztBJHf6/T7tPYugnFRArtIRjoyVagTl79+/D/ui3vxjb28viJQCO3jR7od8kWT6OpmAXBWqOGes1AjNz88P/7neSEGkNGj3BOWhOeXkCr2SCMhVQYrV1EqNVjHbCopcYKSMD5Jd6hd9QHadsVIX071796ymluLp/8fe+YLGlbVx+IoVERFZiIgoNNCIiIgRFRG7MKKwERERgY2IqIiYQigREaUMBBoRSsWIiClURERERIRSSkXEiBEREW2JGGgpEREVKUSUElHxbt5LWy5nM83cPzPznnueBx6+8rXJku3+zm/OPeee46wpz8/Pe5ML84XMCVyIvbu0tCQAodNoNLzMhelC3tzcNDHIIfqkfogFCJ16ve5dLswWcrPZNDG4IfqoXigBEDq1Ws2rXJgs5P39fa5QRMypXrkIEDI/9iB5kwtzhdxut/XVDRMDGqLPXn2o5X1kCJ7Ly0vdbe1FLkwV8tnZmYyPj5sYzBDLoL6Xqad4AYSMvvo0MzNjPhdmCvnq0YJ+ijExiCGWyUqlwk1PEDx6ctfk5KTpXJgp5KubO0wMXohlVDe3AITO27dvdUnUbC5MFPKbN29MDFqIZXZvb08AQqfZbJrNRSQ3wLoxYjm0um4GMGD0oBCTuYikO6wbI5ZMi+tmAINGN3lNTU2Zy0Ukv4F1Y8TyaW3dDOBaAlxPjqQLrBsjlldL62YAXQhuPTmSa2DdGLHcWlo3A+hOWOvJbiGzbowYiFbWzQB+Q1DryZEMmHq9bmIwQsRIVldXBSB0EuvJXXNRukLudDpcGoFozKOjIwEIHXc92c1F6QqZR9WI9rx79268lAQQOnNzc11zUapC3t3dNTH4IOL/3d7eFoDQOT091UfX1+aiNIV8cXEhExMTJgYeRPy/Y2Nj8eH7AKGzubl5bS5KU8irq6smBh1E7O7y8rIAhI7usNZd104uylHIx8fHbORC9MR2uy0AoXN4eOjmohyFPDs7a2KgQcSbnZ6eZoMXQOLAkEQu/C7kZrNpYpBBxN7d2toSgNDREyV1DTmRC38L+fz8XH8YEwMMIqY7VlMHI4DQaTQabi78LOSVlRUTgwsipndhYUEAQkcfU1cqlWQu/Cvko6MjE4MKImZXb2QDCB3tMycXfhVytVo1MaAgYr7LJwAgfuKbzIU/hdxqtUwMJoiY34ODAwEIHT0cRE/wSuTCj0JeWFgwMZAgYn71/HkAEFlbW0vmwn4hX11hZWIQQcTi1KdeAKHjzJI1F7YLmdkxYvnUPSEAIFKr1ZK5sFvIzI4RyyuzZACRjx8/6lHQyVzYLGRmx4jl9d69ewIAIvfv30/mwl4hX31q4AIJxJKrT8EAQkf7zsmFrUK++sRgYsBARE7vAugzmoVkLuwUMrNjxHBklgwgmgM3FzYKmdkxYjgySwboMksediGfnp4yO0YMzJOTEwEInXa77eZiuIW8ublpYoBAxMG5vr4uABDf2+DkYoiFPDU1ZWKAQMTBeevWrfhaOoDQ2d/fLzQXEVcsIiJXMwKk5/LyUsbHx51cDKGQV1dXTQwMiDh4l5eXBQDi4zQLy0WU41OBiYEBEQevHrL/9etXAQgdfVrs5GKghax3QZoYFBBxeO7s7AgAiExPTxeSi4hzqxGRW6AACnnjSHMxuEI+Pz/XabmJAQERh+vZ2ZkAhI7mwMnFYAp5e3vbxECAiMNXZwYAIHrzk5OLARTy7OysiYEAEYevrp0BgMju7m7uXEQpL5IwMQggoh11lylA6Oju6tHRUScXfSzker1uYgBARDvqmQQAEF+25OSij4U8OTlpYgBARDvqmQQAINJqtXLlIkpxs5OJ8COiPbknGUBiJiYmnFwUX8j6srOJ4COiPRuNhgCAyNLSUuZcRCmejZsIPiLaUw8LAgCRZrPp5KL4QtarpUwEHxHtOTY2xpWMAFd0Op3MuYh6/AeYCD0i2pXXnwCcDdApcxH1OAU3EXhEtOvW1pYAQLzEmykXUY+L1CYCj4h2nZubEwCIN0FnykXU4zZuE4FHRLuOjo6yjgzgXDbh5CJXIet7VCbCjoj21YMRACC+I9nNRf5CbjQaJoKOiPbd2NgQABCp1WpOLvIXsr5HZSLoiGhfvZwdAET29vacXOQvZH2PykTQEdG+IyMjcnl5KQChc35+7uYiXyEfHx+bCDki+mO73RYAEKlUKqlyEd0w5TYRcET0x+3tbQEAkZWVFScX2QtZF6JNBBwR/XFtbU0AQOTZs2epchFxIAgiFun8/LwAgMjBwUGqXEQ3PP82EXBE9MepqSkBAJGTkxMnF9kLWU8YMRFwRPRLdloDiJ7Q5eYiWyF//vzZRLAR0T91ZgAAojNjJxcZCvnw8NBEsBHRP/f39wUARNeOnVykL2Tdom0i2Ijon1zFCBCju6t7zkX0m29iItiI6J96HywAxJNbJxfpC1nvcDQRbET0z9nZWQGAePm351xEv1mINhFsRPRPPQMfAOK7kZ1cpCtk3ZptItSI6K96uD4AxK8Q95SLqMvLzCYCjYj+yiUTAF0umUhTyK1Wy0SgEdFfd3Z2BABEFhcXnVykKOTd3V0Tgcbi/PLlS1E7Bnu67efbt29yhf6viZ8fKWSAIaG7qzMXsn6BiUBjcRbFy5cvu/4zbt++Le/fvxcHEz8/Dl696cYyRV0ve+fOna7f/8GDB/r7v/7sn3/+Kf/++2/8exAOtVrt138DjUYjVSHrF5gINBbn0dGRfPjw4Vrd2XPy91wfPnx47fd/8uTJz1kxhYxqfH2rZZ4+fdqXD7zPnz/X4r3xa+7evSvv3r0TKD+ahV5yEXEPMuqsN2uB6qxYyz6BFjOFjLK+vi6W0c01//zzTzfdWXC3P6ez4GQZX1u8+ueSM+XkjFk/EEO5yVXI9XrdRKDRfiE7g4l+L50tU8jo/WldiZ9FZ9OpZt1atPprzYf7IUALOvm9tdCh3DSbzV9/3ysrK6kKWYNkItBou5D18XXiMbeujf38/ylkDLKQdYass+FPnz5JN/QxtTtLhnKj+7ISuaCQsfhC/vvvv/XxdHJXPoWMv6xWq+IzbiEXyM8Prz9lLbnkJDYQ6itQ6Qr53r17JgKNtgs5IYWMFHK6DWUcohIQrVYrmYtUhaxfYCLQSCGjv87MzIjPUMhgopArlYqJQCOFjP46OTkpPtPPQn78+LEkvz+Um06n8/PvWi9uSlXIGiQTgUYKGf11fHxcfKafhZzcaa2/hnJzenrqfFBNUci3bt0yEWikkNFvfaZfhayPp93vDeXm8+fPv/6+JyYmpBsRJyshhYwU8sAKWV+L4mCQAEn890QhI4WMFPKwC/n169fJ7xu/twxhkPh755E1UshIIQ+xkHUmrDNi1o4DxHlkzaYupJCRTV3DKmR3I5cWs57mBWHgbOritSekkJHXnoZUyHpeNe8dB4zz2hMHgyCFjBwMMoRCdsuYu5ADxDkYhKMzkUJGjs4ccCHr11HG4BYyl0sghYwU8gALWcuXMobrLpegkJFCRq5fHFAh6wDMncdQzPWLjx49MhFopJDRX0MtZMoYXJrNZvZC3tjYMBFotF/I79+/lw8fPkjSs7MzSeD+vn6NiZ8b++v6+rr4TIZC1oM/KGP4Xafqr9MVcqPRMBFotF/IGdFZtImfHfunDjw+k6GQkwd/qHpMZq/yXnKJyVPI+rzbRKBxMD558kR+oLPbVF/77ds3SYt+ze3bt0387Ng/t7a2xGdSFHLW5RneTQ6EWq328+9ZJ7zpCnl3d9dEoBHRX/WDvc8kT9bSR9G9cOfOncz/vt69eydQTnTduJdcRF3emTIRaET0V98LGaAolpaWshfy27dvTQQaEf2VR7AA7tOWOBfpCvny8tJEoBHRX/WGGwAQGR0ddXPRvZC5ghERi3RsbEwAQLSA3VykL+S5uTkTwUZE/+SuXwBnT5aTi1SFvLq6aiLYiOify8vLAgDxKV1OLjIU8vb2tolgI6J/bm5uCgCInljn5CJDIb9588ZEsBHRP/f39wUARObn551cpC9kPbHJRLAR0T/11UkAEJmennZykaGQlZGRERPhRkS/1FcnAULn+/fv8scffzi5yFjIlUrFRLgR0R8nJycFAEQ6nY6bi+yFvLi4aCLgiOiP+sokAIi8evXKyUWOQq7X6yYCjoj+qK9MAoDIs2fPnFzkKOS9vT0TAUdEf9RXJgEgvnbRyUWOQj4+PjYRcET0x8PDQwGAeB9WqlxEN+wQ00OxTYQcEe2rO0q/fv0qAKFzcXGRIhdOIXOmNSLm9a+//hIAEDk4OHBzkb+QG42GiaAjon03NjYEAOL7IJxc5C9kPVnERNAR0b56sw0AxCd0pc5FJDejdziaCDsi2nVkZIQTugCcO5CdXOQv5IWFBROBR0S7VqtVAYD4leFMuYh6vM/RROAR0a5bW1sCACIrKyuZchH1eB6nicAjol2Pjo4EAETPrc6Ui0h6ZGJiwkToEdGeel6BnlsAEDqnp6duLoov5KWlJRPBR0R7cqEEgMTs7Oy4uSi+kF+8eGEi+IhoTz1EHwDiyWvmXEQppuEmgo+I9tTzCgAgXt7NnIso5UK1ifAjoh3Hx8cFAETa7babi/4V8qNHj0wMAIhoR33FAwBEs+Dkoo+FfHJyYmIAQEQ76qwAIHT0NC491TJPLqIM9zuaGAQQcfhOTU0JAMSnc7m5yF/I3P6EiNzuBOCQ4qpizcVACvn8/FwvWzYxGCDicP348aMAhI5eJqG9mDcXkWRgfn7exGCAiMNTL10HANH3jQvJRZTxWbmJAQERh6ceFgQAIjMzM4XkIsq4m0zP6DQxKCDicO4+vri4EIDQ0cM/ispFlON9KxMDAyIO3sXFRQEAkbW1tcJyEeU4kcTEwICIg/fg4EAAQkdvctITuZxcFFfIHKWJiDcclclViwAiWsBuLoZXyPV63cQAgYiDUx/RAUD8xlGhuYhyXsTMO8mIgcnNTgCiOSg8F5Hk5P79+yYGCUTsvwsLCwIAollwczH0QtYTSUwMFIjYf4+PjwUgdDqdjpsLG4X845OCicECEftntVoVAIifDLu5sFPIV8/OTQwYiNg/W62WAISOPhXWvVOJXNgqZGbJiOWW2THAr0Ox3FzYK2RmyYjlldkxQHyrkx6PmcyFzUL+cR+kicEDEYtzdnZWACA+JtPNhd1CbrVaJgYQROSYTIA+zo41F7YLWalWqyYGEUTMb6VSEYD/2jtfoCi0Nwz/kmMwbDAQDASCwUAwGAwbDASCwbCBYCAQCAaDgRmCgUDYYCAQCAYCgUAgEAyEDQYCgWAwEAwGgoFgOL995u69d+/KLn+G3f3OOc8z885Vr8Kyu+9593zf+SOJkykHfRE/kJ0lK1WOnB2LpMSVio1Go98XeQSys2SlypCzY5E/e8c9X+QTyM6SlcpfBwcHSaR2OIWLfcd9vsgrkKHVaoUYVJRStxc7JkQksZp60Bf5BTIr0hqNRojBRSl1c7GSlNOIRGpna2tr0Bd5BjK02+0QA4xS6uZaX19PIrXz8+dPJpWDvsg3kH///p2ePXsWYpBRSl2vubm5dHl5mURqhyMyB3yRdyBDp9MJMdAopa7X4eFhEqmd4+Pjq3yRfyD3PmmEGGyUUsPFBTEitdOr7A76opxA7tbiXeClVGA9evQonZ+fJ5Ha2dzcvNIXxQQybG9vhxh4lFJ/amNjI4nUDuFLCF/li6ICubefK8Tgo5T6V0+fPk2U6URq582bN0N9UVwg9048CTEIKaX+EgtYRGpnZ2dnpC+KC2RYXV0NMQgppf6X3r59m0Rq5/T0lFL1SF8UGcjs5Zqfnw8xGClVs9hb+evXryRSM10PsKo6hC8I5EnD8WN8GgkxKClVozgG8OTkJInUTnc2HMYXBPJU2N3dDTEwKVWjOKNXpHa6feNQviCQp8bKykqIwUmpmsRNbCK10+sbh/LFqEC2n6xUYbJvLBKrbxwmkO0nK2XfWOS/1NU3DhXI9pOVsm8sAlBb3zhcINtPVsq+sUiNfeOQgWw/WSn7xiJjvOSI4zBD+yJMINtPVsq+scg4IHhfvnwZ3hehAhn29/c971qpe9Le3l4SqRkuiFhcXMzCF+ECGT5//hxiMFMqZ7Xb7SRSO0tLS9n4ImQgd+FJCzGoKZWjPnz4kERq5927d1n5Imwg957MEIObUjlpeXk5idROu93OzhehA7lXbggxyCmVg7hgnZ6ZSM10255Z+iJ8IPca8iEGO6Uiiy0dbB8UqZmDg4P+hcGsrs7GF+EDuW/JeohBT6moYhA6OjpKIrVyfHzcv3WW86rD7TXOPpD7NnWHGPiUiqQHDx7882sGo06nk0Rq4+zsLD1+/Pg/B3/8+PEj5UQ2gQw8uTMzMyEGQaUiCD8wK+C//aHMn4nUwqAH+DUBnRtZBXLfp6AQg6FS0xTB+/Xr16t8Qfk67OEHIvfdM8YLg77IkewCuTf4OFNWVYv3P6XpEb4glEMfgiByDzc38T7v90XWLZssAxnOz8/tKasqNTs7S/iO8kVWhyGI3IXNzc1BX2RZpi4ikHsLvVx9raoSq0ZZS3FLX3Ahexb7MEXucGgUvshuAVdxgdzbEuU+ZVWFCFne73f1xevXr7PaAiJyxbkUHPIx6Iti3tfZB3LvRWIGEGLQVCrKCVxX+YLBixm0SG4Qus1mM8sTuKoKZID379+HGDyVuk+trq4y6NybL+gx02sWyQXK0fPz84O+KCqMiwtkb4lSpenjx4/j8AWrUbPdGiJ1cXJywiEff/iiRIoL5C7sv2QpfIgBVam7HoO5tbU1Dl+4LUqyAQ88fPjwD1+USpGBDF++fEmNRiPE4KrULQ/8IDwn5gsWe11cXCSRKNAvbrVag74o/rCbYgMZvn//np4/fx5ikFXqptuaTk9PJ+6LJ0+eeNymhKC/RD3oi9IpOpB7K03ZsxZisFVqhFgRzcxgar6gHLixsZFEpsRgiRrhi2K2NVUfyAD7+/uWsFVIUYrjQvUBpuaLhYUFt0bJJKFlwhamML64EgPZErYqW2zl+PbtW7oGS9hSLKz2n52dDeeLQQxkS9iqYK2srKTLy8t0AyxhS5G0223eZ2F90Y+BPEYODw+9xlFNrUS9u7ubInKFLzjdq4oFNTIxmP3SGsnGFwkMZBjrjVFeTqGqK1HfwRfMYjjxq5rFNTIemPmura2xcCs7XyQDeSKlOq6n8yARNe6DPjjqL5tS3BBf0FuufhYjd66+0CvO2hcG8mSgJOdsWY1FL168yPaYymG+ePXqlTMauemiQQ6fKcoXBvJkYJk95/yGGMhV3mo0GsUc83eFLyg7Un50diPDqiycOc37pFhfGMjjhz1xrDi1jK3urOXl5eL28g7zBWXIg4ODJAJwdHTErWJV+MJAngyUUyirhBjgVR5iP2+n00klM8QXlCUtY1cOCwJbrVaVvjCQJ8T29rZbpNRNytPF3c96S18we+aoQ4O5MrqvN9UTytPV+8JAhvGX69iwbhlb/aGlpaXEBeo1MswX/J6jELkoQMqlG8R8AOP11hcG8uShXNdsNkMEgZqqeB94vOT1vqCUbbmyME5OTnhd9YWBHINOp8MbMkQwqMmKLT/cKyy38gUDtc9b5vD6LS4u6gsDOSZ8Umy1WpayK1A3aCzB3oMvWODDLVOSDxzq0Ww29YWBnAe9XorBXJh6i5QccO7fFxyXyMIwj+MMSvd1Yf85r5O+AAM5P1j231ttGCJQ1J3E68eeSVcLj98X/BmzafYyuxp3uvD8s4eYBVlc9qAv+jGQ84XVhZwF3Gg0QgSMunEQExyuDp2OL9hGxfPvkYqThZkul4dwVrm+GIKBXABsC2m32/TNQgSOGn4L0+bmpgNOiuMLTnri6EVm1zKWqgXved77+iIIBvLk4KB+Zgd8Cg0RQrVrZmaGT/32wYYSxxfNZtN+8/31hVkRTR9YXwTDQJ4O/X2aEOFUi3r9SlaN2q8MyHW+4PUjnNfX19li48UW1/eE2YpGpYHnjedPXwTGQB5JjE+s6sZyhlUIN/UFQbKwsJA2NjYIHsPkr34wzwfPCx9s9EVGGMiB6PZ0MBJ9mxAhlrvm5uYSMwPuY5V8uYUvCCAOrqA/XU3J9ezsjJ+X/cAsltMXGWMgB4VFFLu7u+z143q7EAEXXTxPPF87OzsONoVyS18QUJypTY+a9wWz6Gyv/7u4uODxUzng5+Hnot+rLwrCQM6ErpEwFMZyUdi/i0/oe3GbjANNpdzVF2yv4gpJ/h2z7729PRaXTbsnzffncXCSGY+LPb88Th6vvqgAAzlTumUqDIfxMGCIgByXBmc7/Nz8/CLj8AUzSvqvhDViNsoiMgKS8EcsfmJRGTNWQg8RpsB/+T3i//P3+Pv8O8TX4evxdXvfg+9HKVlfVI6BXAgYkVLe2toagxH9tmxXcHcfN4+fQYafhxJdNf1A0Rf6ol4M5MLp9tzYSsKnZ/YWsuCFT+IhBhgeBzMDHtenT5+YRXgIhOgLfVEtBnKlsD2k16uijIYooyGO0KOMhth2wtYI+liU8tDfexnZjsLvEScu8ff4+/w7xIDC10N8fcT3C9GrE9EXEg0DWUREZCQGsoiISDUYyCIiIiMxkEVERKrBQBYRERmJgSwiIlINBrKIiMhIDGQREZFq+D/0dPPlOK8+MgAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Image('../images/mysql-left-join-Venn-diagram.png')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81702f1d-cce3-4b0e-be43-153238ff5134",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "### MySQL LEFT JOIN clause examples"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d52c0b5a-c873-43d2-9c78-b805e9fb6dc7",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### 1) Using MySQL LEFT JOIN clause to join two table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "bc7d9628-88b7-491a-a36d-a9d34b35cd27",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAbIAAAEwCAYAAADSC/TPAAAACXBIWXMAAA7EAAAOxAGVKw4bAAAAB3RJTUUH4wgdADgJoEQDUAAAAAd0RVh0QXV0aG9yAKmuzEgAAAAMdEVYdERlc2NyaXB0aW9uABMJISMAAAAKdEVYdENvcHlyaWdodACsD8w6AAAADnRFWHRDcmVhdGlvbiB0aW1lADX3DwkAAAAJdEVYdFNvZnR3YXJlAF1w/zoAAAALdEVYdERpc2NsYWltZXIAt8C0jwAAAAh0RVh0V2FybmluZwDAG+aHAAAAB3RFWHRTb3VyY2UA9f+D6wAAAAh0RVh0Q29tbWVudAD2zJa/AAAABnRFWHRUaXRsZQCo7tInAAAgAElEQVR4nO3dX6jk1n0H8K+agOsEjF9KNiY4IZW2ZDNp/bDBtYbUJkkLmvXDfUjmpaQ3qRdNaR5mHrx0Q73eLFy8OAtGekjJnQeXifHLDS0XN1dTWKfelxEBO6wfhsW+Z+KHFOKGgnEciNdQ+/Rhc7RHGkkzo5k7kma+Hxj2jv4cHY1G5zfnSKufAUCCqGak5NeWiO74o7IrQEREtIyPqz/4C5fqwDCMsqtARBXDHhkREdUaAxkREdUaAxkREdUaAxkREdUaAxkREdUaAxkREdUaAxkREdUaAxkREdUaA9mCfN/nf8olIqoQBjIiIqq1jQ1kw+EQhmFEL9/3Adx5xFGn04kt22w20Ww2Y9P0dVUPrNlsotfrRfP1dVRPTb2S21DTOp1ObJnJZBJbbzKZxNZLlqvPV2WpZfRtptWfiGgTbWQg830frVYLQRBASrnwcyQNw4DrutG6nucBAEajUfS3lBKj0QjAnYDS6/UghIjW6ff7U8Gs3+9jZ2cHUkq4rot+vw/LsqJ1bNvG7u5ubD96vV6sHpZlTQW7W7duQUqJ/f393PoTEW0qCUBuEgDS87zMea7rxqbZti1t244to7/XeZ4X+7yEEKnbU8sJIVLLTFsvbR19fnId13Vjy89T/7rbxO8rES1n43pkw+EQAHD69OnCZQRBgDAMM4f7dEKI1O2p92o+ADQajbnroPaj1+tF9bAsK3VZ0zQL15+IqO42LpCtguM4kFIiCAIASB3OWxd9eFS9ut1u7jpVqj8R0UnbuECmei3Hx8ep823bnpoWhmHqso7jRMFA71nNsz31PqsXNcus/ZjHPPUnIqq7jQtkpmnCdV30er2oFzKZTKK7FhuNBvr9fjRPTVf0ZXXJgKSG/tK2B9wZEnRdd2rYb5n9ADB1A0nSvPUnItokG3nxXN0IgZQbH9R0/OHGCf1mD3VDhf5K3kxh2/ZUuepGDb3c5Db1m0zmudkjuR/JMtQ83Tz1r7NN/b4SUXHGHxqGhW9RJyqD+j9x/L4SkbJxQ4tERLRdGMiIiKjWGMiIiKjWGMiIiKjWGMiIiKjWGMiIiKjWGMiIiKjWGMiIiKjWPq7+YPJFIiKqI/bIiIio1qIeGR/5Q3XAkQMiSmKPjIiIao2BjIiIao2BrAKazSYzOBMRFTR3IGs2mydZj0ppNpup12Imk8lWfQ5ERHUwdyBrt9snWQ8A1eqZuK47MxszERGVb2YgGw6HMAwDvV4PhmHA9/111Kt0Ozs7GI/HGA6HZVeFiIhyzAxkjuMgCAK4rgvP89DtdlOXMwwjevm+nzoMp/e41PCdYRjRsmEYwrKsWE9ILzc53GcYRhRo1d/6+2TQTdYRuDtc6Pv+VPmDwQCtVit1f2ft36J1S9YvGUAXrTsR0baYa2ix1Wphf38fBwcHqfMNw0AQBJBSQkqZGewU3/fRaDSi5QFgNBrBtm0IIbC/v59abhAEUw324eEhpJQQQqDVasXe93q9WB1VOVJKHBwcTA1jJv8vnWma8Dyv8BDjvHUDAMuyorqp5ZepO1GdJH+w8sXXIi8AkABkUUEQSNd1p6YLIaRt27Fptm1LIYQUQkgAmfPzytWXSdZbn6e/D4JAqv3UX57nRXVJlhMEQWq9VZ3z9m+RuqXNk1JK13VlEAQL133TLft9pWpK+47zxde8r7Xefh+GIYA7PR0pJQaDAQxjehjtJLiuG+vVSK3naNt27rqDwQC7u7szt6H2bxXG43H09zJ13ybf//73F5pO1ZP8nvPFV95LWTqQOY6Dfr8fC0a+78M0TYRhGA2BJecDd4fujo+Po3lCiMxy1d+maS5dx0WGC03TRLvdxrVr12LTsvavCL3s4XCIMAzhOM7Sdd8mV65cWWg6EW2Gj89eZDYhBCzLit6rSOl5XjTddd2o99DtdqNxTX35druNVqsF13Wxv78PKWVsOdu2MRqNVlJHz/MWWr/b7U7d3JG1f0WcOXMm9TMBlq87EdEmM3BnjDHWcBJVVXRhN+X7ahjGQtOpOvKOK1EW9b3hI6qIiKjWGMiolrJuwV1kumEY+PSnP43//u//LnNXiGhJDGRUS1l3MC0yXUqJ3d1dPPXUU2XuChEtidfIqFZWfY3s3XffhWVZuH79Oh566KHVVpbmxmtkVASvkREBuP/++/Hkk0/i6aefLrsqRKk6nQ6zbszAQEZbr9vt4ubNm7hx40bZVSGiAhjIaGNcvnx5oenKH//xH+PSpUu8VkZUUwxktDGWeUTVE088gXfeeQc//elPV1sp2koqI4V6JZ/G0+l0YBhGtJw+X81LWy+rfP0h4nllp93RuwlW8mQPorr72Mc+hitXruB73/seWq0W/uiP+BuPiul0Ouj3+xBCRI/TU0FDZfZQbt26FbvBJbnucDhEq9WKPTXI9330er1oPd/3YVlWbHtpZRuGET01Sa23SSQASVQHJ/l9/eijj+TDDz8sf/zjH59I+ZRtU9ohlZHC87zYdM/zJIAoy4XrurH3eevath3LtJFcJrleWtlqvWTGjrpDGU+/rzqV7DMpLYkmbR7DMLC3t4dLly7hgw8+KLs6VEPqoeenT5+OTVfv1XxF70FlrdtoNKK/1cPDe71eNDyoP4c1q2wACIIAYRimDkfWXSUCmZ5ZuWyu6/Lp8lvs61//OizLwo9+9KOyq0KUSU84rF6zEho7jgMp7yQoBu4k861Ku7usSgSyKtnZ2cF4PF5LjjSqpqtXr+KZZ57Be++9V3ZVqGZU70hPTaW/z+o95a2r5ybMWmYRjuNEwSzZQ6yrpQOZfgeM7/upw3B6j0sN36mubbPZRBiGsCxr7rtrVDJONW84HMbeJy9iJusI3B0uVHf26AaDAVqtVuY+TyaTWJl60FtF3ahcZ8+exaOPPornnnuu7KpQzZimCdd10ev1Yr2dXq8H13VzcymapgnbttHr9aJpvu/HEvZmlT9rFGkymaS2L3mBtU6WCmSGYcS6uLO6tr7vo9FoxJ6BNxqNYNs2hBDR3TTJcoMgmAo2h4eHkFJCCIFWqxV7r38R1OOJ1Ovg4GCqOy0Tj8VRCT+zvhxHR0dReWr7J1U3Ksfe3h5838dvfvObsqtCNbO/vx/lKlQ/Uj3Pm7pjMY3Kt6jWu3XrFlzXnSrfdd1Y+fPQr6u1Wq2puxzrrtDdQkEQSNd1p6YLIabujLFtWwohortrsubnlasvk6yvPk9/HwRBdFeL/vI8L6pLspwgCFLrrddZrau/lFXUjbIV/b4W4bqu7Ha7a9nWtlvncaXNgXXetai6xqZpQkqJwWAwNSR3UlzXzbwoOiuj82AwwO7ubmzaZDKBZVlTT1dfdd2ofJcvX8ZgMMBbb71VdlWIKEfhQOY4Dvr9fiwY+b4P0zQRhmE0RJacD9wdutMvWKqLjmnlqr8X7QanlbXIHYmmaaLdbuPatWuxeupd/aLXtZatG528Bx54AJ1OB1euXCm7KkSUY6knewghYhcLVe9EjQ8Dd3odqufT7XZj47lq+Xa7jVarFf2vcyllbDnbtqOx42Xr6HneQut3u93YzSuO42Bvby+q36LlrbJudPIuXrwIy7Lw+uuvM80LUUUxHxnVShl5q5599lmMRiO89NJLa9vmtmE+MioiygAPBjKqkTIavNu3b8OyLLzwwgt47LHH1rbdbcJARkUwsSbRnJjmhajaGMiI5sA0L0TVxUBGNAc9zctHH31UdnWISMNrZFQrZV5LkVLikUcewXe/+11861vfWvv2N9kmJXmk9WMgo1op+6aAl19+GefPn8ebb76Je+65p5Q6bCIGMloGAxnVStmBDAD++q//Go8//jifwrJCVTiuVD+8/Z5qqQoN3muvvYZz585BCIH77ruvtHpskiocV6of3n5PVBDTvBBVCwMZUQFM87J50nIpVpWe45G2JJCd1EFXSUKT6nRCUDGnT59Gu93G1atXy67KVqnqecW2oFyVC2SrCDonGbiS5bquy6fWbymmeVm/drtddhUysS0oT+UCWd3s7OxgPB6vJbcaVQvTvKzPcDiEYRhRluOi6ZNOEtuC8iwdyFTq7OSXS5+e7HKrpJrJ9ZrNJsIwhGVZ0S+byWQSKyf5JUluP62MLHllq6ECwzCi4YGscgeDAVqtVqHtJD+L4XCY+tnM+rypHBcvXkQQBHj99dfLrspGcxwHQRDAdV14npf5Xx+y2h11Dvu+H5uuL6unVEqbr863rLKA/LYgbZhRH+VZtC1I1m9W2zir7nW2VCAzDANBEExlN05OD4Jg6kM7PDyElBJCCPR6PUwmE4xGI9i2DSEE9vf3AQBHR0dROUKI2JckbftpZWTJKtv3fTQajVgG6LxyVaLQrMCZtw/Jz6LVak19Nvr+6tmkDw4OeMG3ZPfffz+efPJJPP3002VXZeO1Wi3s7+/j4OAgdf487Q5w9xb/5PJ6wlw1f9b5lvzvArPaglnmbQsAxDLVp7WNi9a97iQAuaggCKTrunNPt21bCiGkvPPpZc7T/5ZSSiGEVHXU65q1nbQyku9nla2m27Y9s9wgCKbmCyFi62ZtZ9Znob8PgmCqDADS87zUz2BTFf2+nqT3339ffuYzn5GvvPJK2VWprWWP66x2R52Decvr523e+ZYsS21nVluQbBf05dRnkDUv+T6tTXNdVwZBsHDd60ztW6WvkU0mk9ivDrnCXxB5ZZumCSklBoNBapc9y2AwwO7u7ontg+u6sXKk1gum8jDNSz2oTPXzyjvfZpWV1hakCcNwoTrlGY/H0d/L1L2OCgcyx3HQ7/djjbzv+6nT1d+mac5VthAi+lfv7utjxFnbT5aRt42sstXfapjg+Ph4rnJN00S73ca1a9fm2s4i0vaXd0hVB9O8lGvRdidteT3wLHu+pbUFpmkiDMNoiG/Zm0L0sofDIcIwhOM4W9lWLNUjU+Oy6oKiivhSytj0vb09jEajucpst9totVrodDpwHAfj8Tj1hpG87etlKJZlRcs1m83csrvdbjS91+vllpvU7XZjv4xm7cMikvt75syZpcqj1WGal/It2u4kz6dkj3rZ8y3ZFgCA53lRW3R4eLhU7+jMmTNR3VqtVmy0Z9vaCj5rkWqlys/kk0zzUliVjytVFx8aTLVU9QaPaV6KqfpxpWriQ4OJTsDXv/51WJaFH/3oR2VXhWhrsEdGtVKHX+5M87K4OhxXqh72yIhOCNO8EK0Xe2RUK3X55X58fIyHH34Yb7zxBj71qU+VXZ3Kq8txpWrhzR5US3Vq8DqdDu699154nld2VSqvTseVqoOBjGqpTg3er3/9a3zxi1/EL37xC3z+858vuzqVVqfjStXBQEa1VLcG7+LFi3j77bcxGAzKrkql1e24UjUwkFEt1a3Be/fdd2FZFq5fv46HHnqo7OpUVt2OK1UD71okWgOmeam+tDxhVC9bEcj0JJmGYaDT6UTJLpfJ56XKTeKJQbput4ubN2/ixo0bZVeFVizZtiySLYNWp3KBTM+YusoyhBBROoP9/f0oVcu8T+TPKtd13Y1/sjQth2leNpvetqgHF8+b5WIV7R1VMJDVzc7ODsbjMX+FUS6medkeUsoo6z2tx9KBTO9S679Ckt3t5DrD4XBqvWaziTAMYVlW1MtRQ4BZ3fbk9tPKyKu72kaz2YTv+9E0fchAzc8qdzAYxNKMJ+XtQ/KzGA6HqZ/NrM+bqo1pXk5WVnuTdm4nl7csK7c8dZ5llZXGdd0od2HW+Z/VpvAcX9xSgcwwDARBMJWBNDk9CIKpA394eAgpJYQQ0a+X0WgE27YhhMD+/j4A4OjoKCpH5djJ235aGUA8H1nel0NKCd/30Wg0Yhmds8oF7ibgzAqcefuQ/CxardbUZ6Pvrz6EcXBwwF99NfKNb3wDn/zkJ/Hiiy+WXZWNMk97A9y9IzK5vJ74Vs2fdZ4tcndl1vmf1qbwHC9OApCLCoJAuq4793TbtqUQQso734DMefrfUkophJCqjnpds7aTVkbyvaLKUttIbtO27ZnlBkEwNV8IEVs3ax9mfRb6+yAIpsoAID3PS/0MNlXR72tVXL9+XX72s5+Vt2/fLrsqlbLqdkg/F/Vy05bXz9e88yxZlr6dJNd1o7Yh7/zX1+c5vjj1GVX6GtlkMoFlWbFfKCdFz9SqbgQZDAYL3YU0GAxi6dKB1e6D67qxcqTWC6Z6YJqX9Vs0C3PeeTZPWZPJBP1+H47jLHz+8xwvpnAgcxwH/X4/1sj7vp86Xf097x2CamxZCBHr9utDglnbT5ZRhCpHDRkeHx/PVa5pmmi327h27Vps+ax9WETa/vJuyXq6evUqnnnmGbz33ntlV6X2Fm1v0pbXf3wue56pwBUEAYD5zn/VpvAcX07hoZpklzmty4eUIbrksnr32vM8CSDq/tu2Heti6+tmbT+tjFlDi2l1nKdcfWhR3x+9vLx9yPssku+T+7uNQw7LfF+r5Jvf/Ka8fPly2dWojGWPa1Z7k3ZuJ8+jIAhyLwWo8yytLP3cztqHvPM/2abwHF+M+pz4iCqqlU15lBHTvMRtynGl9eKzFqmWNqnBY5qXuzbpuNL6MJBRLW1Sg8c0L3dt0nGl9WEgo1ratAaPaV7u2LTjSuvBQEa1tGkNHtO83LFpx5XWg2lciCqAaV6IlsceGdXKJv5yv337NizLwgsvvIDHHnus7OqUYhOPK5089siIKoJpXoiWw0BGVAFM80JUHAMZUQUwzQtRcScayGbl7CGiu5jmZTuovGa0OuyREVWEYRjY29vDpUuX8MEHH5RdHdKo5JgnkRtMT+KblUCY8jGQEVUI07xUk0rtNG8Gj0UJIWKpW1qt1tyZMprN5tYn32QgI6oYpnkhKSV6vd7WB6h5LRXI1C+BvC7xcDiM5iV/YSS708l5865bNMcXURWdPXsWjz76KJ577rmyq1J56nqT7/uxNiSvfUi2O+p6Vdq1K723o8pfdpuWZc21b67rRrnKstrZZrOJMAxhWVYsd9m2tY9L98j07KdCCLRardj8w8PDaJ7+C8MwDARBEK0bBMFUMMtbV++GHxwc8JcLbZS9vT34vo/f/OY3ZVelNtR/ps5rH5Ltjp70ssrbPDo6Sm1nR6MRbNuGEAL7+/sz67Kplg5kesZk0zThum6sV6Y+XNM0ozThw+EQruvCcZxoOcdxYNt27APPWheI/+IIwxBHR0fL7gpRZZw+fRrtdhtXr14tuyqVF4Yhut0ugPz2Ia3duXDhQi22ee7cubl6dNvaPq78Gtl4PF51kVNc14394pBSRl8qok1x+fJlDAYDvPXWW2VXpdLUj1xlle1DGIZr3yZwZyix3+/DcRxMJpPYyNesx3hta/tYOMW4bdtRim4ppQyCIFZWslzbtqUQIpoXBEFsXT2N+CLr6nWgzbbM97WO/umf/kn+3d/9XdnVOHFFj6sQItZuqLKy2oe0ecl2R7Uzqj3T251VbNO27dj6etumytfXCYIgVp7nebHPyrbtWPnb1D6q783SPbIzZ85EXdhWqzX3Qz/lH24xVevu7e1hNBrNta4aI1brnjlzZpldIKqsixcvIggCvP7662VXpTby2odZbYfnebAsC4Zh4PDwcKrntYptpj1TU21TDR1KKaPhSMdxMB6PU2+KA4B2u41WqxXd7LGN7eNST79vNpsYDAYn9n8riJK28Snpzz77LEajEV566aWyq3Jiyjquk8kEu7u7c/+Ipmrh0++JaqLb7eLmzZu4ceNG2VUhqiQGMqKKY5oXonxMrEm1so1DiwDw4Ycf4ktf+hJ+8IMf4PHHHy+7Oiu3rceVlsOhRaIaYZoXomwMZEQ1wTQvROk4tEi1su1DUC+//DLOnz+PN998E/fcc0/Z1VmZbT+uVAyHFolqiGleiKaxR0a1wl/uwGuvvYZz585BCIH77ruv7OqsBI8rFcEeGVFNMc0LUdzaAllarp9F5hPRXUzzki/tUU5lYxt3craqR5aWElwlrFtFvh6VCHTTc/9Q+ZjmpZhVnu/LajabU0k+k4mJaT5bFcjSmKYJKeXSz4tc9CGjRMtimpfFrep8XxUhRCzdSqvVmjujc9oP82219YFsVaSUUSJQonV44IEH0Ol0cOXKlbKrQisipUSv12OAWtBSgUx107O6xfq8tKymWfPVWLLv+7Gxbn15/VeL3kVXX4C0aVmS21BDhMnt5NWBqAxM85J9rmedx/r5rno1We3YrPmqvHnai7zMzjrXdSGEAJDdxjabTYRhCMuyovQts+qyyZYKZEdHR1GXWOXAUQzDQBAE0XzXdWPrzpqvqNtxDcOIdcEPDg4wmUzg+z4ajUYsc2ratEUcHh5G+6T/OsqqA1FZ7r//fjz55JN4+umny65KKfLO9azzOEnPvpxsx2bNz2sT5m3j8mS1saPRCLZtQwgRjQRte/tUOOOuymSqv6SczmiqllVZUWfNV+UqKlNr8uV5XrSsnnE1bZqU05lYFX1byc9CrZNXh3m2QauxzPd1U73//vvyM5/5jHzllVfKrkphRY9r1rmedR4n56Wdr67rRhmW8+bntQmz2risspPbz2pjk+vP2z5tGrWfhXtkk8kk9ktFrvg/MiZvmnBdN7YtKSW63W508XYwGERd77Rpq5BVB6IybXOal5M418fj8dzzV90mTCYT9Pt9OI6zcBu77e1ToV9CyV8cnudN9WzUrwop7/x60H+N5M1P/nJJW15tW//F4Xle9EpOU9so2iPLq0PW8rR6Rb+vm+7//u//5Be+8AX5H//xH2VXpZCixzXrXJ91HuvT9fNY9WzmnZ/XJsxqA5Nthep9qXVmtbG2bcfKn6d92jS42/ss3jDYth3rwurlJLvEQRCkDv+lzU8LZMnl9S+wPj1vml5faMMR8wayvDoky05+qWg1GMiyHRwcyEajIT/88MOyq7KwZY5r1vmvywtkqu1Kq8Os+Xltwqw2MK3NSMprY9V7FbDy6rKp1L7yWYtUK3wmXzYpJR555BF897vfxbe+9a2yq7OQso5rs9nEYDDI/H9ls+ZTufisRaINYxgG9vb2cOnSJXzwwQdlV4dobRjIiDYI07zQNuLQItUKhxZnq2OaFx5XKoJDi0QbimleaNuwR0a1wl/u8zk+PsbDDz+MN954A5/61KfKrs5MPK5UhPreMJBRrbDBm1+n08G9994Lz/PKrspMPK5UBAMZ1RIbvPn9+te/xhe/+EX84he/wOc///myq5OLx5WKYCCjWmKDt5iLFy/i7bffxmAwKLsquXhcqQgGMqolNniLeffdd2FZFq5fv46HHnqo7Opk4nGlItZ+16LKMVZ0PhEtbtvTvNB22Krb79NSg6vEdavI25OXfI+oLN1uFzdv3sSNGzfKrgrRidiqQJZGpYFY9llqzWZzKgHeNiW1o+raxjQvaT9aV7EsVdPWB7JVGY1G0d+maU7lUyMq0xNPPIF33nkHP/3pT8uuCtHKLRXI1LBc1nCaPs+yrKn1s+ar62W+70cX85LL+74fTW82m9F09csqbVqW5DaGw2HqdvLqkBSGIZ+YTZXxsY99DFeuXMH3vvc9fPTRR2VXZ6WS53qz2UQYhrAsC51OB0B2W5VcNu1avd5jW6RdofVZKpAdHR1NDacphmEgCIJovuu6sXVnzVfUXUyGYcQynx4cHGAymcD3fTQajVgG1bRpizg8PIz2qdfrRV/YrDokdTodBEGw8HaJTtI3vvENfPKTn8SLL75YdlVWJu1cH41GsG0bQgjs7+8DyG6r0pZdZFtUHYUT2iUTualykplN1bIqqdys+apcRWVlTb48z4uWTUvamUzOuUyG6Lw6JJffhoR2ZVnm+0pSXr9+XX72s5+Vt2/fLrsqMUWP67znelZblVw2Lamvmp+1LSqPOpaFe2STyQSWZcV6KKuUvMbkum5sW1JKdLvd6GaNwWAQDRmkTVuFrDoozWYTTz31VGwaUZVsWpqXec71ZduqMAzn3haVo3AgE0LEhgP160WO46Df78cO9O7u7tzzk9KWV2PfarumacLzPBwfH6dOW1ZeHYC7QcxxnKW3RXSSrl69imeeeQbvvfde2VVZWt65LoSI/s1qq5LLmqaJMAyjSwb6+X4S7QqtTuGhGtu2Y0NsejnJrnwQBKnDf2nz07r3yeX14TukDBekTdPrC22IILlech/1YYe0OmQNO6YNY9Jylvm+0l3f/OY35eXLl8uuRmSZ45p2rqv2SF3CyGurksuq92qa3gakbYvKo44FH1FFtcJHGa1G1dK88LhSEXzWItUSG7zVqVKaFx5XKoKBjGqJDd7qVCnNC48rFcFARrXEBm+1qpLmhceVimAgo1pig7daVUnzwuNKRaw9jQsRVQ/TvNAmYI+MaoW/3Ffv9u3bsCwLL7zwAh577LFS6sDjSkWwR0ZEALYzzQttFgYyImKaF6o1BjIi2ug0L7T5GMiICMBmpnmh7bDyQKYS2DHpHFG9GIaBvb09XLp0CR988EHZ1SGa28oDmUp1oLIj69lViajaNi3NS52wrSyOQ4tEFLNJaV5oOywdyAzDiF4qX4+6t7/ZbCIMQ1iWhU6nkzrkqJYlomo4e/YsHn30UTz33HNlV2Vuae1QcnqyrVHJMdW84XAYe58sZ95lZ9Unbb1kW6mmqeXYU5ttqTxAQRCkTlf0XD6e58XyiAVBEHtPNMsy31ea35tvvinvv/9++T//8z9r2d5JtUP6dJU3UJ+vcpCpXIPJ90WX1SXzmSXXU/OSbaVaTi1L09T3pnAgC4Ig9kEnC1f0gzNrHtEsDGTr47qu7Ha7a9nWqtuhrOnJoJI1r+iyWUl21Q/2vHLSkvgmEwxTnPp8136NzPM8+L6P4XCIRqMR3RRCRNVy+fJlDAYDvPXWW2VXpVZc14WUMvbqdrsLlaFumhsMBtFwJGUrHMgcx0G/3499wMlxYkUIEf197tw5HBwc4PDwEBcuXCi6eSI6YQ888AA6nS/DKBMAAA9ZSURBVA6uXLlSdlUyZbVDadPV3yf54zltu+qa1zxUW6naUtM04Xkejo+PV1vRDfPxZVYWQsCyrOi9THngZ7vdRqvVguu62N/fh2maaDQaGI/H7I0RVdzFixdhWRZef/31UtO85Mlqh6SUsRs8bNvGaDRae33mzcCdbCv1uqe1rXRXKU+/73Q62NnZgeM4a9smbQY+JX39nn32WYxGI7z00ksntg0eVyqitKffTyYTjMdjBjGimuh2u7h58yZu3LhRdlWIUq01kDWbTViWVXpadSKaH9O8UNUxsSbVCoegyvHhhx/iS1/6En7wgx/g8ccfX3n5PK5UBBNrEtHcmOaFqoyBjIjmwjQvVFUcWqRa4RBUuV5++WWcP38eb775Ju65556VlcvjSkVwaJGIFsY0L1RFDGREtJCsNC//9V//ha997Ws4deoUTp06ha997Wt45ZVXMst5//338a//+q/48pe/fNJVpg3HQEZEC0lL8/LDH/4Q3/nOd3D+/HncunULt27dwvnz5/Htb38bP/zhD2Pr//KXv8SFCxfw4IMP4ic/+Qm+//3vr3kPaNPwGhnVCq+lVMPx8TEefvhhvPHGG/joo4/wF3/xF3jttdfw4IMPxpb71a9+hbNnz+LmzZu4efMm/uVf/gWvvvoqvv3tb+Mf/uEf8Kd/+qcAeFzL1Gw2MRgMavnIQPW9WepZi0S0nU6fPo12u42rV6/iz//8z/E3f/M3U0EMAD7xiU/gc5/7HL7whS/gz/7sz/CP//iP+Ld/+zfce++9JdS6HlSSzWRQn0wm2N3dXcvzIutm5UOLzWZz7mymiyxLRNXyz//8z/j3f/93PPHEE3jxxRensjEbhoE/+ZM/wauvvorf/e53eO211/D3f//3+MQnPpGbubksVWqPXNdd6Kn5247XyIiokAcffBC/+tWv8Pzzz+Nv//Zvp3JwSSnxv//7v/jyl7+M++67D2fPnsXzzz+P3//+91PLUdzOzg7G4zHzkM1pqUDWbDajX1STySTqEluWFf2amEwmsV9e6sCkLQsgtmxWfjMiqg7HcfCf//mfqQk4f/vb3+Ktt97CG2+8gcuXL+MnP/kJHnzwQVy4cAG//OUvV1aHZLuh2iOd3uOap+1KlpvsOar2TG/b9PfJ9iutbVPb9n1/qvzBYIBWq5W6v7P2b9G6JeuXDKCL1n3dCgcy3/fRaDRiv6hGoxFs24YQAvv7+wCAo6OjaBkhRHRg0pY1DCP2K+3g4KAyXX0iSnfq1Ck888wz+MpXvoIXXngB77zzDt555x38+Mc/xl/91V/h6tWr+PSnP43HH38cQRDg5z//OQDgL//yL9FqtXB0dLTU9g3DQBAEc2djnrftSpYbBMFUg314eBhr2/T3vV4vVsdZbVuyZ6qSahYdYpy3bgBgWVZqO1207mWQd+qxGCGEBCBt245Nt21bCiGmltNfacsGQTC1HADped7CdaPNVfT7SicvDEP51a9+VZ46dUqeOnVKfvWrX5VhGGYu//vf/14+//zz8uzZs4WPaxAE0nXdqelCiMy2aZ62K6tcfZlkfZNtn3qf17apuiTLCYIgtd6qznn7t0jd0uZJKaXrujIIgoXrvm6qPoV7ZKZpQkqJwWCQ2hUF7nQ99UgvZ0Rt13Wnxs5n/boiomp45JFH8LOf/Qxvv/023n77bfzsZz/DI488krn8vffei+985zt49dVX11K/MAwBzNd2rVpe22bbdu66g8EAu7u7M7eh9m8VxuNx9PcydV+XpYYWgbvd3+Pj42ieECL613XdqXV0alnHcdDv92NfKt61Q0R50toN3/dhmibCMIyGwJLzgfy2K61c9fei/99q2bbNNE20221cu3YtNi1r/4rQyx4OhwjDEI7j1KZdLvz/yLrdbmy8WPW22u02Wq0WXNfF/v4+9vb2ouU8z4uVkVxWCAHLsqL5yeWJiJKS7YZqizzPi6a7rhv1HuZtu6SUseVs2y78f7iWbdu63e7UzR1Z+1fEmTNnUj8TYPm6rwOf7EG1widAbCYeVyqCT78nIqKNwEBGRES1xkBGRES1xkBGRES1xkBGRES1xkBGRES1xkBGRES1xkBGRES1xkBGRES1VutAdpI5cNLy/RARUfXUOpCl0RPAVfHhlkREtFqVC2R6ltNFqOynQogo1cDOzg5ThRMRbbjCT7+vmr29PQghYikWHMcpsUZERLQOS/XIVO9JH85L9oD0eclrWs1mM5qurkmFYQjLsqJhwVnlA/PnCcqrS3K+nrYgbX5abjUiIlq/pYcW9QzQQgi0Wq1onmEYCIIgmh8EQRRAfN9Ho9GIZY4ejUawbRtCCOzv7wMAjo6OMsvXNRqN3Hrm1SVtvp4QVM3XM6QeHBwUGgIlIqLVWjqQqYyqwJ0ekeu6GA6HGA6HcF03NrznOA5s28ZkMsG5c+fQ7/ejOwOzelPnzp3L7SXNY1Zd0uZfuHAhtj4Q75GFYYijo6NC9SEiotVZ+c0e4/F4ruVM04SUEoPBIHPIcDKZxHp8WUn3VDruk+whua4bq4eUEt1u98S2R7SNksP/fPGV99JJALII27al67rR+yAIYmUBkEEQxObbti2llNLzvGi653nRe9u2o3WCIIiV73neVPlZ89Q0VVZeXdLm27adO1+vF63PMt9Xqi51XPniq+Drzh9F2LYdBZCscvR5emBIzlNUeSpQ2LYdLZMXyKS8G0j15eetixAiNj8Z6JLzk2XTeizzfSWizWRE0Sxj2C5Ps9nEYDCYebcg0aqo4YQi31ci2kyV+w/RREREi2AgIyKiWltqaJFo3Ti0SERJ7JEREVGtMZAREVGtMZAREVGtMZAREVGtMZAREVGtMZAREVGtMZAREVGtMZAREVGt1SqQqYzURERESq0CGRERUdLSgUxPcub7fup0PQHaZDKJskIrek/LMO4k2UyW2Ww2EYYhLMtCp9OJyvF9P1o22VtLJl4jIqLNs1QgMwwDQRBMZUxOTg+CYKGgcnh4CCklhBDo9XqYTCYYjUawbRtCCOzv78eWl1LC8zwcHR1F04bDITzPW2b3iIioBj5edMXhcAjXdeE4zszpjuPAtu25r2+pQGWaJmzbzlwuDEOMRiMAQLfbhWEYUTDd29vDYDBYaJ+IiKh+CgeyVQrDsNB6ySDneR5838fp06fRaDSY8JOIaAsUHlp0HAf9fh/D4TCa5vt+6nT1t2maME0TYRhGvTN9uXkIITLnnTt3DgcHBzg8PMSFCxcWKpeIiOppqR6ZEAKWZUXvVY4oKWXsmpht29EQIHCn56TWc103d/hQ12630Wq14LpuaqAyTRONRgPj8Zi9MSKiLbFxiTU7nQ52dnamrt3RZmBiTSJK2qj/RzaZTDAejxnEiIi2SCVu9lgF9f/M8q6hERHR5tm4oUXabBxaJKKkjRpaJCKi7cNARkREtcZARkREtcZARkREtcZARkREtcZARkREtbbWQLbt+cG2af+ZzZuI1qU2PbJmsxlL1JlMzrmMTqdzYmVXhfr8ktISnRIR1UltAhmAWLLOdruNTqdzImXrDzjeJK7rrvQzIyKqgloFMt25c+cwHo/Lrkat7OzsYDweL5w6h4ioypYKZPpwn7oeMplMYsN0eY2mvpzv+7nlJl27dg3tdnvu8hapV7LM4XAYW09/v8x29OX0Yb+0/U7OT9v+rHkAMBgM0Gq1UuuTNsyoX+ta5LNIq0/ys0irq6qD7/tbdU2RiIorHMh830ej0YiG45Sjo6NomhAis9E0DCNaTkqJg4MDTCaTzHIBoNVqRQ3fzs4Out3uzPIUy7Jy66WXnWyQDw8PY+vp73u9XmzZWdvR66sPZwZBEDXcnufh6OgoWnY4HMLzvJn7OeszAO7kbPM8r/AQ46o+i3nqyucpEtG8JAC5KCGEBCBt206drr8U9XcQBFPLAJCe52WWa9u2DIJASiml53mx+XnlqXWFELHyXNeNytP/Tkp+Nsmy9PeztqPvv+u6U9vS19e3q6bn7ec8n4G+j6pMIUT0Wep/z6rTMp/FPMc/S9HvKxFtrsI9MtM0IaXEYDCIho0mk0nsV7jM+UXtum5sOSklut1uarlJ3W4XjUYj1nPKKi/Luq6vFd2O53nwfR/D4RCNRiPKeJ23n4t8BoPBALu7uzPrEYZhofqn0T+LvLrOmzGciEgp9AtX/dJXf6tegd7T8DwvtUem/tZ7CGq9tHKlnO5RqDL03kJaeWpd/b3qEejLrqpHlredvP0PgiDWG1K9I9d1Y9vL289Zn0FyHz3Pk67rxrarf6aq/kV7ZLM+i7S6pvUKdUW/r0S0uQon1ux2u7GL8fIPva+9vb3YtZ4sQghYlhW9V8tmlZsmCIKoB5hVnnLmzJnccpPXsvK2m2fWdvTp+nK2bcdu+zdNE41GA+PxOOqNAdmf26x5abrd7tTNHZ7nRWW4rrtU7yjvs1i0rkREWbYisWaz2cRgMIgFhDpsp9PpYGdnB47jrKS8TcDEmkSUVNv/R7bpJpMJxuMxgxgR0QyFhxbp5DSbTYRhCCFE2VUhIqq8rRhapM3BoUUiSuLQIhER1RoDGRER1RoDGRER1RoDGRER1RoDGRER1RoDGRER1VrlAllaTiwiIqIslQtkOpWkMiu5pp70cd51im6LiIiqqdKBTKV0Uc8uTAtcs9Y5yW0REVH5Kh3IiIiIZlk6kBmGEb1834+ucfm+H0vhkVwuqww9tYeaB9x9/qBlWeh0OjPrpP89HA6j8ofDYey9Xpci2yIionItFcgMw0AQBJnZiNXz8AzDiGUCPjg4iIbtkmW4rpu6rdFoBNu2IYTA/v7+QvU8PDyMcpa1Wq3Y+16vt9JtERHRehV++v1wOITruqlpRsIwjJJEDodDAPFeEgAcHR3h9OnTU2VcuHABu7u7RauVSgUj0zRh2zYuXLgQez+ZTE48VxkREZ2ME7lGlswq7LpurEeW1nsjIiIqonAgcxwH/X4/6nEBmLr2lbWcuu6UNm9Wb2ydObqYD4yIqPqW6pGpa07qxomsXlZyuTNnzmTOe+qppzK312630Wq1YjdgWJYVrbvK/0idti0iIqoeJtakWmFiTSJK4v8jIyKiWmMgIyKiWmMgIyKiWmMgIyKiWmMgIyKiWmMgIyKiWmMgIyKiWmMgIyKiWmMgIyKiWmMgIyKiWmMgIyKiWmMgIyKiWmMgIyKiWmMgIyKiWmMgIyKiWmMgIyKiWvu4+kMlLCQiIqoT9siIiKjW/h9JEJYkHqI9FgAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Image('../images/customers-orders.png')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "07b6126f-bf23-411c-b817-a8343e5dda90",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT customerNumber, customerName, orderNumber, status\n",
    "FROM customers\n",
    "LEFT JOIN orders USING (customerNumber)\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5640721f-8ca5-4663-971e-92e4aab70c6d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('customers', engine)\n",
    "    .merge(pd.read_sql_table('orders', engine),\n",
    "           on='customerNumber', how='left')\n",
    "    [['customerNumber', 'customerName', 'orderNumber', 'status']]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6e42be13-6bd9-458d-b36a-16f312671e94",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "cb2abcd8-653a-4fb6-80ef-abbdf9dbf1c1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customerNumber</th>\n",
       "      <th>customerName</th>\n",
       "      <th>orderNumber</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>103</td>\n",
       "      <td>Atelier graphique</td>\n",
       "      <td>10123.0</td>\n",
       "      <td>Shipped</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>103</td>\n",
       "      <td>Atelier graphique</td>\n",
       "      <td>10298.0</td>\n",
       "      <td>Shipped</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>Atelier graphique</td>\n",
       "      <td>10345.0</td>\n",
       "      <td>Shipped</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>112</td>\n",
       "      <td>Signal Gift Stores</td>\n",
       "      <td>10124.0</td>\n",
       "      <td>Shipped</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>112</td>\n",
       "      <td>Signal Gift Stores</td>\n",
       "      <td>10278.0</td>\n",
       "      <td>Shipped</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerNumber        customerName  orderNumber   status\n",
       "0             103   Atelier graphique      10123.0  Shipped\n",
       "1             103   Atelier graphique      10298.0  Shipped\n",
       "2             103   Atelier graphique      10345.0  Shipped\n",
       "3             112  Signal Gift Stores      10124.0  Shipped\n",
       "4             112  Signal Gift Stores      10278.0  Shipped"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b8fc7ed-094a-4198-b966-46a4f261ce88",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "#### 2) Using MySQL LEFT JOIN clause to find unmatched rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "65f5495b-abfa-40bb-a5be-a32d7f1f2d48",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find customers who have no orders\n",
    "string = '''\n",
    "SELECT customerNumber, customerName, orderNumber, status\n",
    "FROM customers\n",
    "LEFT JOIN orders USING (customerNumber)\n",
    "WHERE orderNumber IS NULL\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql_query(string, engine, dtype={'orderNumber': float})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "10efb262-e2ce-4726-a2b4-cf5c2f5eb13f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('customers', engine)\n",
    "    .merge(pd.read_sql_table('orders', engine),\n",
    "           on='customerNumber', how='left')\n",
    "    .query('orderNumber.isna()')\n",
    "    [['customerNumber', 'customerName', 'orderNumber', 'status']]\n",
    "    .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "8ff23ab9-9d93-4a4a-ae0e-e511bd6fdfaf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "9e0261aa-ea7c-40e4-9a8f-1d5cf9066bce",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customerNumber</th>\n",
       "      <th>customerName</th>\n",
       "      <th>orderNumber</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>125</td>\n",
       "      <td>Havel &amp; Zbyszek Co</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>168</td>\n",
       "      <td>American Souvenirs Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>169</td>\n",
       "      <td>Porto Imports Co.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>206</td>\n",
       "      <td>Asian Shopping Network, Co</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>223</td>\n",
       "      <td>Natürlich Autos</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerNumber                customerName  orderNumber  \\\n",
       "0             125          Havel & Zbyszek Co          NaN   \n",
       "1             168      American Souvenirs Inc          NaN   \n",
       "2             169           Porto Imports Co.          NaN   \n",
       "3             206  Asian Shopping Network, Co          NaN   \n",
       "4             223             Natürlich Autos          NaN   \n",
       "\n",
       "  status  \n",
       "0   None  \n",
       "1   None  \n",
       "2   None  \n",
       "3   None  \n",
       "4   None  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e528a0c-580a-48c6-9e39-599488060448",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### 3) Using MySQL LEFT JOIN to join three tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "7cccaa14-895e-41a3-9370-769bd68e54be",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAo0AAAEwCAYAAADSEUt7AAAACXBIWXMAAA7EAAAOxAGVKw4bAAAAB3RJTUUH4wgdARAIi9z3WwAAAAd0RVh0QXV0aG9yAKmuzEgAAAAMdEVYdERlc2NyaXB0aW9uABMJISMAAAAKdEVYdENvcHlyaWdodACsD8w6AAAADnRFWHRDcmVhdGlvbiB0aW1lADX3DwkAAAAJdEVYdFNvZnR3YXJlAF1w/zoAAAALdEVYdERpc2NsYWltZXIAt8C0jwAAAAh0RVh0V2FybmluZwDAG+aHAAAAB3RFWHRTb3VyY2UA9f+D6wAAAAh0RVh0Q29tbWVudAD2zJa/AAAABnRFWHRUaXRsZQCo7tInAAAgAElEQVR4nO3dT4zc5nk/8C9/8kHxRYoiQ2sFECCIYwTbwQZBZEHLgY1WhtNy9tA9JFsnPqxsC5wiRTOLBhu7gQHnsLHlCgU4BxXZbSFn7cYx1pcF4iVtIHIKVMORvTrIyFQWRCqHhSMV3Ug+VHUAQd73d1BIkxxyOP+HM/x+gIVmhuTLl9T7cJ55+ZKUAAgQdUgINp92SJI07CoQ9Q2PB/3B4wZ1o5dx+f96VhIRERERja0H3Bf8hUjt4C/f7jDeaJzweDAYPG5QO/oRl+xpJCIiIqJETBqJiIiIKBGTRiIiIiJKxKSRiIiIiBIxaSQiIiKiREwaiYiIiCgRk0YiIiIiSsSkkYiIiFKvUCigUCgMuxqZxqRxiCqVCm+KS9Qmxg0R0XAwaSQiIiKiRKlPGguFAkqlkte74P75maYZmOafLklSZHd2qVTy5pMkCaVSyfvMfe84TqBMx3ECZYTr5J/udqPH1btQKGBhYSGyjnHbQjRKwnFZqVQAfBFvflGnnaLioFnchGMtvI5+xLlbljuPf52MYxpnzeKik1hr5bu+lXqEYzDp+z9pW1qZnrVYFwBEWimKIgAIXde9zwAIRVG895qmBZYB4H2m67oAIGzbbpjHLdPdB4ZheOWF94uiKIF1uuWG37vrCdfbtu1AvaLKCNfdnSeN0t5u0ior+81t225MuZ8J0djGhWiMr2ZxEBU3bsz64zxcRj/i3C2j2TEoXP9xlJV2PSxp279JcdFJrLXyXR9eJinuW/n+T9qWVrY1rbHej3YzEkmjv5EIEd8QwtNdSclauGG6CV7UF5W/ofinh5eJqrfbwOPqEVWXtEp7u0mrrOy3cHyEp7WSNMbFQThuouLVP58/Znsd51FfWkn1H0dZadfDkrb9mxQXncRaK9/1/nnaifuk7/+kbWlnW9OkH+0m9aenASCfzwfeP/LIIwAA27YBNJ4Gc09fuTRNw8rKivd+bW0Nuq43XUczpmkCABYWFrx15nK51jeoCcMwYFlWbDc4Udq58eHGaSfaiQP3OBBeX/g4AfQvzmVZ7rj+RKOk1bhoJ9bilomKYVercd/s+z9pW1rZ1qzF+kgkjc04joNisQhd1yGEgBCiISFcXFwEcH9cgmmasCwLMzMzXa/bMAxvne5fuVzuqkxVVSGEgGEYAIBcLjf2jZAoLE1x0Emcp6n+RP3Qj++/fmjl+z9pW5pNz1qsj0TSWK/XA++vXbsG4P5/jvtrwt8Irly5EphflmVomoa1tTWsr69D07SGnoF2uL803Hr0g6qqXiOM+pVFlFZJ8aEoSsNnlmVFzttKHMStz3+c6EQv4pxxTOOmn99/zb7rW61HeJlm3/9J29LOtmYl1kciabQsy7vy0nEcLCwsNCR+GxsbAO53J/u7ol2Li4uwLAsrKyveL49OuY1wYWGh4UrKTrhd4I7jeNvp16tT30SDEBUf/radz+exsrLiTQu3+VbjwI2buHiMOk50ux1Acpwzjmmc9fr7z6+V7/qkekQtE/f9n7QtSdOzGuupGmAbpiiK0DTNu7IKEYPo/VdmKYoSeYGJv6ywcJmtDNoNrzdcRisXwrjzufV21+v/i7vYZ9jS3m7SKkv7LRyXfv42rut65AD3ZnHgjxuXG6P+csPr7HWcR8X0KMVxr2SpXQ9DGvdvs7joJNZa+a5vdrFMXNz7l436/k/almbT0x7r/Wg30p8Kxf3y06dQKCCfz2N5ebmrchzHQS6Xg2EYUFW1R7XLLvdeVGltN2nF/UbjiO26v7Kwf3v1XR8lq9///Wg3I3F6uhtul/L8/Dw0TctUgyEiIsoqfv/33tgnjfPz81623Y9fMERERJQ+/P7vvdSfnqZ0ysLpkn7gfqNxxHbdX9y/1AmeniYiIiKioWDSSERERESJmDQSERERUSImjURERESUiEkjERERESV6wH3hXmVDRP3HeCOidvG4QcPGnkYiIiIiSuT1NPL+T9QO9xfvhx9+iGPHjg25NsP1k5/8BD/5yU9a/hxgvNF4YQ9Y77zwwgs4ffp05DQeN6gd/YhL3tybOuI2xsOHD+MrX/kKvv/97+Opp57Cl770pYZ533//ffz0pz/Ff/3XfwEA/uzP/gwvvvgi/uIv/mKgde4XSZIi4yfqc96kl8YR23Xrko6HPG5Qr/Dm3pQ6juPgpZdewttvv41Dhw5hcXER169f96afPXsWzzzzDE6dOoUrV67gypUrOHXqFE6ePImzZ88OseZERIPF4yGNOvY0pkChUMDq6ipkWR52VVoW9Qvm+vXr+NnPfoaf//znePTRR/Hd734XP/zhD3Hp0iUcOnQosPzW1haOHj2Kjz76CA8//PBA695r7GkcLaMYb2nHdp3s5s2b+PrXv554PDx48OBIHzfijofjKO3HkqH2NBYKhZ6tNO0KhULkWADHcTK1H9p15MgRnDlzBltbW/jOd76D06dP4/PPP8dbb72FP/zhD4F5Dx06hCeffBLvvvvukGqbbllqZ4w3ygLTNPGtb32rIWEEenc8zHq88FjSfy0njXNzc/2sB4D7/+GO4/R9Pa3QNA2lUmnY1Ug9SZIa/h588EE8++yzuHLlCm7fvo3nn38eDz30UMN8b775Jp599llIkoR9+/ZFlpWWv2b1i9sPUZ+3ivFGNNo+++yzwHHvueeewy9+8YvY44h7PATSfdxIOx5L+isxaTRNE5IkYWFhAZIkoVKpDKJeQzc7O4t6vQ7TNIddlVQTQjT8ffbZZzh37hwmJyexb98+vPrqq9je3m6Y73vf+x5ee+01CCFw+/btyLLS8tesfnH7IerzJIw3xhuNhw8++ACyLHuxf+7cOTz99NOxxxH3eAjwuNENHkv6KzFpVFUVhmFA0zTouo5yuRw5n/9XUaVSiewO9vdsuN3IkiR581qWhVwuF/iV0OwXlyRJXrC4r/3vw4ETriPwRbd1pVJpKH91dRXFYjFye5O2r926hesXbvDt1n0Yrl+/jsXFRRw6dAhvv/02fvzjH0OSJHz729/G/v37G+Z97733oKrqkGqbTow3xhuNB8uyoCiK915VVbz77rv43e9+1zBvt8fDTo8b/s9bjR837prFT9w6/XHjHovC8ybVNWkawGNJP7V0erpYLGJ5eRlra2uR0yVJgmEY3q+iuAbrqlQqyOfzgV9R1WoViqLAtm0sLy9HlmsYRsMOXl9fhxACtm2jWCwG3i8sLATq6P/ltra21tBgw7/oZFmGrusdd3W3WjcAyOVyXt3c+bup+6Ds7OzgnXfeQbFYxPHjxwEAFy9ehGEYePrpp/Hyyy/jsccewxtvvIHbt2/j9u3beP311/H444/jlVdewYEDB4ZS7zRjvDHeaPRZloXp6Wnv/cTERF+Ph60cN5q1617FT3idcccUXdexsbHhzWuaJnRdT6xrK/HJY0l/CQCiU4ZhCE3TGj63bVsoihL4TFEUYdu2sG1bAIid3qxc/zzhevun+d8bhiHc7fT/6bru1SVcjmEYkfV269xs+9qpW9Q0IYTQNE0YhtF23QfFrcfhw4fF0aNHxblz58Rnn30WOa9lWeLEiRNiYmJCTExMiBMnTgjLsgZc4/6J+z+I+pzxxngbR92263Hz+eefiy9/+cvi97//fcO0pONhv44bce06ap2dxo+/rHaPKa0cP5K2g8eSoH7E5UDv02hZFgB44zxWV1cTu7Z7RdO0hnEibg+N/xRClNXVVczPzyeuw92+XqjX697rbureb2+99RY2NzfxzDPPRN7YGwCmp6dx/vx53Lx5Ezdv3sT58+cDv8BH3UsvvdTW54PCeGvdqMQbjYarV69iz549OHjwYMO0pONh3NNgutWsXXfLHz/t0HUdlUoFpmkin897t65pVtd2toPHkvs+/PDDnpbXdSYKIJDd+3+9+Hsy3PfudHfeuF8J4XINwwj8QgjXu9kvgnBZ7i+guF8d/nndemqa1rD+qO1rt26KogR+kbllxe2HZnUflF60myxivDHexhGPB0H/+q//Kp5++umeldeP44Y/BnodP3HrDB9T3JjSNC2wvqS6xk3jsSTIbTetnBE8f/68OHHihDhw4IA4cOCAOHHihHj//fcb5vOePd0N27aRy+W+yEL/dI5e13Xvc03TvMy6XC4Hxkq588/NzaFYLELTNCwvL0MIEZhPURRUq9We1NEdO9GqcrncMHg2bvs6MTk5GblPgO7rTuOF8cZ4o3QLj2dMg16262bx45d0TJFlGfl8HvV6PXCD7GZ1bXc7eCy5f+GNYRj4l3/5F/zoRz/CyZMn8bd/+7c4cuQIgPtPKvqnf/onnD59Gn/5l38JAHjvvfdw8uRJ/OhHP8Lf/d3feWXxiTDUETdI2G7aw/1G44jtOuhrX/safvnLX+Ib3/hGT8pL0/7t9VNQSqUSZmdneSeNPohqN90+uY3PniYiIuqR7e1t3LhxA1NTU8OuSuo5joN6vc6EcYC6fXIbexqpI2n65TtKuN9oHLFdf+FXv/oVKpUKfv3rX/eszDTt3171NLr3irVtO7XPbh51vb4H5Je//GX2NBIREfVKrVYb6+ccV6vVniR51WoVQggmjAMgQldwC9HZk9s+/fRTJo1ERES9ksaLYIhc3T65rSdXTxMREWXd3bt3cenSJe/pWERpsLOz4109vbm5iZMnT+LixYve1dP/93//h8ceewynT5/GzMwMAOCdd97BP/7jPzY8qYhjGqkjaRpjM0q432gcsV3f9+GHH+K5557Db3/7256Wy/1LnXDbzeHDh/GVr3wF3//+9/HUU09FPoijVqvhxRdfxJUrVwDcv5XQ0tJSoNdckiQmjdQZHsQ6w/1G44jt+j5d1/Hxxx97z3PvFe5f6oTbbj744AMcO3asJ+VxTCMREVEP1Go1PmqSUqcXCaOLSSMREVEPXLhwgUkjjbW+Jo29vkdQr8vrVKFQgOM4w64GERGlxNbWFu7du5e6W8ik5XuTxkPLSWOa7ztVKBQiA8NxnFTXmyjLGLc0TqrVKqanp8c6SWPMUstJ49zcXD/r0TVN01AqlYZdDaLUS1NPOeOWxkWtVhu7+zNGHSsYs9mWmDSapglJkrCwsABJklCpVAZRr7bNzs6iXq/DNM1hV4WIWsS4pXFhWVYmetsYs9mWmDSqqgrDMKBpGnRdR7lcjpxPkiTvz59YuklnVMIZt0zSNOD+L6BwgK6urqJYLEbWL6r73P8rSpKkQF1N02xa93AdwwEUVX+3DpVKZaxPYVD/hNtVUrt2TydJkuTNa1kWcrlcoLfAX264bbYbG+22/WZx6y4bF2u9qBtRt+7cuYOPP/4YR48eHXZVmmoWS60eKwDGbJa1dHq6WCxieXkZa2trkdMlSYJhGN6zCv2J5fr6OoQQsG0bCwsLgSTN/3zDtbW1wLS48oD7jXtubg7VajXwuSzL0HW9465zf12LxWJD3f1yuZxXP3d+//6I2zYX77dF7UqKi7BKpYJ8Pu/ND9wfd6UoCmzb9u4lFy7XMIyGxK7V2Oik7SfF7cbGRmys9bpuRJ3Y3NzE1NQUdu/ePeyqNBUXS60eK1yM2WwTAESnDMMQmqZFTguXqyiKsG1bGIYh3PX6/3RdTyxPURSh63pDuYZhNKzHtm2hKIoQQgReh+drVteo9+FpQgihaZowDKPpttm23dW+TpNu201Wdbrf4uKiWbt221uzdh9Xbiex0W7bbyVu3W0Ml+nqRd2oe1k/HiwtLYl/+Id/6Fv53exf/3JxsdTKscJ9z5gdHb2OSwBiaPdp1DQt8AtCtNBz4nIfcxNndXUV8/PzieVYltXS+lpRr9e91822jffwon5z27UsyxBCYHV1NXIIRT900/aj4tZxnECvvuiih76bYw5RM5Zlpf4imGax1OmxgjGbPV0njaqqYmVlJdDIksYdRC3jdnMnlVetVjE5Odl0wLEsy5ibm8OZM2cCn1mW5XVtd/sF6i/bNE1YlgVVVZtuG1Gn4uKiWbt248Y9lXTt2jVvmm3bseW6r9u931y3bT8qbm3bhqZpDdvULsYl9cvOzs5IPAmmWSy1cqyIwpjNngd6UYht28jlct77Vn5ZhJfRdb3l8txfGu5YrCjlcrkhsdR13StX07SugnxycjIw7stfx2bbRtSpuLiIa9flcjmyjc7NzaFYLELTNCwvL0MIEZhPUZSG8cKd1rHdth+OW1VVsbS05NWvm1hiXFI/XL16FXv27MHBgweHXZWmmsVSq8eKKIzZbJFw/5w3L8ygtrgHBLab9nC/0TjKcrv+t3/7N/zHf/wH/v3f/71v68jy/qXO9brdSJLEZ08TERF1ahTGMxL1CpNGIiKiDlmWlfrxjES9wtPT1BGeLukM9xuNo6y26+3tbRw5cgSffvopdu3a1bf1ZHX/Und4epqIiCglLl68iGPHjvU1YSRKEyaNREREHajVapl43jSRi0kjERFRB3gRDGUNxzRSRzjGpjPcbzSOstiu7969i3379uGTTz7B3r17+7quLO5f6h7HNBIREaXA5cuXcfjw4b4njERpwqSRiIioTbzVDmURk0YiIqI2jcLzpl2FQgGSJAWe40zUCSaN+CKg/H8MLqLOFAoFOI7Tl3L9z8d1OY7DK1hp4C5cuDASSaNpmsjn8xBCIJfLQZKkruKTcZhtmUwao77UbNuGEML7KxaLqFQqHZdHNAp60Xb7mSSGy9U0DaVSqefrImrH1tYW7t27B1mWh12VRNeuXcPk5CQAQJZlCCHaqjfjkPwymTS2QgiBhYUFJoNEKTI7O4t6vc4zATRU1WoV09PTkT1uWcA4zK6ukkb3F0jcad24aVHd427wufP5l/G/D/f++cv3TwuX404rFAqwLAu5XC7xl5KmabBtu+m2xJUXVy+ibjRr7/6/8DKtxkKzeI5afzvx1Kxs/xAR9zRXXLmrq6soFosdradXxxfKtlqtNhL3ZyyVSlhYWMDCwkLgOxb44nRypVLxPmMcUisEANEJRVECy9q2HXiv63rkNF3XA9MMw/DeAxCapgWWCb93heutKIqwbTu2HHeaf76o9y5N04RhGE23JWr5ZvUaF920myzrZr8B8Npjs88Nw2iIk1ZjoVk7j1t/q/HU7Hjg1s+dFleuPx7926QoSsvb0IvjCwVl7XjwzW9+U/znf/7nwNbXzf4Nf9+65bhx405jHI6fXscl3Pt6d5M0hv/z/ImW+5/v//OvPKqcpAbivne/GMN//uQzrpxOksZm2+JfPqle4yJrXxK90ul+MwwjcEBP+rzVmAq3/bh2HreeqDLi4imubPdz/xdOXLn+pNWdHv6yavW406zuWYnjXsnS8eB///d/xYMPPij++Mc/Dmyd/Uoaozp9GIfjox9JY8/HNNbrdQD3u6ZzuVzg4hI/XddRqVS8K7s6GVCsaVqgfCEEyuVyT7bDcRysrKxAVdXEbRlkvYj6pd123quy3cH5q6urbd25YHV1FfPz833bBsYxRdnc3MTU1BR279497Kp0zX/1N+OQWtF10njmzBnvtWmasCwLqqrCtm1omuZNC49DmJmZwdraGtbX17G4uNj2elVVxcrKSqBht3M1lztWMYrb4A3D8OZtti3+8rqtF1GUqHZVqVQiP3dft/pDzG27zdp53PrDZTRbR1zZ7mtZlqHrOq5du9ZSubIsY25uLnAMaiVWW8E4pjjjelNvxiG1ouukcXJy0hugWiwWvV8UqqqiXq9HDswH7je0fD6Per3e8W0LbNtGsVj01uHeViDJ3NwcisVioPG596+SJMn7haSqakvbEi6v03oRNRNuV+6vbfGnW0S5ny8tLaFarbZUpr/tJrXzuPUnxVOhUGhadrlc9j5fWFhoWm5YuVz2zm4AybHaDsYxRbEsayQugmkX45BaIcEd2NhB93GhUMDq6mrHSV+pVMLs7KyXnNHo6PWD0LOC+43GUVba9c7ODvbv3496vY6DBw8ObL1Z2b/UW71uN5IkDe8+jY7joF6vM2EkIqKRcPXqVezZs2egCSNRmjwwjJW6935KGgdFRESUFpZl8VF5lGldJY2tjpvq1XJERETDMq7jGYlaxccIEhERtWBcr5wmalVXF8JQdnFgdme432gcZaFdb29v48iRI/j000+xa9euga47C/uXem+sLoQhIiIaFRcvXsSxY8cGnjASpQmTRiIiogS1Wo0XwVDmMWkkIiJKwItgiDimkTrEMTad4X6jcTTu7fru3bvYt28fPvnkE+zdu3fg6x/3/Uv9wTGNREREA3b58mUcPnx4KAkjUZowaSSinioUCt5zYiVJQqlUguM4kCQJjuN0XW6Y4zgca0Z9xVvt9EYvjgM0XGOdNBYKBTZOoiZ6ESNRZdi2DSEEhBBYXl6GLMsQQrT1nPqocjVNQ6lU6qq+RO2q1WqZShr79d3ZyXGA0mWsk0YiGi+zs7Oo1+swTXPYVaEMuXDhQqaSRqI4XSWNblez++c/kLvv/dP87yuVSqAsfzn+U1BRp578v4LC63HLdZ9vncvl2DNBY8MfI/4Yiosfd1qrMdIspqPW306cufVyY7pSqXif+U9pu9Pjyl1dXUWxWIxdT7+OS+FplA1bW1u4d+/eSPeORbXjSqUSiC33fVzsNTv2xMVQOK7d+ePq1uqxi4ZLABCd0HXde23bdqAcAELTtMC08Hv/vIZheO8Nw/Cm27YtFEUJrFdRFGHbdux63Gn++ai3umk3WdbNfgvHSdzn/vhxp7caI0kxHbX+cBmKonjbCcArMxzT7ue6rnv1c6fHleuu379M+BjRy+NSs+2kL4zz8eDNN98Uf/3Xfz3UOnR73PDzt2NN04RhGA0xFG7rzcqIO77ExXWr3/3NyqbW9Dou4d5tp9NC3f9E/5+/cL+oLwDbtoVhGIGGFZ7eStIYN40H+f4Z5y+Jfup0v8XFSVL8uOuMmxaOkbiYjltPVBlxcedPGv11ct83i3P3vf8LJu4Y0avjUrgMfwJMQeN8PPj7v/97cfr06aHWoZvjRlI7dn/k+fnjIqmMuGXj4jrpmNLqsYuS9SNp7Pj0tOM4yOVy3mB3McD7R1mWNbB1EWXFIGPaPz7MHRy/uroaeUo8zurqKubn5wOf9XIbNE0LlCOEQLlc7rg8Gk2WZY301fm9aMedlNFpXFO6dZw02rYNTdO8952ONVBVFSsrK4EG5b6WZRmyLMOyLG88RLsNz7btjupFlCZRcVKpVBLjpxVujDSL6bj1h8vohFuOLMvQdR3Xrl1rqVxZljE3N4czZ84E5u/XcYljo7Pnzp07+Pjjj3H06NFhV6UjSe24VCrhxRdfhGEYDe3bjb1OY6FZXMeV2+6xiwav46RRVVXU6/XIwavtEkKgWCx6ZS0tLaFarXrTdV1HLpeDJElYX19v+Sq2ubk5FItFHuxpLNi2HYgT95d+Uvw044+RpJiOW3+3cVYul70yFxYW2iq3XC6jXq9773t5XApv7+TkZFfl0ejZ3NzE1NQUdu/ePeyqdCyuHbtxpaoqVFUNfBaOvU5iIS6u/bo5dtFw8DGC1BE+1qoz3G80jsa1Xf/0pz/F7du38c///M9Drce47l/qLz5GkIiIaEAsy8L09PSwq0GUGuxppI7wl29nuN9oHI1ju97Z2cH+/ftRr9dx8ODBodZlHPcv9R97GomIiAbg6tWr2LNnz9ATRqI0YdJIREQUMuq32iHqByaNREREIRzPSNSISSMREVGIZVkt396NKCt4IQx1hAOzO8P9RuNo3Nr19vY2jhw5gk8//RS7du0adnXGbv/SYPBCGCIioj67ePEijh07loqEkShNmDQSERH51Go1XgRDFIFJI9GY6PaxeUR0Hy+C6RyPQ+ONSSMREdGf3L17F5cuXcLx48eHXZWR4DgOJEmC4zjDrgoNwAPDrgAREVFaXL58GYcPH8bevXuHXZWRIMsyL9DJEPY0EhER/QlvtUMUj0kj0YgoFAreqSD3zzTNwDymaXrTKpVKYJp/ufC4I7esVpYNTyMaJ7VabeySxrj4Dce9aZqxx4Fmxx6OY8wOJo1EIySXy0EIASEEbNtGsVgMTF9fX/emLSwseOOMJEmCYRjesoZhNBzomy3rLieEwNraGscv0di6cOHCWCWNSfHrj/tisdhwHHBtbGw0PfZQNjBpJBohtm17r2VZhqZpgV/8y8vL3jT3i880TWiaBlVVvflUVYWiKIEvj7hlgWBPhWVZ2NjY6NMWEg3P1tYW7t27B1mWh12VnmglfsNxv7i4GHjvHiNmZma8MnK53IC3hNLCuxCG3ctEo6der2N2drav69A0zftiIRpn1WoV09PTY/V92Iv4dRzHO8vhGqd9RK1jTyPRCDlz5oz32jRNWJYV6EGMoqoqVlZWAj2S7uukHpWoZUulUidVJ0q9Wq02Vvdn7FX82rYNTdO89xzXnF0P8FJ5otExOTkZ+IXfavwKIQLLKYqCarXa0rK2bQdOR+m63mJtiUaLZVlj1757Eb+qqmJpack7hozbPqLWSYJZI9HAdPMA+UKhgNXV1bEZb0Xjo5t2nRZ37tzBgQMHcOvWLezevXvY1QkYh/1Lg9frdiNJEk9PExERbW5uYmpqKnUJI1GaMGkkIqLM4029iZIxaSQaEdVqlaemifrEsqyxugiGqB84ppFogDg2icbRqLfrnZ0d7N+/H/V6HQcPHhx2dRqM+v6l4eCYRiIioh67evUq9uzZk8qEkShNmDQSEVGmWZaFQqEw7GoQpR6TRiIiyjSOZyRqDZNGIiLKNF45TdQaXghDNEAc0E7jaJTb9fb2No4cOYJPP/0Uu3btGnZ1Io3y/qXh4YUwRNQSx3GajtFKmk6UFRcvXsSxY8dSmzAOQqFQgCRJ3jOqw+/ThMeu4WLSSER9USgU4DhO4DPHcSBJUsPnnTBNs2dlUXbVarVMJyGmaSKfz0MIAVVVA+9zuVzPYkySJO+vVCr1oOY0DEwaiZhXXsgAAByASURBVGhgZFmGEKLrm5RLkoT19XWOQ6OuZf0imGvXrmFycjLyfS/i1f1xZ9s2hBAQQmB2djaVvZiU7IFhV4CIqF3uGJ0s9xBR9+7evYtLly7h+PHjw67K2FpaWoJt24HEU1XVIdaIusGeRqIR4Z7adf/Cv9T903K5XMPycdPdMUKVSsUbOB2ev1KpeJ+74538p62iPosTXofbExFeT7M6EPXC5cuXcfjwYezdu3fYVekrfxz5469UKmFhYQELCwveaWP/e3fZuLL8MRn1uXuMSuqpjKtf1PSkYxuPE/3FpJFoRGxsbHind2zbRrFY9KZJkgTDMLzpmqYFlk2a7nJ78CRJ8uYVQmBtbQ2O46BSqXjjndx5oz5rx/r6urdNCwsLXtIZVweiXsnCrXbCsW8YhpeYLS8vQ9d16LoOIUTD+6SyyuWy93lcrObz+Y7rFzU96tjG48TgMGkkGhEzMzORv7ZN04SmaYFTPouLiy1PB+5/ebpfAG7vgP/Xu2VZ2NjYwMzMDFZWVrzTwrIsR37WjuXlZW859wu8WR2IeqVWq4110hgV+6qqQlGUthOrqLLcz4HOYjWpfq0c2zpdN3WGYxqJRoDjOMjlcoFf/1GncToV/uLUNM1L5sKEEN6pcsMwoKpq5GfdalYHol64cOEClpaWhl2NkdcsVovFIhYXF7u++K2TdVPvsaeRaATYth04LeMft6OqKlZWVgJjHOfn51ueHhY1v3uLDHe9sixD13Vcu3Yt8rNuNasDUS9sbW3h3r17fUtm0iAqjlodZ9hKWZVKJTFWdV1vGIdYqVRgmmZi/To5tvE40V/saSQaAaqqYmlpyetd1HU9MN227cCB2TCMQA9K0vSw8Pzu+srlcqCH0z8GMvwZgEAZiqKgWq0mbGlyHYD7F95YlhVYR696OCkbqtUqpqene9pjn0ZCiMA2thuHfuGYdGO9WayWy2U88sgjgTrouu7FalL92j22hY+N1Ft8jCDRAPFxYDSORrFd/+AHP8BXv/pVPP/888OuSqJR3L80fHyMIBERUQ9YlsX7fBK1iT2NRAPEHgMaR6PWru/cuYMDBw7g1q1b2L1797Crk2jU9i+lA3saiYiIurS5uYmpqamRSBiJ0oRJIxERZUoWbupN1A9MGomIKFMsy8L09PSwq0E0cjimkWiAODaJxtEoteudnR3s378f9XodBw8eHHZ1WjJK+5fSg2MaiYiIunD16lXs2bNnZBJGon544YUXOlqOSSMREWUGb7VDBLz66qve6/fffx9PPPEEJiYmMDExgSeeeAK/+c1vIpdj0khERJnB8YxEXzh79iyeeeYZnDp1CleuXMGVK1dw6tQpnDx5EmfPnm2Yn2MaiQZoUGOTHMfB/Px87OPCkqYTtWOUxtx97Wtfwy9/+Ut84xvfGHZVWjZK+5fSo1m7kSQJN27cwNe//nVcunQJhw4dCkzf2trC0aNH8dFHH+Hhhx/2lmFPIxH1RaFQgOM4gc8cx4EkSQ2fd0KSJO/PNM2uy6Pxt729jRs3bmBqamrYVSEApVIpEMfuX6lUGnbVMsE0TXzrW99qSBgB4NChQ3jyySfx7rvvBj5n0khEAyPLMoQQkGW5q3IKhQKEEBBCwLZtFIvFniSiNN4uXryIY8eOYdeuXcOuSiZE/XD0W15e9uIYgPd6eXl5UFXMhKjEHACee+45/OIXv4icLkkS3nzzTTz77LPe+4ceeohJIxGNHv9pdVmWeaNmakmtVuNFMJQ5bjLu/wOAc+fO4emnn46cLoTA9773Pbz22mve+//5n/9h0kg0KtxTu3GnZP3Tcrlcw/Jx0x3HQaFQQKVS8X6BhuevVCre54VCwfvc7UWI+ixOeB2maUaup1kdwizL6rr3ksZf1i6C6SS22znO+MsMr6tQKMCyLORyOe90czvHiaT1UfdUVcW7776L3/3udw3Trl+/jvfeew+qqgY+Z9JINCI2NjYaTsm6JEmCYRjedE3TAssmTXe5v0AlSQr84lxbW4PjOKhUKsjn84Ffq1GftWN9fd3bpoWFBe/LJK4OYaVSCYZhtL1eypa7d+/i0qVLOH78+LCrMhRJse3K5XItH2cMw4hM5IQQqFarUBQFtm1jeXm5o+NEq+ujzkxMTODll1/GY489hjfeeAO3b9/G7du38frrr+Pxxx/HK6+8ggMHDgQXEkQ0MABEp2Fn27a3vL8cwzCEpmkN8yqK0tJ0t1yXYRgN6wEgdF335nWX9S/v/0wIIRRFEbZtR+6DqNf+ZZrVITx/+DMavG7a9aB88MEHIp/PD7saHel0/7YT20JEx6ymacIwjMjjiH+Z8LrC5cUdJ/zb6Je0PkrWrN34P7csS5w4cUJMTEyIiYkJceLECWFZVuRyD/Q8dSWinnMcx+sBcPXyF3d4TKCmabGD0YUQ3ikswzCgqmrkZ91qVgfg/qmuF198sSfrovFnWVYmx762E9tR6vU6ZmdnO1qXn3sRXK+PE9SZ06dPe6+np6dx/vz5lpbj6WmiEWDbduCUsn8ckqqqWFlZCYw9mp+fb3l6WNT87pgkd72yLEPXdVy7di3ys241qwPAhJHaV6vVMpk0+iXFFQCcOXPGe22aJizLgqqqkcu6r5uNJ7ZtG0D0saPdurayPmrN888/39Fy7GkkGgGqqmJpacnrXdR1PTDdtu3AxS2GYWBpaanl6WHh+d31lcvlQA+n8I2TCn8GIFCGoiht3Uw8rg7uF5l/rJU7P79MKM6FCxeatvmsiIsr1+TkZGw8CyEC05Jiem5uDsVi0evdjCs3Trvro/7jE2GIBohPdqBxlPZ2vbW1hUcffRT//d//PZIXUgxq/xYKBayurvLH15joR7vh6WkiIhpr1WoV09PTI5kwEqUJk0YiIhprtVotU/dnJOoXJo1ERDTWLMvik2BaUK1WeWqamuKYRqIBSvvYL6JOpLld37lzBwcOHMCtW7ewe/fuYVenI2nev5ReHNNIRETUhs3NTUxNTY1swkiUJkwaiYhobGX1pt5E/cCkkYiIxpZlWbwIhqhHOKaRaIA4NonGUVrb9c7ODvbv3496vY6DBw8OuzodS+v+pXTjmEYiIqIWXb16FXv27BnphJEoTZg0EhHRWOKtdtpTKBTgOE7Py+VN1ccHk0aiEeY4DiRJ6suBnmjUZX08Yy+PD6VSCZIkNfyVSiUehzKESSPRCJNlGUII74a8/eopIBpFWb9yOnx86Mby8jKEEN74OPf18vIyj0MZwqSRiIjGzvb2Nm7cuIGpqalhV4VobDBpJBoh/tNClUrF+wy4/+vesizkcjnvVFL41z7HFlFWXLx4EceOHcOuXbuGXZWhCsd8+PRy3DLun2maba0nfBxKKt89jtFoYNJINCIkSYJhGN5poXK5HJherVahKAps28by8jJ0XcfGxoY33TRN6Lo+6GoTDUWtVuNFMCHhY4hhGA2JYy6X86bbto1isdjWOsLHoag6uOULIbC2tsZT2SOESSPRCDBNE5qmQVXVlpcpl8tYWFjw3i8tLWFmZqYf1SNKnaxfBBMWdQxRVRWKogSSNtu2vdeyLEPTtJZ7G1upAxDsabQsK/DjltKNSSPRGNN1HZVKBaZpIp/P92RAPFHa3b17F5cuXcLx48eHXZWRV6/Xe1qepmmBnsaosyaUXkwaiUaAqqpYWVkJ/OKPGwvk7ymYmZnB2toa1tfXsbi42Pd6EqXB5cuXcfjwYezdu3fYVUmNqGOI+9r/Y/LMmTOB6ZZltXWGw+U/DjWrQ9y4R0qnB4ZdASJqjW3byOVy3vuoR0PNzc2hWCxC0zTvVhj5fB71ep29jJQZWb/VThwhRGAMo6IoqFargXkmJycD83TyCLrwccgvfBzjOOvRwmdPEw3QMJ4hWyqVMDs721FvAVEr0vZs5L/5m79BsVjE/Pz8sKvSE93sX/fCE8oePnuaiNriOA7q9ToTRsqUCxcusKcRX1z8QtQrPD1NNKbc+6VFjS0iGldbW1u4d+9epodjmKbp3SqHvYzUS0waicZUeKwSURZUq1VMT09n+kb2qqoyWaS+4OlpIiIaG7VajfdnJOoTJo1ERDQ2LMvik2CI+oRXTxMNUNquMiXqhbS06zt37uDAgQO4desWdu/ePdS69FJa9i+NFl49TUREFGNzcxNTU1NjlTASpQmTRiIiGgu8qTdRfzFpJCKisWBZFi+CIeojjmkkGiCOTaJxlIZ2vbOzg/3796Ner+PgwYNDq0c/pGH/0ujhmEYiIqIIV69exZ49e8YuYSRKEyaNRCOsUCjAcZyez0s0anirHaL+Y9JIREQjj+MZifqPSSPRiCgUCpAkCZIkwXEc79nSuVwOpVIJAOA4jjePJEkwTdNbNjwvgMC8lUplKNtF1Au8cpqo/5g0Eo2ASqWCfD4PIYQ3qLlarUJRFNi2jeXlZQDAxsaGN49t2ygWi7HzSpLkzSuEwNraGk9f00ja3t7GjRs3MDU1NeyqEI01Jo1EI2BmZgYrKyvemC1ZlmPnc3sOc7lcbHluD6S/p9GyLGxsbPS+8kR9dvHiRRw7dgy7du0adlWIxtoDw64AESWTZRlCCO/0s2EYUFU1MI/jOMjlcoHbK7i3XIiiaZrX60g0ymq1WiYugmkWz0SDwJ5GohHgjjeUZRm6ruPatWveNNu2vX81TWtYxs+dV1VVrKyseD2OAAJjHYlGCS+CIRoM9jQSjYByuRzoZXB7E+fm5lAsFr1ew6WlJW8+XdcDZYTntW07cAo7PD/RKLh79y4uXbqE48ePD7sqfcObelNa8IkwRAPEJzvQOBpmu/7www/x3HPP4be//e3A102UNTw9TUREI4u32iEaHCaNREQ0smq1GpNGogFh0khERCPrwoULTBqJBoRJIxERjaStrS3cu3cv9r6lRNRbTBqJiGgkVatVTE9P8/6FRAPCpJGIiEZSrVbj/RmJBohJIxERjSTLsjLxJBiitOB9GokGiPdppHE0jHZ9584dHDhwALdu3cLu3bsHtl6iLGNPIxERjZzNzU1MTU0xYSQaICaNRNSSfl5s4DgOTzNSW3hTb6LBY9JIRB2TJMn7K5VKw64OZYhlWbwIhmjAmDQSZVihUIDjOG0vZ5omJEmCbdsQQkAIgdnZWZim2YdaEgXt7OzwSTBEQ/DAsCtARKNnaWkJtm0HbqqsquoQa0RZcvXqVezZswcHDx4cdlWIMoU9jUQjwu0V9J8SDvfs+aeFxyAWCgXvc3cMoWVZyOVy3qnlpPIBeJ8lPYWjWV3C03O5XNPplUql+c6hTOGtdoiGg0kj0QjJ5XLe6WDbtlEsFr1pkiTBMAxvumEYXrJWqVSQz+e9acD9p2koigLbtrG8vAwA2NjYiC3fL5/PN61ns7pETdc0rWF5d5oQAmtrax2dRqfxxPGMRMPBpJFohNi27b2WZRmapsE0TZimCU3TAqeIVVWFoihwHAczMzNYWVnxemfieglnZmaa9v61IqkuUdMXFxcDywPBnkbLsrCxsdFRfWj88MppouFg0kg0wur1ekvzybIMIQRWV1djTzs7jhPoyYy7UbOqqlhZWelrz5+maYF6CCFQLpf7tj4aHdvb27hx4wampqaGXRWizGHSSDRCzpw54702TROWZUFVVS+R8yeD/rGH7phAWZah6zquXbvmzef2Xtq2HThN3Gwcoa7rDT2RlUoFpmkm1iVq+vz8vPc6ajpv50Ouixcv4tixY9i1a9ewq0KUObx6mmiETE5OBsYG+nsDhRCBaYqioFqtAgDK5XLkcnNzcygWi9A0DcvLy1haWvLm03U9th7lchmPPPJIoExd171Tzs3qAtxPUP1Jp2EYWFpaip3erC6ULbVajRfBEA0Jnz1NNEDdPKO3UChgdXU18aplokEb5LOn//zP/xwvvPAC/uqv/qrv6yKiIJ6eJiKikXD37l1cunQJx48fH3ZViDKJSSMREY2Ey5cv4/Dhw9i7d++wq0KUSRzTSDQi/GMCibKIt9ohGi72NBIR0Ujg86aJhotJIxERjYQLFy4waSQaIiaNRESUeltbW7h37x7vHkA0REwaiYgo9arVKqanpwP3/ySiwWLSSEREqVer1TA9PT3sahBlGpNGIiJKPcuy+CQYoiHjE2GIBmiQT84gGpR+t+s7d+7gwIEDuHXrFnbv3t2XdRBRMvY0EhFRqm1ubmJqaooJI9GQMWkkokiFQgGO4wy7GkS8qTdRSjBpJCKiVLMsixfBEKUAk0aiESJJkvdXqVQiP/ffksRxnIaLB/w9iJIkwTTNhjILhQIsy0Iul0OpVPLKqVQq3rzhXkjeCoX6YWdnh0+CIUoJJo1EI0KSJBiGASEEhBAol8uRnxuG0VYCt76+DiEEbNvGwsICHMdBtVqFoiiwbRvLy8uB+YUQ0HUdGxsb3memaULX9d5sKJHP1atXsWfPHhw8eHDYVSHKvAeGXQEiSmaaJjRNg6qqiZ+rqgpFUVoej+gmhbIsN+3NsSwL1WoVAFAulyFJkpe4Li0tYXV1ta1tImoFb7VDlB5MGokyxrKsjpYLJ5S6rqNSqeCRRx5BPp/n492oLziekSg9eHqaaASoqoqVlRWYpul9VqlUIj93X8uyDFmWYVmW1+von68Vtm3HTpuZmcHa2hrW19exuLjYVrlEreKV00TpwZ5GohFh2zZyuZz33r2RshAiMIZRURTvNDJwv0fQXU7TtJa/gOfm5lAsFqFpWmRSKMsy8vk86vU6exmpL7a3t3Hjxg1MTU0NuypEBD4Rhmigxu2JMKVSCbOzsw1jLSlb+tWuf/WrX6FSqeDXv/51T8slos7w9DQRdcRxHNTrdSaM1De1Wo0XwRClCJNGImpboVBALpfjFdPUV7wIhihdeHqaaIDG7fQ0EdCfdn337l3s27cPn3zyCfbu3duzcomoc+xpJCKi1Ll8+TIOHz7MhJEoRZg0EhFR6vBWO0Tpw6SRiIhSh8+bJkofJo1ERNQTH374Yc/KunDhApNGopRh0khERD3x1FNP4dFHH8Vrr72GP/7xj5HzvP/++3jiiScwMTGBiYkJPPHEE/jNb34TmGdrawv37t3jTeOJUoZJIxER9YTjOHjppZfw9ttv49ChQ1hcXMT169e96WfPnsUzzzyDU6dO4cqVK7hy5QpOnTqFkydP4uzZs9581WoV09PTgScdEdHw8ZY7RAM0yFvuSJKU6Vv7ZGn7C4UCVldXh9YzF9Wur1+/jp/97Gf4+c9/jkcffRTf/e538cMf/hCXLl3CoUOHAstvbW3h6NGj+Oijj/Dwww/jBz/4Ab761a/i+eefH+h2EFFz7GkkogaFQgGSJHl/vXwqR6lU6lvZaeHuvzDHccZye6McOXIEZ86cwdbWFr7zne/g9OnT+Pzzz/HWW2/hD3/4Q2DeQ4cO4cknn8S7774L4P6V01nZT0SjhEkjEUUyDANCCAghMDc3h1Kp1Jeyq9Vqz8pNE03TerrPRoH/x4D79+CDD+LZZ5/FlStXcPv2bTz//PN46KGHGuZ788038eyzz0KSJPz+97/HN7/5zWFvDhGFMGkkokQzMzOo1+vDrsZImZ2dRb1eh2maw67KwLg/BPx/n332Gc6dO4fJyUns27cPr776Kra3txvm+973vofXXnsNQgjcvHkTX/rSl4a9OUQUwqSRaET4Txk7jgPg/ulOf29NswTFP1+lUmlabtiZM2cwNzfXcnnt1CtcpmmageX877tZT7hny/95eLvD06PWnzQNAFZXV1EsFiPrE3Wq2t2mdvdFVH3C+yKqrm4dKpVKzy86uX79OhYXF3Ho0CG8/fbb+PGPfwxJkvDtb38b+/fvb5j3vffeg6qqPa0DEfUWk0aiEVCpVJDP571eGdfGxob3mW3bsQmKe1GI+7e2tgbHcWLLBYBiseglGbOzsyiXy4nluXK5XNN6+csOJz/r6+uB5fzvFxYWAvMmrcdfX/8pccMwvCRJ13VsbGx485qmCV3XE7czaR8AgCzL0HW949PUvdoXrdS1FxcN7ezs4J133kGxWMTx48cBABcvXoRhGHj66afx8ssv47HHHsMbb7yB27dv4/bt23j99dfx+OOP45VXXsGBAwe6rgMR9ZEgooEBIDoJO9u2BQChKErk5/4//7qEEMIwjIZ5AAhd12PLVRRFGIYhhBBC1/XA9Gblucvath0oT9M0rzz/66j9E66Hvyz/+6T1+Ldf07SGdfmX96/X/bzZdrayD/zb6JZp27a3L/2vk+rUzb5o5f+/W26Zhw8fFkePHhXnzp0Tn332WeS8lmWJEydOiImJCTExMSFOnDghLMvqug5E1H/saSQaAbIsQwiB1dVV79Sj4ziB3iXRpKdI07SGMWTlcjmy3LByuYx8Ph/oEYwrL86gxkN2uh5d11GpVGCaJvL5vHfrmmbb2c4+WF1dxfz8fGI9LMvqqP5R/PuiWV17+dSVt956C5ubm3jmmWdixyROT0/j/PnzuHnzJm7evInz589jenq6Z3Ugov5h0kg0AtyEzT3dee3aNdi2DU3TGuYJU1UVKysrgYTQPV0aVW6U5eVlLCwswHGcpuW5zpw54702TROWZfVlvFor64mqr/vaTQ5nZmawtraG9fV1LC4uxi7nbmcr+8BPlmXMzc0F6ivLMizL8k4Td3vBTNy+aLeu3Th27FhfyiWidHhg2BUgomTlcjlwoYLbq7i0tBQYmxfHtm3kcjnvvTtvXLlRDMPwejbjynNNTk42LTc89rDZeptJWo//c/98iqIEbvUjyzLy+Tzq9XrgBtnNtjNpH4SVy+WGC190XffK0DStq16/Zvui3boSEUXhE2GIBmiQT4QZlkE9naTX6ymVSpidneUVvB3IQrsmIp6eJiKC4zio1+tMGImImuDpaSLKtEKhAMuyYNv2sKtCRJRqPD1NNEA8jUfjiO2aKBt4epqIiIiIEjFpJCIiIqJETBqJiIiIKBGTRiIiIiJKxKSRiIiIiBIxaSQiIiKiREwaiTLMcZyGR9sRERFFYdJIRADuJ5CSJMFxnMjphUKhYVrSMp2ui4iI0odJIxEBAGRZhhDCe5ZzVJKYtEw/10VERMPFpJGIiIiIEjFpJBohkiR5f5VKxRuTWKlUvEe5Rc0XV0Yul2uYBnzxPOZcLodSqZRYJ/9r0zS98k3TDLz316WTdRER0fAwaSQaEZIkwTAMCCEghEC5XA5Md5/7K0mSN48QAmtra96p33AZmqZFrqtarUJRFNi2jeXl5bbqub6+DiEEbNtGsVgMvF9YWOjpuoiIaHAeGHYFiCiZaZrQNA2qqjZMsywL1WrVmw8I9v4BwMbGBh555JGGMhYXFzE/P9/TurqJnyzLUBQFi4uLgfeO47Q9BpKIiIaPPY1EI05RlMB7TdMCPY1RvZJERETtYtJINAJUVcXKyorXkwigYaxi3HzuOMGoaUm9jLZtd1v1lg1yXURE1D4mjUQjwh0j6F5UEtd7GJ5vcnIydtqLL74Yu765uTkUi8XAxSm5XM5btpc3BY9aFxERpYsk3NHzRNR37lhDhh2NE7ZromxgTyMRERERJWLSSERERESJmDQSERERUSImjURERESUiEkjERERESVi0khEREREiZg0EhEREVEiJo1ERERElIhJIxERERElYtJIRERERImYNBIRERFRIiaNRERERJSISSMRERERJWLSSERERESJmDQSERERUSImjURERESU6IFhV4AoiyRJGnYViIiI2sKeRiIiIiJK9P8B6PJ1hNIchqgAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Image('../images/payments-customers-employees.png')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "6cc83019-ff75-4357-a89e-9fd85e0c5530",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT lastName, firstName, customerName, checkNumber, amount\n",
    "FROM employees\n",
    "LEFT JOIN customers ON employeeNumber = salesRepEmployeeNumber\n",
    "LEFT JOIN payments p USING (customerNumber)\n",
    "ORDER BY customerName, checkNumber\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "83e48959-a256-45af-8bec-da956ea95d28",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('employees', engine)\n",
    "    .merge(pd.read_sql_table('customers', engine),\n",
    "           left_on='employeeNumber',\n",
    "           right_on='salesRepEmployeeNumber',\n",
    "           how='left')\n",
    "    .merge(pd.read_sql_table('payments', engine),\n",
    "           on='customerNumber', how='left')\n",
    "    [['lastName', 'firstName', 'customerName', 'checkNumber', 'amount']]\n",
    "    .sort_values(by=['customerName', 'checkNumber'], \n",
    "                 ignore_index=True,\n",
    "                 na_position='first',\n",
    "                 key=lambda col: col.str.upper())\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "c2be2b87-b6b7-456c-93da-b92caaf04d33",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "c8a3d5a2-04d4-4815-b081-6a20a0a34f2c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>lastName</th>\n",
       "      <th>firstName</th>\n",
       "      <th>customerName</th>\n",
       "      <th>checkNumber</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Murphy</td>\n",
       "      <td>Diane</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Patterson</td>\n",
       "      <td>Mary</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Firrelli</td>\n",
       "      <td>Jeff</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Patterson</td>\n",
       "      <td>William</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bondur</td>\n",
       "      <td>Gerard</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    lastName firstName customerName checkNumber  amount\n",
       "0     Murphy     Diane         None        None     NaN\n",
       "1  Patterson      Mary         None        None     NaN\n",
       "2   Firrelli      Jeff         None        None     NaN\n",
       "3  Patterson   William         None        None     NaN\n",
       "4     Bondur    Gerard         None        None     NaN"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "793fba87-49e7-45ec-80da-7f2906bdfe07",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Condition in WHERE clause vs. ON clause"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e7958053-e035-4145-9cb4-190a459e6518",
   "metadata": {},
   "source": [
    "- Condition in `WHERE` clause"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "28fe47bc-bc7c-444f-81db-4573204860bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Notice that for INNER JOIN clause, the condition in the\n",
    "# ON clause is equivalent to the condition in the WHERE clause.\n",
    "string = '''\n",
    "SELECT orderNumber, customerNumber, productCode\n",
    "FROM orders LEFT JOIN orderdetails USING (orderNumber)\n",
    "WHERE orderNumber = 10123\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "24bb319f-274e-47b7-ad8c-904fb47e3d27",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('orders', engine)\n",
    "    .merge(pd.read_sql_table('orderdetails', engine),\n",
    "           on='orderNumber', how='left')\n",
    "    .query('orderNumber == 10123')\n",
    "    [['orderNumber', 'customerNumber', 'productCode']]\n",
    "    .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "9bbab1de-1742-4967-b388-760d6070a4d7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "dc727702-c1ff-4c52-9ea1-1642df573b51",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>orderNumber</th>\n",
       "      <th>customerNumber</th>\n",
       "      <th>productCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10123</td>\n",
       "      <td>103</td>\n",
       "      <td>S18_1589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10123</td>\n",
       "      <td>103</td>\n",
       "      <td>S18_2870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10123</td>\n",
       "      <td>103</td>\n",
       "      <td>S18_3685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10123</td>\n",
       "      <td>103</td>\n",
       "      <td>S24_1628</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   orderNumber  customerNumber productCode\n",
       "0        10123             103    S18_1589\n",
       "1        10123             103    S18_2870\n",
       "2        10123             103    S18_3685\n",
       "3        10123             103    S24_1628"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1e05d1c-25e1-4973-8eac-c58de2cc7fdf",
   "metadata": {},
   "source": [
    "- Condition in `ON` clause"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "287f6ae4-bbdb-4b8e-8a5e-54e6db00fc79",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT o.orderNumber, customerNumber, productCode\n",
    "FROM orders o LEFT JOIN orderdetails d\n",
    "        ON o.orderNumber = d.orderNumber AND \n",
    "           o.orderNumber = 10123\n",
    "ORDER BY \n",
    "    productCode DESC,\n",
    "    customerNumber ASC\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "07870666-b1d7-4f30-8919-292382df7f27",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('orders', engine)\n",
    "    .merge(pd.read_sql_table('orderdetails', engine)\n",
    "           .query('orderNumber == 10123'),\n",
    "           \n",
    "           on='orderNumber', how='left')\n",
    "    [['orderNumber', 'customerNumber', 'productCode']]\n",
    "    .sort_values(by=['productCode', 'customerNumber'],\n",
    "                 ascending=[False, True], \n",
    "                 ignore_index=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "ed635b81-13a8-4f9b-b06a-537aae794fba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "1db360a9-fd97-4563-8540-3fc5132a2583",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>orderNumber</th>\n",
       "      <th>customerNumber</th>\n",
       "      <th>productCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10123</td>\n",
       "      <td>103</td>\n",
       "      <td>S24_1628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10123</td>\n",
       "      <td>103</td>\n",
       "      <td>S18_3685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10123</td>\n",
       "      <td>103</td>\n",
       "      <td>S18_2870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10123</td>\n",
       "      <td>103</td>\n",
       "      <td>S18_1589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10298</td>\n",
       "      <td>103</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10345</td>\n",
       "      <td>103</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>10124</td>\n",
       "      <td>112</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10278</td>\n",
       "      <td>112</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10346</td>\n",
       "      <td>112</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10120</td>\n",
       "      <td>114</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   orderNumber  customerNumber productCode\n",
       "0        10123             103    S24_1628\n",
       "1        10123             103    S18_3685\n",
       "2        10123             103    S18_2870\n",
       "3        10123             103    S18_1589\n",
       "4        10298             103        None\n",
       "5        10345             103        None\n",
       "6        10124             112        None\n",
       "7        10278             112        None\n",
       "8        10346             112        None\n",
       "9        10120             114        None"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head(10)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.15"
  },
  "toc-autonumbering": true,
  "toc-showmarkdowntxt": false,
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 5
}