Section 08 Set Operators/31 MySQL INTERSECT.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "262e247a-5a01-4fe8-9abf-53f17618f02d",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## **[MySQL INTERSECT](https://www.mysqltutorial.org/mysql-intersect/)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df716c5a-4ec5-4fa6-87bb-a04122ed0ca7",
   "metadata": {},
   "source": [
    "Use MySQL INTERSECT to choose distinct rows of two or more queries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "50a97cd4-4d89-4b3f-8e19-56fe03de51b3",
   "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": "e8f19945-7838-4cf5-8c87-49f3e1eb63d6",
   "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": "902a6a36-210e-4d69-82a6-79e17b250ee9",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Introduction to the INTERSECT operator"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7be0da5-3727-4962-bfcb-87618ca25cef",
   "metadata": {},
   "source": [
    "syntax:"
   ]
  },
  {
   "cell_type": "raw",
   "id": "03d84d67-dad3-4263-9745-87d1b47f1752",
   "metadata": {},
   "source": [
    "(SELECT column_list FROM table_1)\n",
    "INTERSECT\n",
    "(SELECT column_list FROM table_2);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "af6c44e7-b6c8-40ed-b420-eaa8f5b292cc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAcYAAAENCAYAAACVTcVeAAAnoUlEQVR42uydIVRUTxSHCQaDwWAwGAwEg8FgMBgMBIPBYDAQDAYDwUAwcI7BYCAYDAQCgUAgEAgEAoFAIBAIBAKBQCAQCATC/Od7Z2fPgrJ/VpZlZt73nfOOirAKe9/93fndO/PGgoiIiHQZCyIiIqIwioiIKIwiIiIKo4iIiMIoIiKiMIqIiCiMIiIiCqP04+zsLOzs7ITl5eWwsLAQ5ubmwvfv35vr8+fP4dOnT+H9+/fhzZs34fXr1+Hp06fNdf/+/TA2NhYePHjQ/Rifw/Xhw4fm6758+dJ9rfn5+eb1V1dXw97eXjg/Pw8io+D4+Dhsbm4Sf801OzvbxOS3b9+I0+aamJhoYvfFixdNLD958oT4bq7Hjx83H3v+/HmK8e7XTU9PN6/18+fP7uuvr6+Ho6OjIO1AYSwYblRu2N+/f4evX7+Gt2/fNjf7vXv3wrNnzxA/bnTEsCtmiCQ3+srKStjY2GiSy8HBAVcjqHB6epo+xudwJZHl30qv1U0k7969C+Pj4xf+XZILwrm1tdUkMZFBIR53d3eJPUSqibVXr16Fhw8fcvH7/xUzYpcikVg+PDzsvXf4GK+fYryvyFJAPnr0qCkaX758GT5+/Bh+/PgRlpaWeP3mnpF6UBgL4OTkBCHjRuSG5MbkBuVGJTkgfNzMfE6fldvoE9nk5CT/1z8SGX+3trZmMpEE4tJb4FFosbKj0KLwQvgo6ijk7rTQ4l6k2ENAEU6KQFadFIWsSFmlTk1NhV+/fvF5uiiFojBmSBQMhIMbD2HB4sTqKXoVdnl1i0jyfVGJU51TsSOs0g5iAYfQYdFTNOF0UEhRNFHgUWAVJyp8T7QVKFIpVhF1CliEHqFE/KUMFMYMQBAQhpmZGQSDmwkhrF4wegqA3u+blbHVdmUkIcTxSP093ANWXliatRILQuxWevMIJUUAq0yFMnMUxrsBSwjhQwi4WVohhNcQSlYLrCgZmEjVNqsIk0hhREcD0UP8EMHWCOGgQkmRQLFA0UAPVPJAYRwdJARWQ/RPsEhbL4TX7K0ilCRWxJJK2+nATIlxTH+ZniDFnkJ4faHEemVmgAKZn5n9934ojOVDgNMXpJdGdUifkP6JDAxFBAmEpEvyJaFYVGQA7kdK7AyfLC4u+r4MDq0DCkGsVmKcniutBlsKl1EYyyQGMk14LBICnAED/myAD3FlQvIlCZOMsaVIzjIyWAXieOB+MJWJ3a0VOFwrmkE1eu9Mu1pQX0BhLIrYB8P2Y2VIQBPYxU2PlkZMxiRl+jUkaaxqrbtbtLaT+0FBQqxvb28HufXBJQbzEEjbCaAwlgH2BwFL4DJhSSDLyGGSlT1kJG3sKAd2hld8IILJ/SDedT/uBrY80btNPVxzjcKYFySGjiBiJdEwN1lkAlYrVXU60o7epAzO/v4+yTetDl2lZERcvWNl405ZBCqMdw/ihwhi2yGKiKPkSe97hUAyyCDXawnQH0cQSb4kYcmTThGIQDKQZhGoMI4UApC9RibZAokCyTYCChmLmauhX8jqgyRLslUQC4L8RP835ScsV1EYR1GRsdnciqxwoigyGIVAMtmq/R0CMU0yxXom1t1qUTAdl4T2jkWgwngrAUaS0MOvlCQGVNjsh2wjcViJCVN+BvbIK6QzA8Glw6UwDmXDMhUX++TcO1Q5CCSnEPFeM2zSBuIWIjbkU/S5am4BUSApfijw3cqkMP5TwuC0CbZdtHYV0UaiMNA/RijYL1atldj5PtOUqT3EFhFjmj2+bPPg12pjXGG8nYTBPkTPKWwpqTCi18ZJRTURbVNWxlinuiAtJq4YWTmygtReVRivlTDcLCu9VnoV1hOrwl7bVAQQxY696lF+CuOFhMEZm9imJgz5m4vAUXO4CFhPRfbh4vg+gqhtKv3sVWKcWC8yxhVGE4aMHqppjkHjLNZiturECWq2pGibyiD2alExrjAOd5VIAGCdmjBkUOspnYWbdWUdtxhpm0rVMR4UxqGe6sFQBavE1r/p8s/DORzywEosu75Mp+jL8v8m5ZBzjHdRGIdWReOjewqEDAP6MazKsomnOCxE0WelL9XGeC8K4/Cs0+KnCyUvECNspzt2IFICc/Reqo3xP1AYb7QNQ+tURmE7/a3w0vKSKujEWesWF1UKY6yitU4lUme8xUreIQnpUGeM90NhtLqRQrhkO2mdSnW0yVqtRhjjqTWtedMkT3rtTX4/RDimkP2UWqdyBeXHeE5UIYypkmHjvshdQ3HGZumjo6NhDZGRiBBGiz7Jgk6MV1ukFS+MWEpYSzyhXSQXZmdnicsbn7+LuJKASEQiOdGJ8SrPmC5aGHk0FI9RYcUokhucPkPiID5v0h4gAYnkSIxxhnKqy8HFCmPctF9ttSL1kBwNfh2wPcDX+VxQyZ4aXbsihTGOqWMvOXkqRYDIDfLwa0biSTTr6+tBpARSjPNc2xooShgZPODZcjw5gN6LSCngbHDgBE5HPxYWFkgwzdm+IiWRYpxHWZVOMcKYxtUnJiZ8wr4UCcUcD0Cenp6+co8iTsj+/n4QKZEU41NTU0VPUBchjFEIHVeXKkixfGnKlD/rhEgV1JCvsxdGfrD8gLlEaoDEgQjOzMykQTJWilVvmJZ2kcSRlWOJZC+MsaeIfVps5SHSb3/i5ORkGB8fd6Uo/7F3dqFZlmEcn/Njfk3dt1PD5Xdqpij5NUFykaDUFMMdLBo0aonKICErwXAHUpFBIwokPZDYQQciEh448GAHHexgyQ4GSuzAgx0YeDDEgw6u7j+8NzzMrW3vx/Pcz//5/+AmWabbet/9nuu+vuiAHHGtevnyZUsbQYvRPVHjyVo5RUHJjRs3bPHixepTFLT4B8De3l5LE8GK0X0j8Q3Vk7SgBGvRqqurbdGiRVZZWWkPHjwwIRjJDapIVZ9jkGJ030B8I9WnKCjBD4qGhgZbuHChlZWVGQ7kCFkKwcjw8HCqppQFJ0b3jcM3UBNtBCW4AcFDH65QIcToqaqq0ute0OKnOQ0NDVnoBCVG91SBb1xqniqEmG0xwtatW32k+NKZO3eu1dXVKX0gaMndBgb/ABiMGN21aeruoYWYTdvRwYMHkVOEBKc88+bNs7Vr1xpWTQnBiJvuFHz9SBBidE/S+EZpn6KgBFJ87733bNmyZZDftGfBggW2a9cutSgJWtC7u3PnzmA7DoIQY1tbm/bNCVrQx4UrUkSDEN9MztKlS+3MmTMmBCsdHR2GEyKJixHT2Pfs2aOnY0EJNmSgqMZfoc7mLFmyxLBpQwhG/AAAXK2GRpJiRHUSim3UliEoQQ6lvr4eV6OzlWK0jUPvD0GLL7jEP0MiKTHiaQGjsPRELCjBDciRI0d8XjHvU15ebnifvHjxwoRgxEWMiByDyjdOKUblFYUoLK/Y2Njo84qFHPQ8BpuLEYIx35iEGJFXREWSnoIFJcgr1tTUzDKvOH0xzm+//WZCMBJavjFuMfo7ZS1jFZQgr4hI0TfxF/GgGCe4XIwQjPnGWMXonwr6+vpMCDYm5BVLcpqamoLKxQjBmG+MVYy4Q+7q6jIhGEHT8urVq30VaklORUWFffLJJyYEK9PlG6nE6Ea9Ka8oaEFLBfoV/XDwEh78HdrEIWjxN4twxqSwiNF9oZiDqjezoOXYsWPoOYS4Yjnr16/XUAxBC1wBZ8AdL8EiRrRldHZ2mhCMIGe+atUqX3ATy0HFa09PjwnBCpwBd0yEQoy+0ujp06cmBBvYgoHpNl6KMR78naruFrTAGZNVqVKIsbm5WVszBC0oEqitrY1din4qzu7du00IVuAOOCRK6sWI0tt9+/aZEIw8ePAAjfwFFtwU3tt448YNE4IVOAQu8aRajLhiQhg8ODhoQrCB6upNmzahfSIpKUYHjStVIWiBQ+ASOAWkWoyuX1E9i4KWS5cuoWcR15mJi3H+/Pl24sQJE4KVqE9SK8ao4YVgAwUvK1as8FeoQRx8LrjaFYKRyA1kOsWI3iosHr5165YJwYgruIEYgxBi9OzatcuEYMU5xS+1T58YsTkDVURCMDIyMgIpouglCBlOjBqx2UMIVpxb4Jh0iREFCQh3sZlfCEZaW1uDjBb92bhxownBinMLHAPXpEeMvb29hh8cQjCCN2V1dTV2IwYhwan2Nt6+fduEYMU5BgP70yFG3Ptu2LBB81AFLUePHg1aiv6sXbtWc1QFLbk5qniNhy9GNGAePnzYhGAEFZ91dXWYURqE/P7nYB9kMJvQhSgBcA1e42GLMRctqlxc0OLeiEEW3ExxMNRcUaOgxbkGzsFrPFwxur1ZihYFLXfv3rXGxsZUiXH58uX2448/mhCs5KLGcMXoFhCrTFzQ4l7fqbhCnUSOWgwuaHHO8VFjeGJEBRx+cAjBiEv0ozw8VdFi7qCtRIM2BDXOPYgawxOj+8RUHi5oaWtrw1qpIESXz9m+fbsJwUouMAtLjC4BqmhR0OI2VqDCM5XXqJG+Rm24EdTkgrNwxOiSn4oWBS1Xr161devWBbFBI98zd+5c++CDD0wIVnJRYxhiHB4eNiQ+hWAFjfILFiwIQnCFnIULF2rTjaCmqanJBgYGkhfjxYsX7fLlyyYEI/fu3bNXXnkliEXExbhO/fbbb00IVuAi7GtMXIwYyYO9dEIwcvz4ceQXgxBbMQ76MIVgBS5CkRxaN5ISI/pHtFpK0PLkyROrrKwMahFxMcbEIQoWghU4CfnGxMTY3t5u2IklBCOXLl2y9evXByG0Yp633nrLhGAFTjp16lQyYhwfHzeErChlF4INXMU0NDRQ5BYnK8JBNCwEI3AShlqg0Cx2MWLKAKwsBCNIE7z66qs2f/78IGRW7A3/165dMyFYgZuuX78evxhbWlrUuyho6e7uRsQYhMhKcXbs2GFCsAI3IdcYpxhxDYNrVK2zEbSsXr2aMlrMHXxtuk4VtMBNcBRe47GJEZNAzp49a0Iw4gaGo3eRqhp1surU3t5eE4IVOKqnpyc+MW7ZssXww0MIRlw1KqbdBCGwUp69e/eaEKzAUXBVLGIcGhoy/GVCsOJaNChGwM3gOlVV5YIajCuFs0ouRlyjYgycEIyMjIxgmlMq9y7mc52Kyj0hWIGr4KySi/Ho0aOanCFoQU5i8+bNQYgrjnPgwAETghW4CtufSirGFy9eGAYRo7lfCEb27NmDBviSiejQoUOGbfqPHj2y58+fm+eff/4x5ETOnz8fpxhxZaz3s6AFr204C+4qmRixkHjfvn0mBCMo7UbvYqmGhvf399sMwO+LTYxVVVUGUQvBCpwFd5VKjFjpofyioAUzFt94442SCAiRYJSHDx/anTt3cCar8MbHY5PjiRMnTAhWvLdKJUbc1Sq/KGjp6Oiw+vr6ksjn9OnTuC5FdIZWkMn+ffRqFb+OrWUEX7MQrBRy01mm/KLIOmhDmjdvXmKFMBMb7q9cuRJb28bY2JgJwYh3F4aKF1uMiBSVXxS04E2D3GLCS4kTuU6tqanR3GNBDW478RovthhxR6v8oqDFvWmQX0T0lDkxIko+d+6cCcEKehmxGKDYYkS0qPyioMU99AWxlDiJq1Sc119/3YRgJd/xcGXT9IKgt0v5RUELrloSHho+sXIVPY/4eGxRI3IxQjCCbRvIM842l16m/KLIKnjTYATcokWLkhQjnmqjDf/4WJz9jFoMIKjB1La+vr7iidHfzwrBCIYMY1s/niiTkiKiwwioUI3178eDwQ8//GBCsIJ+xgsXLhRPjO3t7YbmZyEYgRCQY0syWsSIOA9+ncTn8M4775gQrPz+++/W2tpaPDFifiSaJIVgpK2tzerq6pKSIpr+o439aPZP5PNYsWKFCcGKX5lYLDHiDaMGYEHL9u3brby8PAkZofJ04hWq9jMKUZpGfxSZoaagYDHijaInSUENcosJ7F/0Y+Cm6VuMtwBncHDQhGClqakJO1cLF+PAwIDhKlUIRnATgge/5cuXJylFVIQm3kNZXV1tyMMIwQoqUzHMo2AxougGxTdCMAIhbdy4EX26cVagBidFv5vxu+++MyFYQXcFXuMFixHlrWjXEIIRFL74VVMxHGzMwN7H4KToz8cff2xCsIIcfmdnZ+FiPH78uK5XBC09PT0YBZeEFPHrIGQYPQcPHjQhWLl//741NzcXLkaUtw4PD5sQjCBNgK39MfcqQooQZRAyjJ7Vq1ebEKygpqC2trYgMaKsFW8WzVAUtODpMY78IipOo/T39+Nj0x0/LzXWmalCMON3M+YtRkSKGzZsMCFYWblypVVUVMQSLeZB3C0c2EepnmVBDboskNvPW4woa0WOUQhGcBOCaDGOVg1EiHkQe8M/rpnQoiUEK5h0haK7vMV48+ZN6+joMCEYcTciaPjF1UoQ+b0QDiJG/NAQghVUpaINMW8xXr9+XWLMKFh7hKHS/gfmX3/9ZWxg/u+2bdswDi4IKYVw5syZY6jUzSpuLREmAGmwOjHYsoGTtxj9HyCyxR9//OF/OPhDeb3mUgXYqhGEkEI6X331lWWRv//++6XXPY7gQmIUs40So5sd6MWIVMGuXbuCkFFI58yZM5ZFPv30U3z9EiM5WDN39uzZ/MXY1dWlPYwZAeKbcIWE6i16Mb722mtByCikg+KErIHXt//6v/nmG4mRGF87k7cY8R/jDxHc4AoJPwCiPxgckCO1GN3MRAywCEJGIZ2TJ09a1sg9BOLhEDcnEiMxBYvx1KlTGgeXAXJPy/jh4ItsMiFGlyYIcvpM0ufNN9+0LIGCG/+149cOiZEYjIU7fPhw3mLEf6zN/Rnhl19+sRyZEqNr8A9CRiGd3bt3W1Zw0aFPIfj1ehIjOc5pEqOYMZkTo8uhYzZoEDIK6eB6OSsgbRB5jUuMGQDLuPEQlLcY0fw8OjpqIpuwi9HlGdDQHoSMQjp1dXWWBaK5dVSkOiTGDOCcBrdJjEJinEKMmnqTYTG61iRfcANJSowZoWAx4ofG+Pi4iWzCLsaWlhZbvHhxEDIK6WRhw8bE9gyHxJgR/IzkfMWoF0XGYRejy6EHIaIQDzu55dT4JwpwJMaMgf+v+YoRVlXEmGHYxegixiAkFNphjxhRgT2hPUNizBC5iFE5RiExTpVjXLRoURAyCukw5xh9e0ZkQLjEmDFUfCMkRhXfSIwRvvzyy+jGGIkxgxQsxubmZvUxZhh2MaKPsbKyMggZhXQ2b95srODr87lFFN1MdSaOSPRHpJ9cH6Ma/IXEONXkm4aGhiBkFNJhnXzjK1FneDKzZSZrFDz5prW1VbNSM0wWxKiRcNmZlRrJL+Z90O8o0k1OjNquISTGKbZrINcQhIxCOnggzjrKMfJS8HaNzs5O7WPMMOxidG8Q5NOCkFFIJ4v7GCXG7JATozb4i5n1dk0sQECBQnSWZPTf4fenHYlRG/wlxuyR2+AvMYppyeR0lNu3b9u2bduCkFFI54svvrCsw/Q6F5N6LX8x4hoV16mCn9kWJeD3px2XhNcG/0lOT0+PZR33+qZ5nYsii9HfxQrByPDwsGGDf3l5eRBCCuFgEtCtW7dMCFZ87Uy+YkSrhirUBC2YA1xRUaEm/8ipqamhLLQSwtPe3o6gL28x4ok6U9u8Rfaor6/XsuLIwffiyZMnJgQrmHrz559/5i9GTCHHm+Xff/81IRjZu3evLViwIAgpabOGEKVnxYoV9vTp0/zFCDZs2GCIHIVgBNcquE6NSzyHDh0y5PAePXpkz58/t+hUlocPH9r58+cTFWNjY6MJwcrY2JjV1tYaKEiMx48fN5S1C8HIpUuXkFeLRTr9/f02A3DNk5gY9+/fb0Kwgkr0ffv2FS7G7u5uu3r1qgnBCKK3OMbCIRKMgojxzp07OBChjx49+HgiYvzwww9NCFZQjYpOi0LFiD9ILRuCFlRgrlu3LhYx4rq0t7cXLSIT/z0+Fi16gSiTyC/qIVhQM9tAr2y60FMIRpBzWL58uS1cuDDxwpfTp09bhNjzjShK6OvrMyFY8anBgsWIHxx4wwjBiiu+gRyDqApNWoxY4ioEK76YtGAxgqVLlxrKW4VgZOvWrbZkyZLgxIgIMsa/G1Gz3ueClnzaD8umaYjUNAxBy6lTp4KQIvKPkfaN2P9+PAALwUpkYE1RxIj9bNrLKGjBGpq6urrE84vRytQrV67E/jm0tLSYEKxgxClyjEUTI6btX7hwwYRgBO0Sa9asiUM+qD5F7tAfRIkTx1PhY3FLEXlWVaQKavD6hseKJkZU8Rw+fNiEYAQ5B4yFiyPPCAlOASbf+Lxi3AfFR0qXCGqwEAPDw4smxmfPnhkS80heCsHIgQMHsHKp5AJC4/40YEIOIsu4exj1/hbUoOoavcJFE6MvwEFPoxCM4IolgWHi/mp1YiSJN3Cscty8ebMJwQreX77wpqhivHjxomHrsRCMuHQBRsMFVYAT18xULGo+e/asCcEK8otdXV3FF+O9e/eUZxS0oH8Puwjnz5+fpBwnXrUiaowlv4iKPSFYOXr0KF7jRRcjtp2jz0l5CEELIka8xhMUI9ZSxT39BvUDWk4saEFxHd7XqJUpuhgBZqYichSCEbebEUUoQU2/iUOM1dXVJgQrqI3ZuXOngZKIEbvrkGsUghHXP4glpkENEo+jfePdd981IVhBbQy8VbAYtWlDZJHHjx9bVVVVkmJEwU2sq6eQX7x+/boJwQpqY3DTWTIxIr+Iu1rkG4VgZPv27aXIM2L2KaT3fxEglibHvawYLSoaHC5ogbPQv5ivs8pmYV/lGQUtuHYpxW7GCF6S0Q3++NjEPsY4IlTdAAlqCu2mKJvFDw7lGQUtQ0NDVlNTk8TEm+houDjaNBAZ6xpVUOP770suRsxTxHWTEKxAShgPV4JWDFyXQnyTRYgYBRfnrFT0bKpNQ1ADV8FZJRej34KMJ2shGMmNhwtiR2Mpz44dO0wIVuAouArEIkasoeru7jYhGHFPmNjPGIS8StnUjz2UQrACR+EaNTYxjo6O2sqVKw0TBYRgxPUzYkdhEBLTtBshZgXcBEehBSs+MYLm5mZVpwpaPvrooyCm4GibhhB5VaP6iut4xYhqNozQEoKR+/fvW319fRASK0XRzddff21CsNLW1oZJVrGLEQ2TaJxUs7+gxF3FYCoMZdSIa1SkQ4RgxC+8wOCKuMXoraw+KEHLZ599hraNIGRWzHPgwAETghU4qbW11UAiYrx79652NApaXFRFJ0Y8SeN9KwQrcBJ2LyYlRl/5o+o2Qcvbb79NVZ3q2lBUTS5ogYuKuTe4rJBeEfQ1CsGIi66QawxCakXILeq9KqjB67uzs9NAomLEdIEtW7aYEIzkbkUoJuE4MWqThqAGLsJ6xMTFCLAd+fbt2yYEI3gKXbJkSRByy/eUl5fbyZMnTQhWnIP8CLgwxIhPCHIUgpGxsTFEW0EILt9TWVlpWG8lBCtw0M2bN8MRo/+kEMIKwYiLtlItx02bNpkQrDj3IFpE6iMsMbqoUa0bghY3CQcDLYKQXB7RovqNBTXOPT5aDEuMztQwtqJGQYvb7ZbKIpyqqipD+boQjESixfDE6ICxFTUKWtytSOpaN9DT9f3335sQrDjn+BuRMMXoo8bBwUETgpFt27ZhCHcQ0pvBgcgVLQpanGvQToXXeLhiBD///LNhTp0QjLhrG0RhQUhvurN48WL79ddfTQhWnGv8wu2wxQhzw+Bo/BeCEbeLNBUzVBsbGzX+TdDiHOOjxfDF6IDBFTUKWlzUiGgsCPn9X24ROX8hWIlEi+kQo48ah4eHTQhGjh07FnRf47p160wIViLRYnrECGDylpYWE4IR98YMNmp0n5dGNApqnFv8hv50iRG5DfR99fX1mRCMvP/++0HKEZWzQrDinAK3wDHpEyMYGBiwNWvW2Pj4uAnBxuPHj4MToysK0pANQQtcAqfALY50ihG0t7fbxYsXTQhGzp07F0zTPzZoHDlyxIRgBS7p6OgwkGoxYv9bbW2tjYyMmBBs4Am2oaHB5syZE0RuERvMhWAEDoFL4BSQajH6pn/0fgnBCLb8oz0i6SvUa9eumRCswCFwiSf1YkSSdM+ePXbr1i0TghGspUpymfHWrVtNCFbgDjgELvGkXowAS1KRNH327JkJwQaud7DeKQEpop9SqQpBC5wBd0ycwU0hRtDV1WXd3d0mBCOYNLNs2bLYpfj555+bEKzAGXDHS7CI0ZlfE3EENfv37491Z+OqVau0PUPQAlfAGXDHS7CI0YG9Wbgr1nBjQQmuNHGlGlMVqnoWBS2+NmXKmb9MYswNf9WVqqDlypUrJb9SxU7I06dPmxCswBFtbW02JWxiRFjc1NRkKHMXgg086e7evbuUC41xvaSJUoIWzPrF0vv/e43TiTGyeVkNyYISvK4RNZboClX7TgUto6Ojwez0jVuMfgMHmjaVbxSU3Lt3D72NRZfiTz/9ZEIw4vOKM2rkZxVjLt+oWaqClgsXLhStGKeiokILwAU1Pq8YComJ0ecb8XQtBBv+CRhSK1CMeJ8oryho+Y+9u+WNaovCOE4AgUAA6iYESYJAIBCVCERFZSUhCAQCSQKCBIEgpLI4gkIgkEgkAskHQCCRfIRzzy85O5nbO6XQzkz33uf5JxNe+zazZj1rr7ddS12xCmGErThyyj9//hxC6A31xhPewCElm/nf0C011RWrEUa8evXKrcypN4YukRE57t2NFpS/e/duCKFHaqsrViWM2N7eHl68eDGE0CPqjTpV//bWjPv37w8h9EptdcXqhNEiZnWUjx8/DiH0hsh4a2vLCfCPm22uX7+elW+hW5wSb9y4UVVdsTphnFZqyTVn1VXoEg7g5s2bR+5TPXfu3HD16tXU3UO3jAcgt2ZUPctejTDiy5cvA3G0BCCE3iB2165dG86ePXuoMF65ciVXSYVuGX282/irt/GqhHG6FV00Uf0TF8Jxu/AuX768TBTNPaYDNXRL2XpGHGunOmGc7rcz15J0UugS4kcED4xlNOEwQjhuqcyBx8xiC1QpjNMYh5pMtcXZEE4AESzNOH5N41noFgcczZUOPK1QrTBO7bx2qqY7L3TJmzdvBifF169fDyH0iA1nuk/39vaGlqhaGLG7u+uRBQChK8ZMiOFmN/GLplM2CN0x2riDTZN38FYvjATxzp07w6NHj4YQemDRYbDvp0+fmnNM2SB0A7ve2dmpdoC/eWGcHAnHQRxzcgxNw5bdlHEwC/L48WNiGXEMzTPZeNOZviaEcSHK9mSn5hiaxIYnNnxIgGd1nIazpFVDs0w27qTYrCg2JYzT8ZwwJrIOzWHLhyaE58+fH3WJd+Z4Q5NMNt5kTbFpYZzEUdopkXVohjLDRfT+cGVWM4PQIaCs9GTjPdCcME64jcMSgETWoWqI26VLl/56TvHz588cTTMD0WG+FBt///790AvNCiP29/dF4tmtGqqEGJ5gMT67Zt9V3lcXAoqNu3e0J5oWxumFEa3kVo5QFcRsBUGbjIjMSO4rDdUx2XiXKf/mhXHhKJ+1WqEKVp3mV0u/detWxpVCNYw2bjFFt6WsLoQR3759E714weI8wmmgU1qbemkMW8u40vb29qAlPoQDNG/jNdGNMEK7MOdx9+7drl+0UB8CM6fEBw8ecB5r68h+9uxZt+mrUDcLNt79uFxXwojiPBSEdfaFgDXXWly+urHbAzQ6sG830ITQo43/lgjjiZxHUqthU2mlTV8wLDuS1Gro2sYPJcJ44qYFadWkVkOXaaWkVkPvNr6MCONqnIdTI+eR1GroMq2U1Gro3cb/Q4RxZRDFpFbDStJKRie+f/8+1MSUHUlqNXRr4wMQYVyL83C/46xf9PD3fP36VVrJLGG1t7sI+Kb5su42kYTYOBBhXI/zsOhW6slNB7M3gHD0NToPHz6UbWhmgYQtUG45cCfejx8/hhB6s/EI4/q6+qQMREiJrsNS3r59q86iwaW55gMBoJqj7//ly5cJAMMyG1FLbNbGI4zrrT0mug7/W969tbVlHKL5lVcJAMNhadPbt28rLTVv4xHG9UVOomr7VhNdz5hfv365ZFVKqasrdBIAhmVp0w8fPgwhwngUHAbHwYFktGNmEELOwiXYBLJHpvRqAsAZspg2ffLkSdKmEca/59OnTzr7pKDSvdo54xCzdJLU6Wzu9ZwCQOnVXIQ8A8ZmLGlTpYFZba+JMK4e0bTWdxGWzQ8RyM4giMRBd7Immzky1hzNq3lEIDtkFESjaQKg7koDEcZTRlqtCOTu7u7AoYZ24SwMwXMW+/v7SScOA1EkjvZh2nSSBRiNM76eMiBsPK9nhHH9Arm3tzc4YThp6OoK7TCejkTPcRa/b9Apz5FTdIKGhmDPZhAXMgCx8Qjj5uAsyoIATsQJJNTLdBry4DjiLP78VM3G2XoEsmJGexboCWacEpMSjzCeKpyFqDoGWSGTs5AaTP1sNXVY5YRuu3VbZArQNQkmQI8w1sdixGbMQzu8oeqwcTRIWfNn7IKzyMjNatDJqAHNmIcZuDjh00PntFlbPQ8p6UQY24DDmByIVJROsKShsN7ar/ksp3YnG04jDVLrQcCnzu4k7qQiCEm39maedwF3ed6d3vO8RxjbgxgSReI4Rdm5SHa1p3SzprqEPb/mTf059cPNIfgQhAhGzMcpKyTVuh4f4nQYHwIgwtg+U5St1iXdauNIUq3Hd8a2dsQZV8QUpAhOEqSsKOtEBD2XOzs71rYl63SACGNnLETZtq0onift9/uomaOQOhJYJI1UOWOQIlgRtLBx6/U0PiV4OeJiYKNEbrdg3+ycX3B3bDiMCGOXjNG0NwOR9EYQHSqkz14oPS9FCDXPXLx4Ue2Q00gaqTHGtXPsmV2zb3bO3mcvlAtCyLZLP4IaYta1VUSE8fThKDgMN2frbJ2NUI5CqKuOU+AcCCEHymlwHll03BEHXufZCOUBIfSzC/oEf4LApJwrJcJYIWMaRW2BUEqvEEqNJhwLRyKybO4NxUEQeT8XpzA5SJ12s3CQYXlARCQuXLhQhFKHMcFoMpXoSic/l3SyWjgh9LP5dRLC1AsbIcLYANJSZiW92RTknSrPnDkz+NWf/T2HIt3ozXmacGgiZHtI1ZgIoLnC8+fPE0EnYdEzgWzS+YX11ZIJpXGnkmL0cCvEvXv3/JvNRYLC0xQXou5iX4GcpjqNMr5fnaMCPd+vBiRNdsl6NEyEsVG8QTkJb9AlDsXvORSnTtGqB7EisB4ckYdTHOH18DmLoyp/Z6DY/zMsXz5Wird8To6BI+AQOIbSMerrchw6FHNDeDj2CUywV05g5a5UQZYOb0Ehu/dvxR6LjXpfFBvXrMWWBWKAz11svPw/H1M+nrD5fII4X2PJ1y4nXO+NBHmdEWHskMmheIMTw+I0iJU3OcGUwvIo3Z4e3vROotI/5e8Inv+ni9bHenAI5XNyWuatCGhSoWFjCLYEXWxcYMgWLRxgn0XIio0TMrb8z7/t2UEGACAQQNFrzP1Pmk9zhYj3aJfaDF+aab5bvfB2xnffhrZVEDuzQHbHt18YCCMACCMACCMACCMACCMAXMIIAM8drs3GuUtFBMgAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Image('../images/MySQL-INTERSECT.png')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d0f0082-9eaf-4117-b9a5-148e895d5603",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Setting up sample tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "3cbb61fa-b42b-4855-a21f-56e0f6062d8b",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_13332/120258163.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 0x7f963c1bc7c0>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.execute('DROP TABLE IF EXISTS t1, t2')\n",
    "\n",
    "string = '''\n",
    "CREATE TABLE t1 (\n",
    "    id INT PRIMARY KEY\n",
    "); '''\n",
    "\n",
    "engine.execute(string)\n",
    "\n",
    "string = '''\n",
    "CREATE TABLE t2 (\n",
    "    id INT PRIMARY KEY\n",
    "); '''\n",
    "\n",
    "engine.execute(string)\n",
    "\n",
    "engine.execute('INSERT INTO t1 VALUES (1),(2),(3);')\n",
    "engine.execute('INSERT INTO t2 VALUES (2),(3),(4);')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "854b1fe9-f5fe-4842-9216-5818e1b62606",
   "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>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   2\n",
       "1   3"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string = '''\n",
    "SELECT id FROM t1\n",
    "INTERSECT\n",
    "SELECT id FROM t2\n",
    ";'''\n",
    "\n",
    "pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "caa24201-d3e2-4165-afd4-c3189eb96d00",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Emulating INTERSECT in MySQL"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8ca940c-e417-45b8-ac3d-fcda57c70ddd",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### 1) Emulate INTERSECT using DISTINCT and INNER JOIN clause"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "eead424d-9cea-4756-b7f2-799e6f5e2cf5",
   "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>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   2\n",
       "1   3"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string = '''\n",
    "SELECT #DISTINCT\n",
    "    id\n",
    "FROM t1 INNER JOIN t2 USING(id)\n",
    ";'''\n",
    "\n",
    "pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f7a9f0d3-02f9-4a27-a90b-488f80edad50",
   "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>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   2\n",
       "1   3"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(pd.read_sql_table('t1', engine)\n",
    " .merge(pd.read_sql_table('t2', engine), on='id', how='inner')\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3333ebc-c9aa-4911-a92d-4f203be023a6",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### 2) Emulate INTERSECT using IN and subquery"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a7a3663e-428e-4226-a84a-44f17de0fb49",
   "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>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   2\n",
       "1   3"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string = '''\n",
    "SELECT #DISTINCT\n",
    "    id\n",
    "FROM t1\n",
    "WHERE id IN (SELECT id FROM t2)\n",
    ";'''\n",
    "\n",
    "pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "49d583f4-01cb-4e3a-a100-c4ddb6f70a21",
   "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>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "id_t2 = pd.read_sql_table('t2', engine).id.values\n",
    "\n",
    "(pd.read_sql_table('t1', engine)\n",
    " .query('id in @id_t2')\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "58c2a98e-7a8b-4463-b4c9-68a213a4ee60",
   "metadata": {},
   "source": [
    "Drop the tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "591a91d1-2c50-46a8-8da4-8e868afd6be3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f963f22efd0>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.execute('DROP TABLE IF EXISTS t1, t2;')"
   ]
  }
 ],
 "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
}