Section 08 Set Operators/29 MySQL UNION.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c2e262f5-43a9-4849-a3ec-f731d7a7cd2d",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## **[MySQL UNION](https://www.mysqltutorial.org/sql-union-mysql.aspx)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92e87d1c-9e10-4578-b39e-83535aaaaaff",
   "metadata": {},
   "source": [
    "Use MySQL UNION operator to combine two or more result sets from multiple SELECT statements into a single result set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "83ab9eae-16b1-404a-bdb8-800dcff518e7",
   "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": "9671693f-7d59-4c23-b6a0-68094a650f70",
   "metadata": {},
   "outputs": [],
   "source": [
    "# connect to the classicmodels database\n",
    "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": "1c1c5e90-d2f1-4e1b-a9dd-373386cc6396",
   "metadata": {},
   "source": [
    "Create two tables:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5faac86f-1eaa-46fe-9f34-e0242f90228c",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_13166/4110078888.py:1: RemovedIn20Warning: Deprecated API features detected! These feature(s) are not compatible with SQLAlchemy 2.0. To prevent incompatible upgrades prior to updating applications, ensure requirements files are pinned to \"sqlalchemy<2.0\". Set environment variable SQLALCHEMY_WARN_20=1 to show all deprecation warnings.  Set environment variable SQLALCHEMY_SILENCE_UBER_WARNING=1 to silence this message. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)\n",
      "  engine.execute('DROP TABLE IF EXISTS t1,t2;')\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7fb0881196a0>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.execute('DROP TABLE IF EXISTS t1,t2;')\n",
    "\n",
    "engine.execute('''\n",
    "CREATE TABLE t1 (\n",
    "    id INT PRIMARY KEY\n",
    ");''')\n",
    "\n",
    "engine.execute('''\n",
    "CREATE TABLE t2 (\n",
    "    id INT PRIMARY KEY\n",
    ");''')\n",
    "\n",
    "engine.execute('''\n",
    "INSERT INTO t1 VALUES (1),(2),(3);\n",
    "''')\n",
    "\n",
    "engine.execute('''\n",
    "INSERT INTO t2 VALUES (2),(3),(4);\n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2fdaeed3-7cdf-4b8e-ba1f-a124fd6c3113",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL UNION operator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "61679c49-3df6-4fef-aea5-5fe894665541",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT id\n",
    "FROM t1\n",
    "UNION\n",
    "SELECT id\n",
    "FROM t2\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "aec2b8f6-128c-4f34-80e3-c1c0831b1068",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.concat([\n",
    "    pd.read_sql_table('t1', engine),\n",
    "    pd.read_sql_table('t2', engine)\n",
    "]).drop_duplicates(ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c0e35776-871f-4c4a-b4da-67432a6a1bc5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "15d9072f-6041-435f-87c9-fdbce555905e",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3\n",
       "3   4"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "67b817c1-26eb-40e8-b207-3c785f0c2f7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT id\n",
    "FROM t1\n",
    "UNION ALL\n",
    "SELECT id\n",
    "FROM t2\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "21ca6419-ba42-44df-9be4-17279a407710",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.concat([\n",
    "    pd.read_sql_table('t1', engine),\n",
    "    pd.read_sql_table('t2', engine)\n",
    "], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "62cd65b6-88bc-4066-bb12-cb32e1d129ba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "ea3e2dd8-13c7-476e-b3fe-258bd049876e",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3\n",
       "3   2\n",
       "4   3\n",
       "5   4"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cca55a11-4ea5-495f-a674-e6bd57693c7b",
   "metadata": {
    "tags": []
   },
   "source": [
    "### UNION vs. JOIN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "2d033882-7e65-4869-aeab-313eda7b8fd7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAngAAAEjCAYAAAChNnyTAAAj9UlEQVR42uydv4oUTRRH9wFMfAZfwQcw8wUEU1NfQQRjQzMzIzE1MRJWFmVVcEEUzcTAwEBhWVEQNuhvfnx7mbZqt//MzM7euvccKHZnpqenp25X3zNV3dU7HQAAAACEYqcDAAAAgFAgeAAAAADBQPAAAAAAgoHgAQAAAAQDwQMAAAAIBoIHAAAAEAwEDwAAACAYCB4AAABAMBA8AAAAgGBsXPAeP35MWbF8+vSpg1wcHR252PdaLRCb/f19F/tZi+Xt27cd5AbBc1S+fPnSQS5+//7tYt9rtUBsPnz44GI/a7Go7iA3CJ6jguDlA8FD8ADBQ/AAwQteELx8IHgIHiB4CB4geMELgpcPBA/BAwQPwQMEL3hB8PKB4CF4gOAheIDgBS8IXj4QPAQPEDwEDxC84AXByweCh+ABgofgQRrBOzw87MS7d+9cNBQEDzwL3qtXrzrx69cvF/sxggeeBC9rPkHwYOuCd3x83Kk8f/78rGWUqFI2SAQvHxK8KfL258+fs5ZRO0HwICSSlCny9vHjR/IJggcInt+C4OUDwUPwAMFD8CCE4PUKDRLBS8+MIVoED8FLx0JSyCcIHrQieOq9E+qZsOe+f/9uz+t/9VakbJAIXj6m9uCpffSfszby9+/f7tu3bwgehGRqD97Xr1/JJwgeXLTgiV5js+RUkrJBInj5GBM8tQFDj58+fWrJqw+CByGRpEzonVMeIZ8geOBN8JSseg1Uz+u5lA0SwcvHXMFTT4X13Ok8Vgnfjx8/EDwIyQzBI58geOBN8E7gnAkELyUzBc96KPSXc/AQvPDMEDzyCYIHCJ7fguDlA8FD8ADBQ/AgtOAthp7Sd6kjePlYUfA0RKvhWYZoITQzBI98guCBN8FbJCdOikXw0jJX8HTe3QIuskDwUjBD8MgnCB54myal1wOh13RZuxprygaJ4OVjhWlSNKmrevBsAmTrpUDwIBwzpkkhnyB40MJEx1kLgpePheC52PdaLRCbhaS42M9aLAgeIHiOCoKXDwQPwQMED8EDBC94QfDygeAheIDgIXiA4AUvCF4+EDwEDxA8BA8QvOAFwcsHgofgAYKH4AGCF7wgePlA8BA8QPAQPEDwghcELx8IHoIHCB6CB00I3u7urnYsysxycHBAg0yI5vHa29tzsQ+2VlRvEBvaxuplf3+/g9xsWvCQlNV7cujBSwhxXxnqLQHkE+oOELzmIdHnhLgjeEA+oe4AwQsMiT4nxB3BA/IJdQcIXmBI9Dkh7ggekE+oO0DwAkOizwlxR/CAfELdAYIXGBJ9Tog7ggfkE+oOELzAkOhzQtwRPCCfUHeA4AWGRJ8T4o7gAfmEuvOJjjE7OzvdkydPuha5MMF7+fJlp4q7fft2ByT6rEyJuw4uaitWrl+/3gGClwHlk4n7Am3DqeBZbFRaA8Gbv1NJ6lRpCB6Clx6L+8gBpv+YdoPgpWFKPrl//z4/fhwL3iI+ikuTooTgzdyprDdCFXflyhUSFYKXmlXirjajtpMd2kt8lE8mjATpryQCwXMoeItjlfK+YtNcvkfwVt+pEDwELz0rxF2/iBE8BC8FM/IJgudQ8EzA1Vatc6ekL+j1yN5yHcJer6RruVyxjupzdOws1lEJXf91BA/BQ/Bg7bjTbhA8QPBCCZ4Nzw70hhWyZaJWPTZRtPXa47IntzxOlp+jZcrRxP4y5XsQPAQPwYPtxF0HGjtIZYf2Eh8Er23BU46XjA3EqBCoYrmiF7B4n61bHqFy2rFy6HPsuf7yDNEieAgebD/uWs4OaoDgZQDBa1fwCjEre95qyapPQxkSvP45fTbsWpdxwdNn9T8TwUPwEDzYety5ShDB+4fXr193x8fHXWQQvHYFbzlbRlUkU5sWPFtnCYKH4I3jKdFDHCbGXW2F5IXg/cO9e/e6GzduhJY8BK9dwStErozTJodobflVBY8hWgTPR6KHWFjcRxIXV80ieKcK3qVLl0JLHoLXpuD1LmAYem3oIgv9Hb3IQlQXZiw/R34xRfDKU2C4yGLNefDKkj6BIXg5GYm7HbjKwrl4CF4nwbt7964EL6zkTZwHr1+avWtCJMEbmKuzFCkTqHL6kjLGRawreSxfN9mfKnj1Opbz9SJ4XrqFWwXBywlxR/DWEDwViV1YySOfxK87E68RiU/f3hG8hiHR54S4I3jrCt6CsJJHPolfdwgeghceEn1OiDuCtwHBCyt55JP4dYfgIXjhIdHnhLgjeBsQvLCSRz6h7gDBax4SfU6IO4K3AcELK3nkE+oOELzmIdHnhLgjeBsQvLCSRz6h7gDBax4SfU6IezzBe/HixVbKrVu3TPDCSh75hLoDR4K3u7urHYsysxwcHNAgE3J4eNjt7e252AdbK6o3j+iE8GvXrm2lPHr0qFsQVvJoG6uX/f39DnJDD54T6MnJCXGP14PnbZLdliWPfELdzbyDhrv2dzYIXhpI9Dkh7ghe1yF55BPqDsFD8MJCos8JcUfw/gfJI59Qd5sWPB0j7DZn54Tre9UieE4g0eeEuCN4S5A88gl1h+AheOFwkOjVCIZuYq/nq9f1WOXq1atj6yvfM+sm+j9//uzu3Lnzzw2pb968WTfc8W1ydaN+BA/BK0DyyCch6k43+9dxVn/VJm7fvn1qDtAxfUl1w/+hO1rYc/pbCp4+e0puEdo2vV5uZ70ek7nlZ6n03zvwHUoQvDS0LnjWyM5D8N6/f99dvny5WK5qcGdtE4IXFK/15l3wWpI88km7dWdiVxxry+ckRZK8skfM2rhenyp483vw6vdo27S8/V+ur8w5xeOB71CD4GWhdcFTA9VOPFOmdBAYa4DqibMeO6sj/VWPnp6X/OnxWduE4AXFa721IHitSB75pN2607FdpZCppczVEjYgZOcmeMoFtk0ler6UN+UUfa9a5uYPCyN4WWhd8B4+fGiNb6OC9+zZs+Vwa42kT69L9oa2CcELiNd6a0XwWpA88km7dadju2TolKHQslgOKJfZhuCJenh2+d7TyoDg1d9zgJiCV1SW2wP1PyRI9GsInrbdRGuTgqeGUjagcvi2bnD1NjUtePru5TkrgOBlkLwJ+aR/Xq6bnpMR3NTdRQienhcT43reQ7TlugZ6E2dfZGHfIc8QrYKryug9bu6AiOBVgmeNV8OluiBiPcGrXx+qG9uGsW1qVvDK9mEHjOwgeAie2kFxPhedBo4Fz3LIzKtiNyN487fbcsUUwXM5N98WBG9UKvjVFUTw9H7b2TcteGKW4NXb1KzgnZLU6MVD8MLL3Qr5RPuE62kr/sdn3W1B8MRpQ6F2PFPc9Fp9rKvfa7EeELxJAqbttPeXUqe/pVTq8/vr0/bZ8v3v4OJ4veOlQSJ47QveyQUR2pndCF5vm8IInupEJTsIXmy5O8knoeYlO8Ft3Z2L4I2fpjV0nt7QFCrmD7Xg1XlraL/Qa9OmSalzh9ZZvqZ1ME3KSeU1c0D0lug9Cp7t7FrXJgVPQ6xnYFfSTtmm5gVv8V1IYAheCrlbIZ+oXTBE60TwYIwEgsf5RP4ETxclrCh4QrKlq1tXF7z6KlldTTu0zVpuyjY1KXi9X62utn0EBM8prcjdCvlEPfX0biN44EDw1BA5l8ih4GlIc0TGapmqRVC/ptcVPFuHlht6v5abuk2uJGmFuKvN0G4QvPByNzOfqKPAdd33cFd3EJNtCx5XOzkWPOvpsl5V2x4Nj1q8TCwGZErvMylbV/D02bZN5UTH/W3SclO3qXnB40RyBC+D3M3IJ2rPnMuN4IEDwVNjRO6cCl4x51pZNFQ6IHiVwG/iVmX6zLOWlfxpLryp2+RumBPBQ/AWIHer5xO1A+QOwYMtCR6NsWHBE4qNest6EqXHJlJTZErr2JTgCX22tkHbYttkvYxztqk5wVu0F/U6cmoDgpdO7ibkE7VpfuwgeOBF8HqzjjM7v1PBA+En7guho600JnjXrl3bSnn06FFYubN8MqNtuJiawgsIHnAvWicgeDkh7vEE78WLF1spt27d6u7duxdW7sgn1B0geCEg0eeEuMcTvC0huVMJK3fkE+pu69QTNGvExM0pPQheo5Doc0LcEbw1BS+s3JFPqLuZU+QgeAieT0j0OSHuCN6aghdW7sgn1N2ggNX350bwEDyfkOhzQtwRvDUFL6zckU+ouwEBY4gWwWsHEn1OiDuCt6bghZU78km7dSdBkigNzN9Z3oKxkjY911/mwYMHZ82+IQGz/8XwDf/r2TymCp7+12vN3P8YwXMCiT4nxB3BW1Pwwsod+aTduqulZ/lcX+701yjkSa/bMkMCVgtePWSrz7b36Hmts78+lXHBW96koRRJt/c/3rjg7e7uaseizCwHBwcczBJyeHjY7e3tudgHWyuqt8xI7u7evRtW7hbQNtYo+/v73QVSTkAtEVIp/q+l0KgmpB8XvDl3+qnfOy54Wncpna4n2qYHzwn05OSEuNODt47gXbp0KazckU/arruiZ8vEqBgirUopTnMFT58xNmRafuY0watf1/a5nlQbwXMCiT4nxB3BW0PwQssd+aTtujPRsv8lSpU01Zy34Nn5fqv04FVSp+VUvILgOYFEnxPijuCtyuvXr0PLHfmk/bqTHNn5b0UvmMp0wVt/iLaWv/mCV67f9fAsgucIEn1OiDuCB+STqHUnMZIsFRc26P9SjkwES8EbGvodkrTTLrI460reOYJnyzRxz2MEzwkk+pwQdwQPyCdR605ttBCoUvKsmLSNCZ7WWbynELxawlTKIVYrc3rwynW4Hp5F8BxBos8JcUfwgHxC3bVFf5oXzyB4TiDR54S4I3hAPqHu2sKGnb2D4DmBRJ8T4o7gAfkkWt0terfSFY9sV/Dq4DfRzSlI9OAg7vrV6P7KrRMQPDjvfFLOqUbbcCJ44INtC566NsvZrd3abx9viR5iMCPuOtFXJwCTxBC8NCifjLQJlf5jt/cFLUDwIJ7gtXqyoqdED3GYGHctY4kLwUPw0jAzn9A+EDzwJHiLhsgvLgQvLYNxryf3dH/vwx4IHtBhcDou6w7iceGCZ3PZZAfBy4nFfcKPoCZubt0HwYMt5xOdj9fE1Y0e6w7isXPBJ8XSGBG81FjcJ9zuB8FD8NKhfDKhd9v91YwFbuoOYrN9watPkqVLHcFLi8V97MKKBQgegpeOmfmkifuDLnBZdxAPF/PgLRJY+mFaBC8nFveJvRMq9H4jeGlY5JOwk9B6rDuIBYLnBAQvJxPjTg8egpcSBA/Bg4YEr7hiVomKIVoELy0IHoIHK+cTiVw/d+h/2geCBxckeGp43MkCwQMED8GDdfOJCR13skDwwOsQLSB4WSHuCB6QT6g7QPACQ6LPCXFH8IB8Qt0BghcYEn1OiDuCB+QT6g4QvMCQ6HNC3BE8IJ9Qd4DgBYZEnxPijuAB+YS6AwQvMCT6nBB3BA/IJ9QdIHiBIdHnhLgjeEA+oe6gCcF7+fKlDryUmeXz58/dp0+fOsjF0dFR9+bNGxf7YGtF9Qax+Y+9+1lpK4gCOJx38WF8Fh/FByluu++yuhAkAUUXiiiIRNwooiCITD3CQBiaP0rAuSffBwdrvE3KcC/5MbdpDw4OujjXhjiHh4eFzbb2wHt8fIxdCfONeX19LWyW9/f3iLwuzr+hTawbuY3H4y7OtSHOZDIpbLZRAYAOuc1o7RB4ACQjUqwdAg+AZESKtUPgAZCMSLF2CDwAkhEp1g6BB0AyIsXaIfAASEakWDsEHgDJfCVS9vb2ymg0KltbWwWBh8ADoFNfiZSIu+3tbYEn8BB4APRs1UjZ2dn5nN3dXYEn8BB4APQsImWV//88du8uLy8FnsBD4AHQu1UiJYIuwu6DwBN4CDwAeheRsuSDFTXoBJ7AQ+ABMARLIiVuzcYtWoEn8BB4AAxFRMqSv3s3d2J3b5MJPAQeAF1aMVLs4Ak8BB4AQyHwBB4CD4BkBJ7AQ+ABkIxIsXYIPACSESnWDoEHQDIixdoh8ABIRqRYOwQeAMmIFGuHwAMgGZFi7RB4ACQjUqwdAg+AZMbjcXl+fjbfmMlkUthsAg+ALt3e3pa7uzvzjbm/vy9sNoEHAJCMwAMASEbgAQAkI/AAAJIReAAAyQg8AIBkBB4AQDICDwAgGYEHAJCMwAMASEbgAQAkI/AAAJIReAAAyQg8AIBkBB4AQDICDwAgGYEHAJCMwAMASEbgAQAkI/AAAJIReAAAyQg8AIBkBB4AQDICDwAgGYEHAJCMwAMASEbgAQA0Li8vy2g0Knt7e2WI1h54Z2dnsSjmi3NxcVGm02lhs7y+vpbT09MuzsGhTawbuZ2fn3dxrg1xrq+vSwdqJH3M8PaTBF7j169f5psTkcdmeX5+7uLcG+qQ23g87uI8G+JMJpPSg93d3bK9vT3IUBJ4Am9tEycTm0XgCTzmOz4+7uI8G+LE2vVga2srAukz8nZ2dsqQCDyBJ/AQeAIPgdfV9BB4f//+jUCK97aIpM9fN+KxOK7u8sVECLbPUY+tU6OrPa59jvZ1Ijjb52iDbvbnAk/gCTwEnsBD4PUzHQRevT27cDesja0aau33NRTr89bvZ4+Jr1WEXERe8zr1mBpv9TnqMe3vEXgCT+Ah8AQeAq+f6SHwIrIixqqIvZgZTUA1xzW7gO3vq88dURZTzQTcvNdpH6vHu0Ur8AQeAk/gIfD6nR8OvDbM2p23ReEVx0UcLgq82b/TV2+7trMo8NpIrK8p8ASewEPgCTwEXr/zw4EX8TUvuiKm1h149TlbAq+3wHt4eCjh6OioiwtF4NFz4O3v75fw9PTUxXks8Ogp8Hp+P5m5dtMFXhNy7e3Xdd6ircd/N/Dcol1X4L29vX3Onz9/5h0TJ3u3F6TAY/2Bt/wN4OXlZd4xcZ0IPFKKSFkl3k5OTrp7P7m6uiphOp0uunZTBl7zAYZ5P1v0IYv4uvRDFlXzwYz6OrHDt2rgxXPX733IQuAJPASewEPgCbxGhFW93dlqQ6oGVPvPl7TxVr+2sVe1P1/lwxxt0P3vNeLPJfC+cYu2uwtS4NEawi1agUdWH5GS+v0ka+AtsSy8/hdu3h+HEHixexdiZ6I+Np1O6+Px69it6PaCFHj8xA5eXB+zj8U1Uv8v25ubG4FHSivu4MVuWXfvJ3UHL/6MC65dgSfw8gReVS+2eoI3BB4bYVngxTVQxfe/f/8u/9o7m9wokiCMzgG8scSCg/guPgNX8MZHYOuLeItgg8zC4A0GiQ1LJCyBT9BTn6ZjVI7p+h13ERnxnpRqu9yurs6s6ngd+VMKXg4ED1IiSZmRnVMcCRdPnLwNXbsIHoKXV/B0wvcuUG3XNgQPSrBU8JQVsG//GseqoPHz508ED1KyQPDCxRMneEPXbnnBg8SCt6eZMRMIHvxBwbOgoUfG4CF46VkgeOHiSU/w/LXLGDzoQPBCBBEEDxC8mAVyg+AheJBA8Lr0NV20CF45VgqeunnUxUMXLaRmgeCFiydO8Py1Sxct5Be87gQPMygWwYMnBBQ8jd3pYJIFgleCBYIXLp44wbNrl0kWsBMpBc8tk9L/FqO/aVq7LgwED0qwYpkULeqqLIAtgGxZCgQP0rFgmZRw8cQtkzJ27SJ4z3wHDQi80HHVguDVoxO8EOdeqwVy00lKiPOsxYLgAYIXqCB49UDwEDxA8BC84wieYqrd5uxIhL5XLYIXqCB49UDwEDxA8BA8BA/BS14QvHogeAgeIHgZBU83+9eN/PUoCXr16tUTKbKim/mLgRv+j97Rwrbp0QueXtv2YUXbDqBj09/9cfr9mMz1X0vF/e/Ae/AgeLUKglcPBA/BAwQvq+AdkCq/TVJkkuczYoqJ+vscwVubwfP/o2PT8+1nvz+fsXO/j70HD4JXqiB49UDwEDxA8LIKnoqTqb7MeQkbErJjCp4kzo7Jo+1e3iRrel9e5lZ0CyN4pQqCVw8ED8EDBC+r4EmGBrpCfZEQ+edsIXii38Xq//dQGRO8kffgQfBKlW/fvu2gFggeggfDfPz4McR51mL59OnTbprtBU/bJ7DsmUnXMQXP72sgm7hqkoW9hzxdtDc3N7sfP35QFpbv37/vvn79uoNaPD4+6oM4xDnYWlG9QW4+fPgQ4lxrsWgR5VG2Fzwb07Z0VuwWgueP240XnBS8kGvzPferlpya/UyZHLpoC0K7r4Z6KwDxpN26M1FyHOoKVabLZEh/G5uAoW2+C9UL3hIB03Ha/3up06OXSr1+f386Pnv+1HvwIHhVINDXhHZH8IB4krHunOB56eqXsXF6Q92oJl5TgmeS5kXPH8/cZVL0u8/Q+b9pHzmXSeGCJNAD7d6B4AHx5CDUHXQgeHUg0NeEdkfwgHhC3QGClxgCfU1odwQPiCfUHSB4iSHQ14R2R/CAeELdAYKXGAJ9TWh3BO9/EGK1fCQlJtQdbCp4/oPJZqwAgb4qU+1us7T8KuqA4ImTk5PUkqd4MnNBXJUwt4jyM0L9LMsodQe52VLw/EmP4CF45Zlod/3NL9zJdYPgPVne4eXLl6GkZqN4YrFE0uRjS4hzQ1/M9GXMf1HTo0DwIJ3g7U9yW9yQQIXglWZBu4dYODMSXC87y1illbylkqJzwiQqIvuYF7LuIB/HELyQJ3t0ELyarGh3ZSwQPARPWHY3reStkJTQgtdl9MoIXvdeS8f36UWYEbwyIHg12bd72CxAZLhe/hW8tJK3UFIURMN00Tp0TKW6aI8oeKrDcOMtETwCFYIHh9o91QfbHgRvO8FLK3kLJUUxJewkJBsfKCLWXYuCt9FngK4nez0ED8FD8OD5213Ps5l4gOD1BC+t5HXxJKxARc84ZRa8pyB4CF5wELyaLGh3lkhB8IYEL63kKZ7MHJcaNrOtY/JfzCLV3ZEFz9pGRb8vXUrG2tX+LlHywqXX6u/D/mdqKR0vb/Z3+90fX/8YTNr8sc8RPJ+B9E6E4GUDwavJvt2b7noaAMHbTvDSSl4XT0JmxzrCZ90DCJ6996ExiHqOPtvG6mtKyqZWGfBi6c+Xvjz6fYy9ni2D45+jxzkZvDEhRPCyEUDw7JvWaDH8t6aHh4fRjJO/kC4uLuZ+C190TPYavpydnYWcWTfR7lbPzJptSPAuLy+3Knb+p5U8xZOJ9xd61mxPLhw1BM/eu9vmhcbHgSERGhOuKanyx6EytC+/zf8+PYN7+lhUDzoGvzpC21209iZdKR/AWhc8/e8SwTs9PZUUbiV4YRfWHmj3qffDWLzAgqe22VLyOtJKnuLJjCyRLyHG4o18nqotygqeihOeMXkzaRp7jt//kER5mZoSPD3654y18RLB8/vU8Wh/bQsexBU8dwGZoI1dwCbnKnMFT8+1C2Gu4Gk/a49bImn7DreEQrR2b4mo9RZxoH+rkkc8abfuNhQ8P65tC8HTPvy2JYLXlzp7rh4RvIxEC/RLBO/q6spO7EnBs23n5+c6ubcQPEPdtOHG6kRr95aIWm8RBa9VySOetFt3I4LnJWpIvuYKnu/K9TFl6NjWCp7fx1rBs7F+9p5zzaKFuIF+geDpuTo5JVCzBc9O9uvrawQvULu3RNR6iyp4LUoe8aTduhsRvLFJFiZJSwRvQJAGJ1kMj6+bnjDh5czFw0WCZ69jcorgZSVaoF8oeG5m0qTguZmhRxU81asuxpBjPaO1e0tErbfIgtea5BFP2q27EcHz2w6NYVsieH4ZlKGZuH6YzlzBExZDbLt/XRO+JYJn+7DXR/CyEi3QLxQ8jXPTxAmd5HMEz49hWDnJwvY1PSnB5O7u7m4XiWjt3hJR6y264LUkecQT6i47e+nNt9AxxA30SwVPaOkTE7YZgicptG8+xxY8dc+Gqt+o7d4SUeutBcFrRfKIJ9RdZnpd0gheZqIF+sWC5xepnBY8S3vbkinH6KLVMennsHeBiNbuLRG13loRvBYkj3jSZt111wBlfUHwshEt0K8QPKHZsRK2uYKnLlObiXvMZVJ0TCEXRI3W7i0Rtd5aErzokkc8oe4gkOC9efNGJxZlYbm9vQ11Qa4QPL+y/JTg+ZlURxM8oX1bV20kfv36tXv37l2Ic7C1onqLSGuCJ16/fr178eLFTudjJLg21pf379/voDZk8IIQLZOzQvD8fVPnCl5fBo8leJbFY5mUREStt9YEjwxeTqg7QPCC0PKtyiwwOGGbI3h+qvhz3apsaPXycGPxEDwEb4TSckc8oe4AwUtBJsGzbNkCwdNrH1vw7LhC3a4MwUPwBigvd8QT6g4QvBQQ6GtCuyN4B0DuiCfUHSB4WSDQ14R2R/AcyB3xhLoDBC8TBPqa0O4IXg/kjnhC3QGClw0CfU1odwRvD3JHPKHuAMHLCIG+JrQ7gteB3BFPqDvIIXi9GZChZjSOQaCHrdvdLztjRUvKQGzBu7y83KqklbsZ8cQvsRTubjW6FWP/2PS7iFJ3kJutBU/LV9gFaL+H/Mb7XxA8+DPt7q8PCxTViXq9bCh3OhfSyp3Fk4mlnVT6v4dJGqjO/TJRfQFF8CCd4B24CMLdXeAAIQI95GNFu0vuyOIFFryNMNlPK3cr4onOiXBZPJdt1PUbsu4gH39FuSBb/hBC8GDDdldWINTdOA6D4IkNBC+t3GVMGHTHpixjyLqDfPxpwdPJThctgleWhe2uzEToDEUPBG8bYUgrdyviiTJkYbPbXebOuo9D1h3k408IHuOJEDxY0O6WlVCxDAAgePvPz7RyNxFP/DhulXDJAn/7R2uXKHUHudla8PyFGfbb1n9A8CBIu0fOUvRA8LYRvLRy14snKTLc++FIjMGDf8gseN1JHma20ywCBnpon327pxpIbiB4mwheWrnrxZM0k5D2AqpzN2TdQS62FLyQU9mjEEHwfFegL0NcX1/vzs/Pn6zTpjZ+eHiY7E7xxU8esO1nZ2eHjrf5LksED8H7n4KXVu4QPAQPGhO87gQPPctpiIiBPoDgWSbWF5MyJ3mrBc/OmXKC110vqhOGNiB4Q4KXVu5mxBNdB/33rp/DfPnRZ6P7bKKLFnokFLzequOszh9M8Bxeyg5m7rT99PRUH6hPtls7X1xcTGZy9ejwgmfrR5UTvH39c60geAc5OTlJK3cj8cR/BoS9k0V3PNzJAgZJJ3iQR/CUodN2CZ3n7u7OPtRcFm+d4F1dXVnXRjnBAwRvgNRyRzyh7gDBS0HkQG+C54LrVDbJxuVZEForeCZ2lhFE8ADBKwLxhLoDBK95Igd6J3gmV77LYVrg1guejWlRd7AygggeUG8FIJ5Qd4DgNU/kQO8Ez8vVFoLnB1AjeEC9FYB4Qt0Bgtc8kQN9FMHbj/tTtzCCB9RbAYgn1B0geM0TOdAPCN7cWbKaIPEcgueX2UHwikO95Yd4Qt0Bgtc8kQN9T/D8LFll1Kb+T899LsETGoenCRwIXnGot/wQT6g7CCR4XdDVBy9lYbm/v999/vx5FxEJlURqaE1DtblD29ws22cTPPs/ZfOaF7zHx8fdzc1NiHOwtaJ6g9x0khLiXGuxfPnyZQe1IYMXhAiZHGXaJEt6FJqtaneqUMbMoecOLXSsbdY9+9yCp3qyu140L3hk8MjgAQAgeImJfKsyyZpJ38Bix4eK5PDZ7kXrMPFE8ApDvQEAIHjhCRDolbGTLJm0WebOy53H/49+tozesQRPMorgFYd6AwBA8MJDoK8J7Y7gAQAgeIkh0NeEdkfwAAAQvMQQ6GtCuyN4AAAIXmII9DWh3RE8AAAELzEE+prQ7ggeAEAawfMzJzUjsjoE+posbXdbXFqzlKvD9QIAEEjwtH6ZgpNfz6w6CF5NFrS7loPRMjIIHoIHABBP8BzK3pHFQ/DKMtHu/u4dekTwEDwAgPiCt7+faPkPawSvJjPbXZk7uzMIgofgAQDEFzy7e0F1ELyaWLvP+RLUgeAheAAAcQXPBoqr6GdA8Kpi7T5xvUjoEDwEDwAgtuC5geOMwUPwymLtPjWxogPBQ/AAANoRvA4FsPLdtAheTSba3WbN9gvZbwQPAADBawUEryYz250MHoIHABBb8NyMWQUqumgRvLIgeAgeAEAKwesCE3eyQPAAwUPwAAAyCR4geEC7I3gAAAheCQj0NaHdETwAAAQvMQT6mtDuCB4AAIKXGAJ9TWh3BA8AAMFLDIG+JrQ7ggcAgOAlhkBfE9odwQMAQPASQ6CvCe2O4AEANCF4b9++leRRFpbb29vd/f39Dmrx+/dvrQMZ4hxsrbB+JgDAmOABAAAAQCoQPAAAAIBkIHgAAAAAyUDwAAAAAJKB4AEAAAAkA8EDAAAASAaCBwAAAJCMvwGcOfpTp1FuswAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Image('../images/MySQL-UNION-vs-JOIN.png')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70b963d3-701f-41f1-bc65-5c8e763871c6",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL UNION and column alias examples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "7a862e5b-1b2c-418e-a496-f6048428654f",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT firstName, lastName\n",
    "FROM employees\n",
    "UNION\n",
    "SELECT contactFirstName, contactLastName\n",
    "FROM customers\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "c9965bef-9519-42da-9ec4-fe116fda2701",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.concat([\n",
    "    pd.read_sql_table(\n",
    "        'employees', engine, columns=['firstName', 'lastName']),\n",
    "    (pd.read_sql_table(\n",
    "        'customers', engine, columns=['contactFirstName', 'contactLastName'])\n",
    "     .rename(columns={'contactFirstName': 'firstName', 'contactLastName': 'lastName'}))\n",
    "]).drop_duplicates(ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "4a95c668-f0ad-4993-978a-ccbdca00c859",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "3220e291-7172-4bee-b6e7-7a025250416c",
   "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>firstName</th>\n",
       "      <th>lastName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Diane</td>\n",
       "      <td>Murphy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mary</td>\n",
       "      <td>Patterson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jeff</td>\n",
       "      <td>Firrelli</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>William</td>\n",
       "      <td>Patterson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Gerard</td>\n",
       "      <td>Bondur</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  firstName   lastName\n",
       "0     Diane     Murphy\n",
       "1      Mary  Patterson\n",
       "2      Jeff   Firrelli\n",
       "3   William  Patterson\n",
       "4    Gerard     Bondur"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "f8f5c9fc-4c2a-4884-94da-7b512e84d622",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT CONCAT(firstName, ' ', lastName) fullname\n",
    "FROM employees\n",
    "UNION\n",
    "SELECT CONCAT(contactFirstName, ' ', contactLastName)\n",
    "FROM customers\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "ba1bb578-e69a-468c-b8a6-32ed63ede630",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.concat([\n",
    "    (pd.read_sql_table('employees', engine, columns=['firstName', 'lastName'])\n",
    "     .assign(fullname = lambda df: df.firstName + ' ' + df.lastName)\n",
    "     [['fullname']]),\n",
    "    (pd.read_sql_table('customers', engine, columns=['contactFirstName', 'contactLastName'])\n",
    "     .assign(fullname = lambda df: df.contactFirstName + ' ' + df.contactLastName)\n",
    "     [['fullname']])\n",
    "]).drop_duplicates(ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "6f19a923-3534-411c-b49b-a48b1d5922be",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "bee5cad2-ad5e-44e5-94b5-822cf6479679",
   "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>fullname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Diane Murphy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mary Patterson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jeff Firrelli</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>William Patterson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Gerard Bondur</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            fullname\n",
       "0       Diane Murphy\n",
       "1     Mary Patterson\n",
       "2      Jeff Firrelli\n",
       "3  William Patterson\n",
       "4      Gerard Bondur"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82cc6e1a-1566-4b31-81db-a7b7d683fddf",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL UNION and ORDER BY"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "a3ff865d-f6db-4983-a322-aece06c3057a",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT concat(firstName, ' ', lastName) fullname\n",
    "FROM employees\n",
    "UNION\n",
    "SELECT concat(contactFirstName, ' ', contactLastName)\n",
    "FROM customers\n",
    "ORDER BY fullname\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "65731005-cc29-4fdc-83b5-fd92efdb7f58",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.concat([\n",
    "    (pd.read_sql_table('employees', engine, columns=['firstName', 'lastName'])\n",
    "     .assign(fullname = lambda df: df.firstName + ' ' + df.lastName)\n",
    "     [['fullname']]),\n",
    "    (pd.read_sql_table('customers', engine, columns=['contactFirstName', 'contactLastName'])\n",
    "     .assign(fullname = lambda df: df.contactFirstName + ' ' + df.contactLastName)\n",
    "     [['fullname']])\n",
    "]).drop_duplicates().sort_values(\n",
    "    by='fullname', key=lambda col: col.str.upper(), ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "d0258651-1c39-4eb0-add9-0aa93b9467a5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "75513b3c-58d5-4c69-bd56-b2508c05a467",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "fullname    3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1 != df2).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "42e8927d-e8bf-466e-b16a-e9a21b09fcdf",
   "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>fullname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>Martín  Sommer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>Martin Gerard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>Martine  Rancé</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          fullname\n",
       "86  Martín  Sommer\n",
       "87   Martin Gerard\n",
       "88  Martine  Rancé"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.iloc[np.where(df1 != df2)[0], :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "28382dae-a303-414e-8adc-2593fcb7ab1e",
   "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>fullname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>Martin Gerard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>Martine  Rancé</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>Martín  Sommer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          fullname\n",
       "86   Martin Gerard\n",
       "87  Martine  Rancé\n",
       "88  Martín  Sommer"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.iloc[np.where(df1 != df2)[0], :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "5dd326df-8080-49da-8abf-63fff5eba1b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT\n",
    "    concat(firstName, ' ', lastName) fullname,\n",
    "    'Employee' as contactType\n",
    "FROM employees\n",
    "UNION\n",
    "SELECT\n",
    "    concat(contactFirstName, ' ', contactLastName),\n",
    "    'Customer' as contactType\n",
    "FROM customers\n",
    "ORDER BY fullname\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "13c55c3f-416c-4b0c-96e4-d5359ac4e255",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.concat([\n",
    "    (pd.read_sql_table('employees', engine, columns=['firstName', 'lastName'])\n",
    "     .assign(fullname = lambda df: df.firstName + ' ' + df.lastName,\n",
    "             contactType = 'Employee')\n",
    "     [['fullname', 'contactType']]),\n",
    "    (pd.read_sql_table('customers', engine, columns=['contactFirstName', 'contactLastName'])\n",
    "     .assign(fullname = lambda df: df.contactFirstName + ' ' + df.contactLastName,\n",
    "             contactType = 'Customer')\n",
    "     [['fullname', 'contactType']])\n",
    "]).sort_values(by='fullname', key=lambda col: col.str.upper(), ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "46022eb4-167a-4f17-8a5f-34296974f804",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "d3a713e4-cf5a-48b7-a2cd-a7951b969af6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "fullname       3\n",
       "contactType    2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1 != df2).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "8f1899ca-6fac-4b38-bcca-68ed7b0d1688",
   "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>fullname</th>\n",
       "      <th>contactType</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>Martín  Sommer</td>\n",
       "      <td>Customer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>Martin Gerard</td>\n",
       "      <td>Employee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>Martine  Rancé</td>\n",
       "      <td>Customer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          fullname contactType\n",
       "86  Martín  Sommer    Customer\n",
       "87   Martin Gerard    Employee\n",
       "88  Martine  Rancé    Customer"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "concern = np.where(df1.fullname != df2.fullname)[0]\n",
    "df1.iloc[concern, :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "15bb395f-446b-4ade-a16c-d1b9aceef6e1",
   "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>fullname</th>\n",
       "      <th>contactType</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>Martin Gerard</td>\n",
       "      <td>Employee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>Martine  Rancé</td>\n",
       "      <td>Customer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>Martín  Sommer</td>\n",
       "      <td>Customer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          fullname contactType\n",
       "86   Martin Gerard    Employee\n",
       "87  Martine  Rancé    Customer\n",
       "88  Martín  Sommer    Customer"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.iloc[concern, :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "56045ed5-f2e4-4a5e-be63-d91eb2a69685",
   "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>fullname</th>\n",
       "      <th>contactType</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Adrian Huxley</td>\n",
       "      <td>Customer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Akiko Shimamura</td>\n",
       "      <td>Customer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alejandra  Camino</td>\n",
       "      <td>Customer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alexander  Feuer</td>\n",
       "      <td>Customer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alexander  Semenov</td>\n",
       "      <td>Customer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             fullname contactType\n",
       "0       Adrian Huxley    Customer\n",
       "1     Akiko Shimamura    Customer\n",
       "2   Alejandra  Camino    Customer\n",
       "3    Alexander  Feuer    Customer\n",
       "4  Alexander  Semenov    Customer"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "6f5a44c6-2fdc-475e-b3fd-df24086d7655",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7fb0126b21c0>"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.execute('DROP TABLE IF EXISTS t1,t2;')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b8d45de5-abf3-415c-9fb7-51b951ec5d57",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}