Section 08 Set Operators/30 Emulate the MINUS operator by JOIN or EXCEPT.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "892267f4-ffd4-45c6-acb4-7d72c37cffcb",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## **[Emulate the MINUS operator by JOIN or EXCEPT](https://www.mysqltutorial.org/mysql-minus/)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b44e8bbe-8b39-4a3b-be4a-7b7fbf7f95b7",
   "metadata": {},
   "source": [
    "MySQL does not support MINUS operator. This tutorial shows you to how to emulate the MINUS operator in MySQL using join clause"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "83b1db24-7c1d-4527-90f3-fc482026a570",
   "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": "17b3ccfb-3eab-450b-94b7-4db3b48633b6",
   "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": "ea108da7-b15a-4111-9717-5341cf674028",
   "metadata": {},
   "source": [
    "Create two tables:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c6dd55c7-9030-422f-b23e-eaab1b6d0ef9",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_13200/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 0x7f10fc96af10>"
      ]
     },
     "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": "e9611150-e1ec-4a72-a30e-745c4b4cdba2",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Introduction to SQL MINUS operator"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c433a7f8-be65-494f-8e0a-cefbd5797b65",
   "metadata": {},
   "source": [
    "syntax:"
   ]
  },
  {
   "cell_type": "raw",
   "id": "42795be3-27bb-46eb-bcf1-2ffb134c6c51",
   "metadata": {},
   "source": [
    "SELECT select_list1 \n",
    "FROM table_name1\n",
    "MINUS \n",
    "SELECT select_list2 \n",
    "FROM table_name2;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5077b28e-9e69-40f4-a588-891f74e6862c",
   "metadata": {},
   "source": [
    "MySQL does not support MINUS operator:"
   ]
  },
  {
   "cell_type": "raw",
   "id": "ee577693-ca29-4fa8-b1e8-68c5edacab0b",
   "metadata": {},
   "source": [
    "# ProgrammingError\n",
    "# Returns distinct values from the query of the t1 table that is not found\n",
    "# in the result of the query of the t2 table\n",
    "string = '''\n",
    "SELECT id FROM t1\n",
    "MINUS\n",
    "SELECT id FROM t2\n",
    ";'''\n",
    "\n",
    "engine.execute(string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4e0955d2-e2dd-4942-a810-f09ebd874823",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAgYAAACxCAMAAACIsYV2AAAAq1BMVEUAAAAMDAwREREjIyM0NDRAQEBHR0dQUFBUVFRXV1dgYGBpaWl6enp8fHx/f3+JiYmYmJifn5+lpaWnp6ewsLC1tbW7u7u+vr6/v7/BwcHDw8PExMTGxsbHx8fIyMjJycnMzMzNzc3Ozs7Q0NDS0tLT09PV1dXW1tbd3d3f39/j4+Pn5+fr6+vs7Ozv7+/w8PDx8fHy8vLz8/P09PT19fX4+Pj7+/v9/f3///98UoMEAAAGbklEQVR4XuzSMQEAAAzDoMqKf2UzsRM0sKD1Ag3QAA3QAA3QAA3QAA3QAA3QAA3QAA3Q4Nizw9QIYSCK43cIIoCFBtgtNYo0VIf//U/WpQrsAcJIhvcAGT/PD15CUtt8cWN+UttwUz5S03z6M/jmxvwGYfBITfMQAzEQAzEQAzEQgzYRAzEQAzEQg40chsFEDcFgIzsxMBvPofbPYGI/h9w3g43n+07EQAy8S6EGKoUcoBR8GRhTSoux7BFKwV6fnaP0XgrztRM3BpBTAaB/BhnSYAB9M6iUayeuDIySssVgMHOMw9o9g2snrgz+/2oMBoXyGrpncO3En4EYiMEcphQKxxChFGD2LoU10BFxJMYRcQXA9cI4rNhSglwYnwd7tt4vjO870dOSnpbEQAzEQAzEQAzEQAzEQAzEQAya5q+du9ttpQajMLzIFz52cKiLrAhqQSVoBD4JVFSUdf9XhuqkO2k7uzTDJJOZWe9JUh80J4/s+fN8hMEP6y77Zc0e++Nm3WU37Kmbdaf99AEGa3bZQ2FX9f/rhT21PtG/EwMxEAMxEIP2iYEYiIEYiIEYiIEYiEFGGAuDBMDHwKDAz8wgYDQMCsiCMHwGEedmkFBsLAxqwQbPICP7WRnUxsUgDp1B7X8yEAMLYiAGCXnyDMSgIHLyDMQAzt4Z/PzYLwMxMGf/DK4+PfbJQAzceAkMvqoOBsUg4SkbBYOMWuybwXefqoPWDDJqg76noHsKV1eP1cHEby2JAauDqTMQg+pg8gzEoDqYPAMxqA4mz0AMqoOxMxCDH7/Yt1fcO+iRwRk2r2nzGr7+Yt+znYPbdaf9evrZQLMB+G57B/3WPwMxqA4mz0AMqoOpMhCDZgdikIFCcgNEMmIX6/cVSTrIjMinYv3cxDlgyzJEBs0OxOAauK4fDQy8kcFmjpoPkUGzAzGICHOS8wViHchw1iIMpYlBwPyOLNdhmAyaHYhBxh3vUN4yiLPQxMCRh39ssHdw6QwAoJyBAW3JpRP+hkHAppHBstXmNbs8BtXBZTPwRDrOwSAaLTUxKIgNDFaA5yMZgCTCJTL45q/LXxQy8qkZBLAgYdPEgG4NDLgywNLmDJvXtCjUyukZOMg5AhsZrJAaGJDJgVk+koE7W6VDxAieg0HCqpkBzRsZkGWBWTmKQUJiq3TCiHAWBjVDA4Nr3G0ZLF8xIBeIH2eQ2z+XrMtHbjwfA29isJmFLYM5yZfnissjGNTM2CJdTA4op2cwt/cYMMzmIDlDKNwEGElPhdykGVbHMShtVgXdWoooPD0D+AGDiF2fGZTtHwnbViSxK/D0DHSjOSGzfwb0yoB5AcwWd/VrMMAWqyMQJm+5xOmxExvR5jV651cRu38ITc8b6JFUMdAD6mKg7SpioM1rYqCtrGKgje1ioNdc9J82r7XvppOX3vSfZoP+X4E1eQZi0L8CMbiI12OKgRj0lhh0lRiIgRiIgRiIgRgAQB4PA0Pi0YlBSGTAaBhEF4O+N6/1z6CgtGUgBgllJAw8sC0DMYBzHAwS2I6BGBhgHAkDS2Lwf7ay5lEwiM72DMSA7qNg4KjZpBmIAdlyNhADFDIhT5uBGKTRXEUUA91T6CoxEAMxEAMxEAMxEAMxEAMxEAMxyKXLfv+NPXZ/W7rsdkIM/nzotL/ZY//cP3TZ/UgZKAX2mxIDJQZKDJQYKDFQYqDEQImBEgMlBkoMlBgoMVBioMRAiYESAyUGSgyUGCgxUGKQwG3ROPiCc5sHMWjOUcbCwAGkoxiIQUYhWYDPDBIGyyA4yRjIiLwf+SgDMbCQRsOghngEAzEI2M2gnxk4AGRGAKkOAyhbBrvBiw1PsWbxcMQBFDJ4AuyZgQHgYZoNyFezQQzboQSQAVsGEWRBGsBskF8sCp7IYGSAk+ZbBhbICL6TGNQK8tZEQaoMLJEMPgAGbq9HEqqJLW4PzPjA2/PEIAB4ZkCLjMaC2gAYRJQXIwagMtjh9sCI2rsMxCCALG8Y5GEcIiakFyPmZHrNwPifiYHFhkUBcRAMMtKLkYL8dlFIKGKwryDvGexhuJNWGYAM9nyIWMiQeLFFq2jjqxFEFuD5EDHsDhGdJMRgVwAy7XChdKAQQERmsgyAByeMkRccYAyolf1IBhBBhhABPzxhLGLQlBIDJQZKDJQYKDFQYqDEQImBEgMlBkoMlBgoMVBioNS/Jk1rNSn/XRoAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Image('../images/MySQL-MINUS-Example.png')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43bdbb00-6741-4a6e-9e95-aecb1ceb3e71",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL MINUS operator emulation"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7cf2b2b2-e204-41a6-8a23-9ae85e739881",
   "metadata": {},
   "source": [
    "You can use MySQL JOIN to emulate MySQL MINUS:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "62c5b562-f80d-4076-a17a-78ffa3cbcc5f",
   "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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string = '''\n",
    "SELECT id FROM t1\n",
    "LEFT JOIN t2 USING (id)\n",
    "WHERE t2.id IS NULL\n",
    ";'''\n",
    "\n",
    "pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "023cefd1-e9ae-427b-8868-fe6d470175dc",
   "metadata": {},
   "source": [
    "Or you can use EXCEPT instead:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "297c7849-abda-47b4-a150-4d933965d7f1",
   "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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string = '''\n",
    "SELECT id FROM t1\n",
    "EXCEPT\n",
    "SELECT id FROM t2\n",
    ";'''\n",
    "\n",
    "pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7b51a34e-8b11-4fbe-a846-be7476bb8b1f",
   "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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "id_t2 = pd.read_sql_table('t2', engine).id\n",
    "(\n",
    "    pd.read_sql_table('t1', engine)\n",
    "    .query('id not in @id_t2')\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "82e23647-b2d2-4e8d-86fe-d3c048c8ea09",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f10fc7e7580>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.execute('DROP TABLE IF EXISTS t1,t2;')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "20c57ff9-5720-4c2e-8de8-3e8cf6427ebf",
   "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
}