DemoOnJoins.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Demonstrating Different Join Operations using Embedded SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "#refenrence: https://www.tutorialspoint.com/python/python_database_access.htm\n",
    "#reference: https://www.dofactory.com/sql/full-outer-join\n",
    "#reference: https://www.w3schools.com/sql/sql_join_inner.asp (for images too)\n",
    "#code written by Shivji Bhagat @shv07\n",
    "\n",
    "#!/usr/bin/python\n",
    "#!pip3 install --user pymysql\n",
    "import pymysql    #Alternative to MySQLdb, both have almost same functions\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "#to display all the outputs of a code cell instead of just the last\n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>\n",
       ".output {\n",
       "    flex-direction: row;\n",
       "}\n",
       "</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#to print tables side by side in output\n",
    "#refernece: https://stackoverflow.com/questions/38783027/jupyter-notebook-display-two-pandas-tables-side-by-side\n",
    "from IPython.display import display, HTML\n",
    "CSS = \"\"\"\n",
    ".output {\n",
    "    flex-direction: row;\n",
    "}\n",
    "\"\"\"\n",
    "\n",
    "HTML('<style>{}</style>'.format(CSS))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "#miscellaneous functions\n",
    "def show_table(table_name): #returns pandas dataframe cooresponding to the table\n",
    "    sql='select * from '+table_name\n",
    "    cursor.execute(sql)\n",
    "    table=np.array(cursor.fetchall())\n",
    "    columns_=[]           #to store the names of the columns of the resulting table\n",
    "    for i in cursor.description:\n",
    "        columns_.append(i[0])\n",
    "    x=pd.DataFrame(table, columns=columns_) \n",
    "    return x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "#connects to the database - PublicationManagementSystem\n",
    "#user_name - root\n",
    "#password - god\n",
    "db = pymysql.connect(\"localhost\",\"root\",\"god\",\"PublicationManagementSystem\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = db.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## JOINS\n",
    "Joins are the operations which are performed to obtain a certain combination of two or more tables. </br>\n",
    "They are of following types:-<br>\n",
    "1 ) Inner Join <br>\n",
    "2 ) Left Outer Join <br>\n",
    "3 ) Right Outer Join <br>\n",
    "4 ) Full Outer Join <br>\n",
    "The result of the above can be modified by using the clauses : Natural, On and Using. <br><br><br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## INNER JOIN\n",
    "Returns the rows from the participating tables which have the same entry for the columns  based on which we are joining the tables. The resultant table has all the columns of the participating tables even if they have the same name.<br><br>\n",
    "<img src=\"https://www.w3schools.com/sql/img_innerjoin.gif\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Shown below are the tables on which we will perform inner join</br> operations on the condition : Author.AuthCode=Writes.AuthCode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>AuthCode</th>\n",
       "      <th>AuthName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11</td>\n",
       "      <td>JK Rowling</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>22</td>\n",
       "      <td>Charles Dickens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>33</td>\n",
       "      <td>Stan Lee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>44</td>\n",
       "      <td>William Shakespeare</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>55</td>\n",
       "      <td>Chetan Bhagat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>66</td>\n",
       "      <td>Premchand</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>77</td>\n",
       "      <td>EL James</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>88</td>\n",
       "      <td>Stephen Hawking</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  AuthCode             AuthName\n",
       "0       11           JK Rowling\n",
       "1       22      Charles Dickens\n",
       "2       33             Stan Lee\n",
       "3       44  William Shakespeare\n",
       "4       55        Chetan Bhagat\n",
       "5       66            Premchand\n",
       "6       77             EL James\n",
       "7       88      Stephen Hawking"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>AuthCode</th>\n",
       "      <th>BookCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>22</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>66</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "      <td>111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>88</td>\n",
       "      <td>202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22</td>\n",
       "      <td>222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>55</td>\n",
       "      <td>333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>33</td>\n",
       "      <td>444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>44</td>\n",
       "      <td>444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>44</td>\n",
       "      <td>555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>55</td>\n",
       "      <td>555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>55</td>\n",
       "      <td>666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>77</td>\n",
       "      <td>777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>77</td>\n",
       "      <td>888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>11</td>\n",
       "      <td>999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    AuthCode  BookCode\n",
       "0         22       101\n",
       "1         66       101\n",
       "2         11       111\n",
       "3         88       202\n",
       "4         22       222\n",
       "5         55       333\n",
       "6         33       444\n",
       "7         44       444\n",
       "8         44       555\n",
       "9         55       555\n",
       "10        55       666\n",
       "11        77       777\n",
       "12        77       888\n",
       "13        11       999"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "show_table(\"Author\")\n",
    "show_table('Writes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>AuthCode</th>\n",
       "      <th>AuthName</th>\n",
       "      <th>AuthCode</th>\n",
       "      <th>BookCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11</td>\n",
       "      <td>JK Rowling</td>\n",
       "      <td>11</td>\n",
       "      <td>111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>JK Rowling</td>\n",
       "      <td>11</td>\n",
       "      <td>999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>22</td>\n",
       "      <td>Charles Dickens</td>\n",
       "      <td>22</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22</td>\n",
       "      <td>Charles Dickens</td>\n",
       "      <td>22</td>\n",
       "      <td>222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>33</td>\n",
       "      <td>Stan Lee</td>\n",
       "      <td>33</td>\n",
       "      <td>444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>44</td>\n",
       "      <td>William Shakespeare</td>\n",
       "      <td>44</td>\n",
       "      <td>444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>44</td>\n",
       "      <td>William Shakespeare</td>\n",
       "      <td>44</td>\n",
       "      <td>555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>55</td>\n",
       "      <td>Chetan Bhagat</td>\n",
       "      <td>55</td>\n",
       "      <td>333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>55</td>\n",
       "      <td>Chetan Bhagat</td>\n",
       "      <td>55</td>\n",
       "      <td>555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>55</td>\n",
       "      <td>Chetan Bhagat</td>\n",
       "      <td>55</td>\n",
       "      <td>666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>66</td>\n",
       "      <td>Premchand</td>\n",
       "      <td>66</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>77</td>\n",
       "      <td>EL James</td>\n",
       "      <td>77</td>\n",
       "      <td>777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>77</td>\n",
       "      <td>EL James</td>\n",
       "      <td>77</td>\n",
       "      <td>888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>88</td>\n",
       "      <td>Stephen Hawking</td>\n",
       "      <td>88</td>\n",
       "      <td>202</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   AuthCode             AuthName AuthCode BookCode\n",
       "0        11           JK Rowling       11      111\n",
       "1        11           JK Rowling       11      999\n",
       "2        22      Charles Dickens       22      101\n",
       "3        22      Charles Dickens       22      222\n",
       "4        33             Stan Lee       33      444\n",
       "5        44  William Shakespeare       44      444\n",
       "6        44  William Shakespeare       44      555\n",
       "7        55        Chetan Bhagat       55      333\n",
       "8        55        Chetan Bhagat       55      555\n",
       "9        55        Chetan Bhagat       55      666\n",
       "10       66            Premchand       66      101\n",
       "11       77             EL James       77      777\n",
       "12       77             EL James       77      888\n",
       "13       88      Stephen Hawking       88      202"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#query_statement\n",
    "sql1=\"select * from  Author  INNER JOIN Writes \\\n",
    "      on Author.AuthCode=Writes.AuthCode\"\n",
    "\n",
    "tmp=cursor.execute(sql1)\n",
    "result_table=cursor.fetchall()\n",
    "\n",
    "columns_=[]\n",
    "for i in cursor.description:\n",
    "    columns_.append(i[0])\n",
    "#columns = np.array([\"AuthCode\", \"AuthName\", \"AuthCode\", \"BookCode\"])\n",
    "pd.DataFrame(np.array(result_table), columns=np.array(columns_))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## LEFT JOIN\n",
    "Returns a table which contains all the columns of both the tables, </br> \n",
    "all the rows of the left table and only those rows of the right table </br>\n",
    "which satisfy the join condition. The remaining entries are filled </br>with NULL</br></br>\n",
    "<img src=\"https://www.w3schools.com/sql/img_leftjoin.gif\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Shown below are the tables on which we will perform left join</br> operations on the condition : Publishes.BookCode=Book.BookCode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>BookCode</th>\n",
       "      <th>BookName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Nirmala</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111</td>\n",
       "      <td>Philosophers Stone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>202</td>\n",
       "      <td>A Brief History of Time</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>222</td>\n",
       "      <td>Tom Sawyer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>333</td>\n",
       "      <td>Half Girlfriend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>444</td>\n",
       "      <td>Arrow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>555</td>\n",
       "      <td>Tempest</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>666</td>\n",
       "      <td>Five Point Someone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>777</td>\n",
       "      <td>Fifty Shades of Grey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>888</td>\n",
       "      <td>Fifty Shades Freed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>999</td>\n",
       "      <td>Half Blood Prince</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   BookCode                 BookName\n",
       "0       101                  Nirmala\n",
       "1       111       Philosophers Stone\n",
       "2       202  A Brief History of Time\n",
       "3       222               Tom Sawyer\n",
       "4       333          Half Girlfriend\n",
       "5       444                    Arrow\n",
       "6       555                  Tempest\n",
       "7       666       Five Point Someone\n",
       "8       777     Fifty Shades of Grey\n",
       "9       888       Fifty Shades Freed\n",
       "10      999        Half Blood Prince"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>BookCode</th>\n",
       "      <th>PubCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>777</td>\n",
       "      <td>1111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111</td>\n",
       "      <td>2222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>999</td>\n",
       "      <td>2222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>222</td>\n",
       "      <td>3333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>444</td>\n",
       "      <td>4444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>555</td>\n",
       "      <td>4444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>101</td>\n",
       "      <td>6666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>202</td>\n",
       "      <td>7777</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   BookCode  PubCode\n",
       "0       777     1111\n",
       "1       111     2222\n",
       "2       999     2222\n",
       "3       222     3333\n",
       "4       444     4444\n",
       "5       555     4444\n",
       "6       101     6666\n",
       "7       202     7777"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "show_table(\"Book\")                #left table\n",
    "show_table('Publishes')           #right table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>BookCode</th>\n",
       "      <th>BookName</th>\n",
       "      <th>BookCode</th>\n",
       "      <th>PubCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Nirmala</td>\n",
       "      <td>101</td>\n",
       "      <td>6666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111</td>\n",
       "      <td>Philosophers Stone</td>\n",
       "      <td>111</td>\n",
       "      <td>2222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>202</td>\n",
       "      <td>A Brief History of Time</td>\n",
       "      <td>202</td>\n",
       "      <td>7777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>222</td>\n",
       "      <td>Tom Sawyer</td>\n",
       "      <td>222</td>\n",
       "      <td>3333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>333</td>\n",
       "      <td>Half Girlfriend</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>444</td>\n",
       "      <td>Arrow</td>\n",
       "      <td>444</td>\n",
       "      <td>4444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>555</td>\n",
       "      <td>Tempest</td>\n",
       "      <td>555</td>\n",
       "      <td>4444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>666</td>\n",
       "      <td>Five Point Someone</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>777</td>\n",
       "      <td>Fifty Shades of Grey</td>\n",
       "      <td>777</td>\n",
       "      <td>1111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>888</td>\n",
       "      <td>Fifty Shades Freed</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>999</td>\n",
       "      <td>Half Blood Prince</td>\n",
       "      <td>999</td>\n",
       "      <td>2222</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   BookCode                 BookName BookCode PubCode\n",
       "0       101                  Nirmala      101    6666\n",
       "1       111       Philosophers Stone      111    2222\n",
       "2       202  A Brief History of Time      202    7777\n",
       "3       222               Tom Sawyer      222    3333\n",
       "4       333          Half Girlfriend     None    None\n",
       "5       444                    Arrow      444    4444\n",
       "6       555                  Tempest      555    4444\n",
       "7       666       Five Point Someone     None    None\n",
       "8       777     Fifty Shades of Grey      777    1111\n",
       "9       888       Fifty Shades Freed     None    None\n",
       "10      999        Half Blood Prince      999    2222"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#query_statement\n",
    "sql1=\"select * from  Book LEFT JOIN Publishes \\\n",
    "      on Publishes.BookCode=Book.BookCode\"\n",
    "\n",
    "tmp=cursor.execute(sql1)\n",
    "result_table=cursor.fetchall()\n",
    "\n",
    "columns_=[]\n",
    "for i in cursor.description:\n",
    "    columns_.append(i[0])\n",
    "pd.DataFrame(np.array(result_table), columns=np.array(columns_))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## RIGHT JOIN\n",
    "Returns a table with all rows from the right table, and for left table it takes only those rows which satisfy the join condition. For the other rows of left table it fills with NULL value.</br></br>\n",
    "<img src=\"https://www.w3schools.com/sql/img_rightjoin.gif\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Shown below are the tables on which we will perform right join</br> operations on the condition : Publishes.BookCode=Book.BookCode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>BookCode</th>\n",
       "      <th>PubCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>777</td>\n",
       "      <td>1111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111</td>\n",
       "      <td>2222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>999</td>\n",
       "      <td>2222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>222</td>\n",
       "      <td>3333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>444</td>\n",
       "      <td>4444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>555</td>\n",
       "      <td>4444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>101</td>\n",
       "      <td>6666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>202</td>\n",
       "      <td>7777</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   BookCode  PubCode\n",
       "0       777     1111\n",
       "1       111     2222\n",
       "2       999     2222\n",
       "3       222     3333\n",
       "4       444     4444\n",
       "5       555     4444\n",
       "6       101     6666\n",
       "7       202     7777"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>BookCode</th>\n",
       "      <th>BookName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Nirmala</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111</td>\n",
       "      <td>Philosophers Stone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>202</td>\n",
       "      <td>A Brief History of Time</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>222</td>\n",
       "      <td>Tom Sawyer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>333</td>\n",
       "      <td>Half Girlfriend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>444</td>\n",
       "      <td>Arrow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>555</td>\n",
       "      <td>Tempest</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>666</td>\n",
       "      <td>Five Point Someone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>777</td>\n",
       "      <td>Fifty Shades of Grey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>888</td>\n",
       "      <td>Fifty Shades Freed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>999</td>\n",
       "      <td>Half Blood Prince</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   BookCode                 BookName\n",
       "0       101                  Nirmala\n",
       "1       111       Philosophers Stone\n",
       "2       202  A Brief History of Time\n",
       "3       222               Tom Sawyer\n",
       "4       333          Half Girlfriend\n",
       "5       444                    Arrow\n",
       "6       555                  Tempest\n",
       "7       666       Five Point Someone\n",
       "8       777     Fifty Shades of Grey\n",
       "9       888       Fifty Shades Freed\n",
       "10      999        Half Blood Prince"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "show_table(\"Publishes\")\n",
    "show_table(\"Book\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "EOL while scanning string literal (<ipython-input-40-42207567c5c4>, line 2)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;36m  File \u001b[0;32m\"<ipython-input-40-42207567c5c4>\"\u001b[0;36m, line \u001b[0;32m2\u001b[0m\n\u001b[0;31m    sql1=\"select * from  Publishes RIGHT JOIN Book \\\u001b[0m\n\u001b[0m                                                             ^\u001b[0m\n\u001b[0;31mSyntaxError\u001b[0m\u001b[0;31m:\u001b[0m EOL while scanning string literal\n"
     ]
    }
   ],
   "source": [
    "#query_statement\n",
    "sql1=\"select * from  Publishes RIGHT JOIN Book \\         \n",
    "      on Publishes.BookCode=Book.BookCode\"\n",
    "\n",
    "tmp=cursor.execute(sql1)\n",
    "result_table=cursor.fetchall()      #fetches all the rows of the resulant table\n",
    "\n",
    "columns_=[]\n",
    "for i in cursor.description:        #stores the names of the columns of the resultant table\n",
    "    columns_.append(i[0])\n",
    "pd.DataFrame(np.array(result_table), columns=np.array(columns_))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## FULL OUTER JOIN\n",
    "This returns a table with all the rows from both tables. If the join condition is not satisfied </br>\n",
    "then those are filled with NULL value in case of both left and right table</br></br>\n",
    "<img src=\"https://www.w3schools.com/sql/img_fulljoin.gif\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Shown below are the tables on which we will perform full outer join</br> operations on the condition : Publishes.BookCode=Book.BookCode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>BookCode</th>\n",
       "      <th>PubCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>777</td>\n",
       "      <td>1111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111</td>\n",
       "      <td>2222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>999</td>\n",
       "      <td>2222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>222</td>\n",
       "      <td>3333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>444</td>\n",
       "      <td>4444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>555</td>\n",
       "      <td>4444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>101</td>\n",
       "      <td>6666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>202</td>\n",
       "      <td>7777</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   BookCode  PubCode\n",
       "0       777     1111\n",
       "1       111     2222\n",
       "2       999     2222\n",
       "3       222     3333\n",
       "4       444     4444\n",
       "5       555     4444\n",
       "6       101     6666\n",
       "7       202     7777"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>BookCode</th>\n",
       "      <th>BookName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Nirmala</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111</td>\n",
       "      <td>Philosophers Stone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>202</td>\n",
       "      <td>A Brief History of Time</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>222</td>\n",
       "      <td>Tom Sawyer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>333</td>\n",
       "      <td>Half Girlfriend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>444</td>\n",
       "      <td>Arrow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>555</td>\n",
       "      <td>Tempest</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>666</td>\n",
       "      <td>Five Point Someone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>777</td>\n",
       "      <td>Fifty Shades of Grey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>888</td>\n",
       "      <td>Fifty Shades Freed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>999</td>\n",
       "      <td>Half Blood Prince</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   BookCode                 BookName\n",
       "0       101                  Nirmala\n",
       "1       111       Philosophers Stone\n",
       "2       202  A Brief History of Time\n",
       "3       222               Tom Sawyer\n",
       "4       333          Half Girlfriend\n",
       "5       444                    Arrow\n",
       "6       555                  Tempest\n",
       "7       666       Five Point Someone\n",
       "8       777     Fifty Shades of Grey\n",
       "9       888       Fifty Shades Freed\n",
       "10      999        Half Blood Prince"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "show_table(\"Publishes\")\n",
    "show_table(\"Book\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>BookCode</th>\n",
       "      <th>PubCode</th>\n",
       "      <th>BookCode</th>\n",
       "      <th>BookName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>777</td>\n",
       "      <td>1111</td>\n",
       "      <td>777</td>\n",
       "      <td>Fifty Shades of Grey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111</td>\n",
       "      <td>2222</td>\n",
       "      <td>111</td>\n",
       "      <td>Philosophers Stone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>999</td>\n",
       "      <td>2222</td>\n",
       "      <td>999</td>\n",
       "      <td>Half Blood Prince</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>222</td>\n",
       "      <td>3333</td>\n",
       "      <td>222</td>\n",
       "      <td>Tom Sawyer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>444</td>\n",
       "      <td>4444</td>\n",
       "      <td>444</td>\n",
       "      <td>Arrow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>555</td>\n",
       "      <td>4444</td>\n",
       "      <td>555</td>\n",
       "      <td>Tempest</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>101</td>\n",
       "      <td>6666</td>\n",
       "      <td>101</td>\n",
       "      <td>Nirmala</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>202</td>\n",
       "      <td>7777</td>\n",
       "      <td>202</td>\n",
       "      <td>A Brief History of Time</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>333</td>\n",
       "      <td>Half Girlfriend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>666</td>\n",
       "      <td>Five Point Someone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>888</td>\n",
       "      <td>Fifty Shades Freed</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   BookCode PubCode BookCode                 BookName\n",
       "0       777    1111      777     Fifty Shades of Grey\n",
       "1       111    2222      111       Philosophers Stone\n",
       "2       999    2222      999        Half Blood Prince\n",
       "3       222    3333      222               Tom Sawyer\n",
       "4       444    4444      444                    Arrow\n",
       "5       555    4444      555                  Tempest\n",
       "6       101    6666      101                  Nirmala\n",
       "7       202    7777      202  A Brief History of Time\n",
       "8      None    None      333          Half Girlfriend\n",
       "9      None    None      666       Five Point Someone\n",
       "10     None    None      888       Fifty Shades Freed"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#query_statement\n",
    "sql1=\"select * from  Publishes P LEFT JOIN Book B ON P.BookCode=B.BookCode \\\n",
    "UNION \\\n",
    "select * from Publishes P RIGHT JOIN Book B ON P.BookCode=B.BookCode\"\n",
    "\n",
    "tmp=cursor.execute(sql1)\n",
    "result_table=cursor.fetchall()\n",
    "\n",
    "columns_=[]\n",
    "for i in cursor.description:\n",
    "    columns_.append(i[0])\n",
    "pd.DataFrame(np.array(result_table), columns=np.array(columns_))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## NATURAL JOIN\n",
    "Natural Join is can be any of the above joins with some more modifications. It does not require ON clause and instead performs join based on all the columns of the tables which have same name (datatype should also be same). The columns with same name appear only once in the resultant table. By default Natural Join performs Natural Inner Join<br><br>\n",
    "Shown below are the tables on which we will perform full outer join</br> operations on the condition : Author.AuthCode=Writes.AuthCode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>BookCode</th>\n",
       "      <th>PubCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>777</td>\n",
       "      <td>1111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111</td>\n",
       "      <td>2222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>999</td>\n",
       "      <td>2222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>222</td>\n",
       "      <td>3333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>444</td>\n",
       "      <td>4444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>555</td>\n",
       "      <td>4444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>101</td>\n",
       "      <td>6666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>202</td>\n",
       "      <td>7777</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   BookCode  PubCode\n",
       "0       777     1111\n",
       "1       111     2222\n",
       "2       999     2222\n",
       "3       222     3333\n",
       "4       444     4444\n",
       "5       555     4444\n",
       "6       101     6666\n",
       "7       202     7777"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>BookCode</th>\n",
       "      <th>BookName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Nirmala</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111</td>\n",
       "      <td>Philosophers Stone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>202</td>\n",
       "      <td>A Brief History of Time</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>222</td>\n",
       "      <td>Tom Sawyer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>333</td>\n",
       "      <td>Half Girlfriend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>444</td>\n",
       "      <td>Arrow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>555</td>\n",
       "      <td>Tempest</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>666</td>\n",
       "      <td>Five Point Someone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>777</td>\n",
       "      <td>Fifty Shades of Grey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>888</td>\n",
       "      <td>Fifty Shades Freed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>999</td>\n",
       "      <td>Half Blood Prince</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   BookCode                 BookName\n",
       "0       101                  Nirmala\n",
       "1       111       Philosophers Stone\n",
       "2       202  A Brief History of Time\n",
       "3       222               Tom Sawyer\n",
       "4       333          Half Girlfriend\n",
       "5       444                    Arrow\n",
       "6       555                  Tempest\n",
       "7       666       Five Point Someone\n",
       "8       777     Fifty Shades of Grey\n",
       "9       888       Fifty Shades Freed\n",
       "10      999        Half Blood Prince"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "show_table(\"Publishes\")\n",
    "show_table(\"Book\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>BookCode</th>\n",
       "      <th>PubCode</th>\n",
       "      <th>BookName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>777</td>\n",
       "      <td>1111</td>\n",
       "      <td>Fifty Shades of Grey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111</td>\n",
       "      <td>2222</td>\n",
       "      <td>Philosophers Stone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>999</td>\n",
       "      <td>2222</td>\n",
       "      <td>Half Blood Prince</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>222</td>\n",
       "      <td>3333</td>\n",
       "      <td>Tom Sawyer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>444</td>\n",
       "      <td>4444</td>\n",
       "      <td>Arrow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>555</td>\n",
       "      <td>4444</td>\n",
       "      <td>Tempest</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>101</td>\n",
       "      <td>6666</td>\n",
       "      <td>Nirmala</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>202</td>\n",
       "      <td>7777</td>\n",
       "      <td>A Brief History of Time</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  BookCode PubCode                 BookName\n",
       "0      777    1111     Fifty Shades of Grey\n",
       "1      111    2222       Philosophers Stone\n",
       "2      999    2222        Half Blood Prince\n",
       "3      222    3333               Tom Sawyer\n",
       "4      444    4444                    Arrow\n",
       "5      555    4444                  Tempest\n",
       "6      101    6666                  Nirmala\n",
       "7      202    7777  A Brief History of Time"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#query_statement\n",
    "sql1=\"select * from  Publishes NATURAL JOIN Book \\\n",
    "      \"\n",
    "tmp=cursor.execute(sql1)\n",
    "result_table=cursor.fetchall()      #fetches all the rows of the resulant table\n",
    "\n",
    "columns_=[]\n",
    "for i in cursor.description:        #stores the names of the columns of the resultant table\n",
    "    columns_.append(i[0])\n",
    "pd.DataFrame(np.array(result_table), columns=np.array(columns_))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### NATURAL RIGHT JOIN with Publishe as Left and Book as Right tables\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>BookCode</th>\n",
       "      <th>BookName</th>\n",
       "      <th>PubCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Nirmala</td>\n",
       "      <td>6666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111</td>\n",
       "      <td>Philosophers Stone</td>\n",
       "      <td>2222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>202</td>\n",
       "      <td>A Brief History of Time</td>\n",
       "      <td>7777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>222</td>\n",
       "      <td>Tom Sawyer</td>\n",
       "      <td>3333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>333</td>\n",
       "      <td>Half Girlfriend</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>444</td>\n",
       "      <td>Arrow</td>\n",
       "      <td>4444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>555</td>\n",
       "      <td>Tempest</td>\n",
       "      <td>4444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>666</td>\n",
       "      <td>Five Point Someone</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>777</td>\n",
       "      <td>Fifty Shades of Grey</td>\n",
       "      <td>1111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>888</td>\n",
       "      <td>Fifty Shades Freed</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>999</td>\n",
       "      <td>Half Blood Prince</td>\n",
       "      <td>2222</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   BookCode                 BookName PubCode\n",
       "0       101                  Nirmala    6666\n",
       "1       111       Philosophers Stone    2222\n",
       "2       202  A Brief History of Time    7777\n",
       "3       222               Tom Sawyer    3333\n",
       "4       333          Half Girlfriend    None\n",
       "5       444                    Arrow    4444\n",
       "6       555                  Tempest    4444\n",
       "7       666       Five Point Someone    None\n",
       "8       777     Fifty Shades of Grey    1111\n",
       "9       888       Fifty Shades Freed    None\n",
       "10      999        Half Blood Prince    2222"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#query_statement\n",
    "sql1=\"select * from  Publishes NATURAL RIGHT JOIN Book \\\n",
    "      \"\n",
    "tmp=cursor.execute(sql1)\n",
    "result_table=cursor.fetchall()      #fetches all the rows of the resulant table\n",
    "\n",
    "columns_=[]\n",
    "for i in cursor.description:        #stores the names of the columns of the resultant table\n",
    "    columns_.append(i[0])\n",
    "pd.DataFrame(np.array(result_table), columns=np.array(columns_))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### NATURAL LEFT JOIN with Book as Left and Publishes as Right table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>BookCode</th>\n",
       "      <th>BookName</th>\n",
       "      <th>PubCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Nirmala</td>\n",
       "      <td>6666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111</td>\n",
       "      <td>Philosophers Stone</td>\n",
       "      <td>2222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>202</td>\n",
       "      <td>A Brief History of Time</td>\n",
       "      <td>7777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>222</td>\n",
       "      <td>Tom Sawyer</td>\n",
       "      <td>3333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>333</td>\n",
       "      <td>Half Girlfriend</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>444</td>\n",
       "      <td>Arrow</td>\n",
       "      <td>4444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>555</td>\n",
       "      <td>Tempest</td>\n",
       "      <td>4444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>666</td>\n",
       "      <td>Five Point Someone</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>777</td>\n",
       "      <td>Fifty Shades of Grey</td>\n",
       "      <td>1111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>888</td>\n",
       "      <td>Fifty Shades Freed</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>999</td>\n",
       "      <td>Half Blood Prince</td>\n",
       "      <td>2222</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   BookCode                 BookName PubCode\n",
       "0       101                  Nirmala    6666\n",
       "1       111       Philosophers Stone    2222\n",
       "2       202  A Brief History of Time    7777\n",
       "3       222               Tom Sawyer    3333\n",
       "4       333          Half Girlfriend    None\n",
       "5       444                    Arrow    4444\n",
       "6       555                  Tempest    4444\n",
       "7       666       Five Point Someone    None\n",
       "8       777     Fifty Shades of Grey    1111\n",
       "9       888       Fifty Shades Freed    None\n",
       "10      999        Half Blood Prince    2222"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#query_statement\n",
    "sql1=\"select * from  Book NATURAL LEFT JOIN Publishes \\\n",
    "      \"\n",
    "tmp=cursor.execute(sql1)\n",
    "result_table=cursor.fetchall()      #fetches all the rows of the resulant table\n",
    "\n",
    "columns_=[]      \n",
    "for i in cursor.description:        #stores the names of the columns of the resultant table\n",
    "    columns_.append(i[0])\n",
    "pd.DataFrame(np.array(result_table), columns=np.array(columns_))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}