3. JOIN Statements.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# JOIN Statements\n",
    "\n",
    "In this notebook we study some use case examples of the JOIN statments. We make use of Python as our main programming alongside libraries such as Pandas and Psycopg2 to present the results of the queries.\n",
    "\n",
    "The JOIN statements helps us to combine rows from multiple tables.\n",
    "\n",
    "In order to start coding, we have to call the pandas and psycopg2 libraries and create a connection to communicate with the database. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import psycopg2 as pg2\n",
    "\n",
    "connection = pg2.connect(database = 'dvdrental', user = 'postgres', password = 'password')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Just like in the prevous notebook, we´ll define the get_data function in order to prevent memory problems and to make our code cleaner"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_data(query, rows = 10):\n",
    "\n",
    "    with connection.cursor() as cursor:\n",
    "        cursor.execute(query)\n",
    "\n",
    "        if rows == 'all':\n",
    "            raw_data = cursor.fetchall()\n",
    "        else:\n",
    "            raw_data = cursor.fetchmany(rows) \n",
    "\n",
    "        col_names = [col_desc[0] for col_desc in cursor.description]\n",
    "        data = pd.DataFrame(raw_data, columns = col_names)\n",
    "\n",
    "    return data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## INNER JOIN \n",
    "\n",
    "The INNER JOIN statement is used to create a new table by combining rows that have matching values in two or more tables. In other words, the INNER JOIN statment helps us to find a sort of intersection between to tables based on their values on a given column, putting together only the rows whose values on that given column appear in both tables.\n",
    "\n",
    "## Ex. 1\n",
    "\n",
    "Consider the payment table and the customer table, the customer_id column happens to be in both tables, therefore we can join these two tables on this column. Let's do this and see what result we get"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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>payment_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>staff_id</th>\n",
       "      <th>rental_id</th>\n",
       "      <th>amount</th>\n",
       "      <th>payment_date</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>store_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>address_id</th>\n",
       "      <th>activebool</th>\n",
       "      <th>create_date</th>\n",
       "      <th>last_update</th>\n",
       "      <th>active</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17503</td>\n",
       "      <td>341</td>\n",
       "      <td>2</td>\n",
       "      <td>1520</td>\n",
       "      <td>7.99</td>\n",
       "      <td>2007-02-15 22:25:46.996577</td>\n",
       "      <td>341</td>\n",
       "      <td>1</td>\n",
       "      <td>Peter</td>\n",
       "      <td>Menard</td>\n",
       "      <td>peter.menard@sakilacustomer.org</td>\n",
       "      <td>346</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17504</td>\n",
       "      <td>341</td>\n",
       "      <td>1</td>\n",
       "      <td>1778</td>\n",
       "      <td>1.99</td>\n",
       "      <td>2007-02-16 17:23:14.996577</td>\n",
       "      <td>341</td>\n",
       "      <td>1</td>\n",
       "      <td>Peter</td>\n",
       "      <td>Menard</td>\n",
       "      <td>peter.menard@sakilacustomer.org</td>\n",
       "      <td>346</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17505</td>\n",
       "      <td>341</td>\n",
       "      <td>1</td>\n",
       "      <td>1849</td>\n",
       "      <td>7.99</td>\n",
       "      <td>2007-02-16 22:41:45.996577</td>\n",
       "      <td>341</td>\n",
       "      <td>1</td>\n",
       "      <td>Peter</td>\n",
       "      <td>Menard</td>\n",
       "      <td>peter.menard@sakilacustomer.org</td>\n",
       "      <td>346</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17506</td>\n",
       "      <td>341</td>\n",
       "      <td>2</td>\n",
       "      <td>2829</td>\n",
       "      <td>2.99</td>\n",
       "      <td>2007-02-19 19:39:56.996577</td>\n",
       "      <td>341</td>\n",
       "      <td>1</td>\n",
       "      <td>Peter</td>\n",
       "      <td>Menard</td>\n",
       "      <td>peter.menard@sakilacustomer.org</td>\n",
       "      <td>346</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17507</td>\n",
       "      <td>341</td>\n",
       "      <td>2</td>\n",
       "      <td>3130</td>\n",
       "      <td>7.99</td>\n",
       "      <td>2007-02-20 17:31:48.996577</td>\n",
       "      <td>341</td>\n",
       "      <td>1</td>\n",
       "      <td>Peter</td>\n",
       "      <td>Menard</td>\n",
       "      <td>peter.menard@sakilacustomer.org</td>\n",
       "      <td>346</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>17508</td>\n",
       "      <td>341</td>\n",
       "      <td>1</td>\n",
       "      <td>3382</td>\n",
       "      <td>5.99</td>\n",
       "      <td>2007-02-21 12:33:49.996577</td>\n",
       "      <td>341</td>\n",
       "      <td>1</td>\n",
       "      <td>Peter</td>\n",
       "      <td>Menard</td>\n",
       "      <td>peter.menard@sakilacustomer.org</td>\n",
       "      <td>346</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>17509</td>\n",
       "      <td>342</td>\n",
       "      <td>2</td>\n",
       "      <td>2190</td>\n",
       "      <td>5.99</td>\n",
       "      <td>2007-02-17 23:58:17.996577</td>\n",
       "      <td>342</td>\n",
       "      <td>1</td>\n",
       "      <td>Harold</td>\n",
       "      <td>Martino</td>\n",
       "      <td>harold.martino@sakilacustomer.org</td>\n",
       "      <td>347</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>17510</td>\n",
       "      <td>342</td>\n",
       "      <td>1</td>\n",
       "      <td>2914</td>\n",
       "      <td>5.99</td>\n",
       "      <td>2007-02-20 02:11:44.996577</td>\n",
       "      <td>342</td>\n",
       "      <td>1</td>\n",
       "      <td>Harold</td>\n",
       "      <td>Martino</td>\n",
       "      <td>harold.martino@sakilacustomer.org</td>\n",
       "      <td>347</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>17511</td>\n",
       "      <td>342</td>\n",
       "      <td>1</td>\n",
       "      <td>3081</td>\n",
       "      <td>2.99</td>\n",
       "      <td>2007-02-20 13:57:39.996577</td>\n",
       "      <td>342</td>\n",
       "      <td>1</td>\n",
       "      <td>Harold</td>\n",
       "      <td>Martino</td>\n",
       "      <td>harold.martino@sakilacustomer.org</td>\n",
       "      <td>347</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>17512</td>\n",
       "      <td>343</td>\n",
       "      <td>2</td>\n",
       "      <td>1547</td>\n",
       "      <td>4.99</td>\n",
       "      <td>2007-02-16 00:10:50.996577</td>\n",
       "      <td>343</td>\n",
       "      <td>1</td>\n",
       "      <td>Douglas</td>\n",
       "      <td>Graf</td>\n",
       "      <td>douglas.graf@sakilacustomer.org</td>\n",
       "      <td>348</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   payment_id  customer_id  staff_id  rental_id amount  \\\n",
       "0       17503          341         2       1520   7.99   \n",
       "1       17504          341         1       1778   1.99   \n",
       "2       17505          341         1       1849   7.99   \n",
       "3       17506          341         2       2829   2.99   \n",
       "4       17507          341         2       3130   7.99   \n",
       "5       17508          341         1       3382   5.99   \n",
       "6       17509          342         2       2190   5.99   \n",
       "7       17510          342         1       2914   5.99   \n",
       "8       17511          342         1       3081   2.99   \n",
       "9       17512          343         2       1547   4.99   \n",
       "\n",
       "                payment_date  customer_id  store_id first_name last_name  \\\n",
       "0 2007-02-15 22:25:46.996577          341         1      Peter    Menard   \n",
       "1 2007-02-16 17:23:14.996577          341         1      Peter    Menard   \n",
       "2 2007-02-16 22:41:45.996577          341         1      Peter    Menard   \n",
       "3 2007-02-19 19:39:56.996577          341         1      Peter    Menard   \n",
       "4 2007-02-20 17:31:48.996577          341         1      Peter    Menard   \n",
       "5 2007-02-21 12:33:49.996577          341         1      Peter    Menard   \n",
       "6 2007-02-17 23:58:17.996577          342         1     Harold   Martino   \n",
       "7 2007-02-20 02:11:44.996577          342         1     Harold   Martino   \n",
       "8 2007-02-20 13:57:39.996577          342         1     Harold   Martino   \n",
       "9 2007-02-16 00:10:50.996577          343         1    Douglas      Graf   \n",
       "\n",
       "                               email  address_id  activebool create_date  \\\n",
       "0    peter.menard@sakilacustomer.org         346        True  2006-02-14   \n",
       "1    peter.menard@sakilacustomer.org         346        True  2006-02-14   \n",
       "2    peter.menard@sakilacustomer.org         346        True  2006-02-14   \n",
       "3    peter.menard@sakilacustomer.org         346        True  2006-02-14   \n",
       "4    peter.menard@sakilacustomer.org         346        True  2006-02-14   \n",
       "5    peter.menard@sakilacustomer.org         346        True  2006-02-14   \n",
       "6  harold.martino@sakilacustomer.org         347        True  2006-02-14   \n",
       "7  harold.martino@sakilacustomer.org         347        True  2006-02-14   \n",
       "8  harold.martino@sakilacustomer.org         347        True  2006-02-14   \n",
       "9    douglas.graf@sakilacustomer.org         348        True  2006-02-14   \n",
       "\n",
       "              last_update  active  \n",
       "0 2013-05-26 14:49:45.738       1  \n",
       "1 2013-05-26 14:49:45.738       1  \n",
       "2 2013-05-26 14:49:45.738       1  \n",
       "3 2013-05-26 14:49:45.738       1  \n",
       "4 2013-05-26 14:49:45.738       1  \n",
       "5 2013-05-26 14:49:45.738       1  \n",
       "6 2013-05-26 14:49:45.738       1  \n",
       "7 2013-05-26 14:49:45.738       1  \n",
       "8 2013-05-26 14:49:45.738       1  \n",
       "9 2013-05-26 14:49:45.738       1  "
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_1 = '''\n",
    "          SELECT *  \n",
    "          FROM payment INNER JOIN customer\n",
    "          ON payment.customer_id = customer.customer_id\n",
    "          '''\n",
    "items_in_customer_and_payment = get_data(query_1)\n",
    "items_in_customer_and_payment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ex. 2\n",
    "\n",
    "In addition to the above, we can especify which columns to select from either table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>payment_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17503</td>\n",
       "      <td>341</td>\n",
       "      <td>Peter</td>\n",
       "      <td>Menard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17504</td>\n",
       "      <td>341</td>\n",
       "      <td>Peter</td>\n",
       "      <td>Menard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17505</td>\n",
       "      <td>341</td>\n",
       "      <td>Peter</td>\n",
       "      <td>Menard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17506</td>\n",
       "      <td>341</td>\n",
       "      <td>Peter</td>\n",
       "      <td>Menard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17507</td>\n",
       "      <td>341</td>\n",
       "      <td>Peter</td>\n",
       "      <td>Menard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>17508</td>\n",
       "      <td>341</td>\n",
       "      <td>Peter</td>\n",
       "      <td>Menard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>17509</td>\n",
       "      <td>342</td>\n",
       "      <td>Harold</td>\n",
       "      <td>Martino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>17510</td>\n",
       "      <td>342</td>\n",
       "      <td>Harold</td>\n",
       "      <td>Martino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>17511</td>\n",
       "      <td>342</td>\n",
       "      <td>Harold</td>\n",
       "      <td>Martino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>17512</td>\n",
       "      <td>343</td>\n",
       "      <td>Douglas</td>\n",
       "      <td>Graf</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   payment_id  customer_id first_name last_name\n",
       "0       17503          341      Peter    Menard\n",
       "1       17504          341      Peter    Menard\n",
       "2       17505          341      Peter    Menard\n",
       "3       17506          341      Peter    Menard\n",
       "4       17507          341      Peter    Menard\n",
       "5       17508          341      Peter    Menard\n",
       "6       17509          342     Harold   Martino\n",
       "7       17510          342     Harold   Martino\n",
       "8       17511          342     Harold   Martino\n",
       "9       17512          343    Douglas      Graf"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_2 = '''\n",
    "          SELECT payment_id, payment.customer_id, first_name, last_name \n",
    "          FROM payment INNER JOIN customer\n",
    "          ON payment.customer_id = customer.customer_id\n",
    "          '''\n",
    "items_in_customer_and_payment = get_data(query_2)\n",
    "items_in_customer_and_payment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that, when the tables happen to contain a column with the same name, we use the syntax table.column_name to specify from which tale we want to grab the column.\n",
    "\n",
    "## FULL OUTER JOIN Statement\n",
    "\n",
    "The FULL OUTER JOUN Statement grabs all the rows form both tables, whether their values match or not. When the values match, in the selected field, the statement will deal with those rows in the excact same way as the INNER JOIN statement. But what happens to the unmatched rows? Let's say we want to FULL OUTER JOIN table A with table B, supose that there are umatched rows form table A, in this case, at te moment of incluiding these rows in the output data SQL will fill the missing values, corresponding to table B columns, with NULL values. An analogous procedure occurs in the opposite case. \n",
    "\n",
    "## Ex 3.\n",
    "\n",
    "Let's repeat example 1, but now using the FULL OUTER JOIN statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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>payment_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>staff_id</th>\n",
       "      <th>rental_id</th>\n",
       "      <th>amount</th>\n",
       "      <th>payment_date</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>store_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>address_id</th>\n",
       "      <th>activebool</th>\n",
       "      <th>create_date</th>\n",
       "      <th>last_update</th>\n",
       "      <th>active</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17503</td>\n",
       "      <td>341</td>\n",
       "      <td>2</td>\n",
       "      <td>1520</td>\n",
       "      <td>7.99</td>\n",
       "      <td>2007-02-15 22:25:46.996577</td>\n",
       "      <td>341</td>\n",
       "      <td>1</td>\n",
       "      <td>Peter</td>\n",
       "      <td>Menard</td>\n",
       "      <td>peter.menard@sakilacustomer.org</td>\n",
       "      <td>346</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17504</td>\n",
       "      <td>341</td>\n",
       "      <td>1</td>\n",
       "      <td>1778</td>\n",
       "      <td>1.99</td>\n",
       "      <td>2007-02-16 17:23:14.996577</td>\n",
       "      <td>341</td>\n",
       "      <td>1</td>\n",
       "      <td>Peter</td>\n",
       "      <td>Menard</td>\n",
       "      <td>peter.menard@sakilacustomer.org</td>\n",
       "      <td>346</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17505</td>\n",
       "      <td>341</td>\n",
       "      <td>1</td>\n",
       "      <td>1849</td>\n",
       "      <td>7.99</td>\n",
       "      <td>2007-02-16 22:41:45.996577</td>\n",
       "      <td>341</td>\n",
       "      <td>1</td>\n",
       "      <td>Peter</td>\n",
       "      <td>Menard</td>\n",
       "      <td>peter.menard@sakilacustomer.org</td>\n",
       "      <td>346</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17506</td>\n",
       "      <td>341</td>\n",
       "      <td>2</td>\n",
       "      <td>2829</td>\n",
       "      <td>2.99</td>\n",
       "      <td>2007-02-19 19:39:56.996577</td>\n",
       "      <td>341</td>\n",
       "      <td>1</td>\n",
       "      <td>Peter</td>\n",
       "      <td>Menard</td>\n",
       "      <td>peter.menard@sakilacustomer.org</td>\n",
       "      <td>346</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17507</td>\n",
       "      <td>341</td>\n",
       "      <td>2</td>\n",
       "      <td>3130</td>\n",
       "      <td>7.99</td>\n",
       "      <td>2007-02-20 17:31:48.996577</td>\n",
       "      <td>341</td>\n",
       "      <td>1</td>\n",
       "      <td>Peter</td>\n",
       "      <td>Menard</td>\n",
       "      <td>peter.menard@sakilacustomer.org</td>\n",
       "      <td>346</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14591</th>\n",
       "      <td>32094</td>\n",
       "      <td>245</td>\n",
       "      <td>2</td>\n",
       "      <td>12682</td>\n",
       "      <td>2.99</td>\n",
       "      <td>2007-05-14 13:44:29.996577</td>\n",
       "      <td>245</td>\n",
       "      <td>1</td>\n",
       "      <td>Courtney</td>\n",
       "      <td>Day</td>\n",
       "      <td>courtney.day@sakilacustomer.org</td>\n",
       "      <td>249</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14592</th>\n",
       "      <td>32095</td>\n",
       "      <td>251</td>\n",
       "      <td>1</td>\n",
       "      <td>14107</td>\n",
       "      <td>0.99</td>\n",
       "      <td>2007-05-14 13:44:29.996577</td>\n",
       "      <td>251</td>\n",
       "      <td>2</td>\n",
       "      <td>Vickie</td>\n",
       "      <td>Brewer</td>\n",
       "      <td>vickie.brewer@sakilacustomer.org</td>\n",
       "      <td>255</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14593</th>\n",
       "      <td>32096</td>\n",
       "      <td>252</td>\n",
       "      <td>2</td>\n",
       "      <td>13756</td>\n",
       "      <td>4.99</td>\n",
       "      <td>2007-05-14 13:44:29.996577</td>\n",
       "      <td>252</td>\n",
       "      <td>2</td>\n",
       "      <td>Mattie</td>\n",
       "      <td>Hoffman</td>\n",
       "      <td>mattie.hoffman@sakilacustomer.org</td>\n",
       "      <td>256</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14594</th>\n",
       "      <td>32097</td>\n",
       "      <td>263</td>\n",
       "      <td>1</td>\n",
       "      <td>15293</td>\n",
       "      <td>0.99</td>\n",
       "      <td>2007-05-14 13:44:29.996577</td>\n",
       "      <td>263</td>\n",
       "      <td>1</td>\n",
       "      <td>Hilda</td>\n",
       "      <td>Hopkins</td>\n",
       "      <td>hilda.hopkins@sakilacustomer.org</td>\n",
       "      <td>268</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14595</th>\n",
       "      <td>32098</td>\n",
       "      <td>264</td>\n",
       "      <td>2</td>\n",
       "      <td>14243</td>\n",
       "      <td>2.99</td>\n",
       "      <td>2007-05-14 13:44:29.996577</td>\n",
       "      <td>264</td>\n",
       "      <td>1</td>\n",
       "      <td>Gwendolyn</td>\n",
       "      <td>May</td>\n",
       "      <td>gwendolyn.may@sakilacustomer.org</td>\n",
       "      <td>269</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14596 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       payment_id  customer_id  staff_id  rental_id amount  \\\n",
       "0           17503          341         2       1520   7.99   \n",
       "1           17504          341         1       1778   1.99   \n",
       "2           17505          341         1       1849   7.99   \n",
       "3           17506          341         2       2829   2.99   \n",
       "4           17507          341         2       3130   7.99   \n",
       "...           ...          ...       ...        ...    ...   \n",
       "14591       32094          245         2      12682   2.99   \n",
       "14592       32095          251         1      14107   0.99   \n",
       "14593       32096          252         2      13756   4.99   \n",
       "14594       32097          263         1      15293   0.99   \n",
       "14595       32098          264         2      14243   2.99   \n",
       "\n",
       "                    payment_date  customer_id  store_id first_name last_name  \\\n",
       "0     2007-02-15 22:25:46.996577          341         1      Peter    Menard   \n",
       "1     2007-02-16 17:23:14.996577          341         1      Peter    Menard   \n",
       "2     2007-02-16 22:41:45.996577          341         1      Peter    Menard   \n",
       "3     2007-02-19 19:39:56.996577          341         1      Peter    Menard   \n",
       "4     2007-02-20 17:31:48.996577          341         1      Peter    Menard   \n",
       "...                          ...          ...       ...        ...       ...   \n",
       "14591 2007-05-14 13:44:29.996577          245         1   Courtney       Day   \n",
       "14592 2007-05-14 13:44:29.996577          251         2     Vickie    Brewer   \n",
       "14593 2007-05-14 13:44:29.996577          252         2     Mattie   Hoffman   \n",
       "14594 2007-05-14 13:44:29.996577          263         1      Hilda   Hopkins   \n",
       "14595 2007-05-14 13:44:29.996577          264         1  Gwendolyn       May   \n",
       "\n",
       "                                   email  address_id  activebool create_date  \\\n",
       "0        peter.menard@sakilacustomer.org         346        True  2006-02-14   \n",
       "1        peter.menard@sakilacustomer.org         346        True  2006-02-14   \n",
       "2        peter.menard@sakilacustomer.org         346        True  2006-02-14   \n",
       "3        peter.menard@sakilacustomer.org         346        True  2006-02-14   \n",
       "4        peter.menard@sakilacustomer.org         346        True  2006-02-14   \n",
       "...                                  ...         ...         ...         ...   \n",
       "14591    courtney.day@sakilacustomer.org         249        True  2006-02-14   \n",
       "14592   vickie.brewer@sakilacustomer.org         255        True  2006-02-14   \n",
       "14593  mattie.hoffman@sakilacustomer.org         256        True  2006-02-14   \n",
       "14594   hilda.hopkins@sakilacustomer.org         268        True  2006-02-14   \n",
       "14595   gwendolyn.may@sakilacustomer.org         269        True  2006-02-14   \n",
       "\n",
       "                  last_update  active  \n",
       "0     2013-05-26 14:49:45.738       1  \n",
       "1     2013-05-26 14:49:45.738       1  \n",
       "2     2013-05-26 14:49:45.738       1  \n",
       "3     2013-05-26 14:49:45.738       1  \n",
       "4     2013-05-26 14:49:45.738       1  \n",
       "...                       ...     ...  \n",
       "14591 2013-05-26 14:49:45.738       1  \n",
       "14592 2013-05-26 14:49:45.738       1  \n",
       "14593 2013-05-26 14:49:45.738       1  \n",
       "14594 2013-05-26 14:49:45.738       1  \n",
       "14595 2013-05-26 14:49:45.738       1  \n",
       "\n",
       "[14596 rows x 16 columns]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_3 = '''\n",
    "          SELECT * \n",
    "          FROM payment FULL OUTER JOIN customer\n",
    "          ON payment.customer_id = customer.customer_id\n",
    "          '''\n",
    "customer_and_payment = get_data(query_3, rows = 'all')\n",
    "customer_and_payment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## LEFT OUTER JOIN Statement\n",
    "\n",
    "Before explaning what the LEFT OUTER JOIN statement does, we must establish some naming conventions conventions. Notice that, when joining two tables, the JOIN statement appears right in between the names of the two tables. We call left table to the table whose names appears right before the JOIN statment and raght table to the another one.  \n",
    "\n",
    "The LEFT OUTER JOIN statement returns all the matching rows from both two tables plus the remaining rows form the left table. \n",
    "\n",
    "## Ex. 4\n",
    "\n",
    "Les't LEFT OUTER JOIN the film table with the inventory table on the film_id column and, in order to make our results more readable, we'll only ask for a few columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>film_id</th>\n",
       "      <th>title</th>\n",
       "      <th>inventory_id</th>\n",
       "      <th>store_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Academy Dinosaur</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Academy Dinosaur</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Academy Dinosaur</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Academy Dinosaur</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Academy Dinosaur</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>Academy Dinosaur</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>Academy Dinosaur</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>Academy Dinosaur</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2</td>\n",
       "      <td>Ace Goldfinger</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2</td>\n",
       "      <td>Ace Goldfinger</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   film_id             title  inventory_id  store_id\n",
       "0        1  Academy Dinosaur             1         1\n",
       "1        1  Academy Dinosaur             2         1\n",
       "2        1  Academy Dinosaur             3         1\n",
       "3        1  Academy Dinosaur             4         1\n",
       "4        1  Academy Dinosaur             5         2\n",
       "5        1  Academy Dinosaur             6         2\n",
       "6        1  Academy Dinosaur             7         2\n",
       "7        1  Academy Dinosaur             8         2\n",
       "8        2    Ace Goldfinger             9         2\n",
       "9        2    Ace Goldfinger            10         2"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_4 = '''\n",
    "          SELECT film.film_id, title, inventory_id, store_id \n",
    "          FROM film LEFT OUTER JOIN inventory\n",
    "          ON film.film_id = inventory.film_id\n",
    "          '''\n",
    "LOJ_film_inventory = get_data(query_4)\n",
    "LOJ_film_inventory"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ex. 5\n",
    "\n",
    "Since we're only displaying the first ten rows of the output, it is not very clear that the outpunt contains rows unique to the left table (film table). Taking into account that the inventory_id column is unique to the inventory table, we can get the rows unique to the film table by doing the following "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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>film_id</th>\n",
       "      <th>title</th>\n",
       "      <th>inventory_id</th>\n",
       "      <th>store_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>802</td>\n",
       "      <td>Sky Miracle</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>497</td>\n",
       "      <td>Kill Brotherhood</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>801</td>\n",
       "      <td>Sister Freddy</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>359</td>\n",
       "      <td>Gladiator Westward</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>325</td>\n",
       "      <td>Floats Garden</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>33</td>\n",
       "      <td>Apollo Teen</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>198</td>\n",
       "      <td>Crystal Breaking</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>419</td>\n",
       "      <td>Hocus Frida</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>332</td>\n",
       "      <td>Frankenstein Stranger</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>712</td>\n",
       "      <td>Raiders Antitrust</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   film_id                  title inventory_id store_id\n",
       "0      802            Sky Miracle         None     None\n",
       "1      497       Kill Brotherhood         None     None\n",
       "2      801          Sister Freddy         None     None\n",
       "3      359     Gladiator Westward         None     None\n",
       "4      325          Floats Garden         None     None\n",
       "5       33            Apollo Teen         None     None\n",
       "6      198       Crystal Breaking         None     None\n",
       "7      419            Hocus Frida         None     None\n",
       "8      332  Frankenstein Stranger         None     None\n",
       "9      712      Raiders Antitrust         None     None"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_5 = '''\n",
    "          SELECT film.film_id, title, inventory_id, store_id \n",
    "          FROM film LEFT OUTER JOIN inventory\n",
    "          ON film.film_id = inventory.film_id\n",
    "          WHERE inventory_id IS NULL\n",
    "          '''\n",
    "only_film_items = get_data(query_5)\n",
    "only_film_items "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that we're getting None values instead of Null values, this is because, at the moment of converting the SQL into a pandas DataFrame, pandas transforms changes the Null values for None values.\n",
    "\n",
    "## RIGHT OUTER JOIN Statement\n",
    "\n",
    "The RIGHT OUTER JOIN statement works in the exact same way as the LEFT OUTER JOIN statement, with the difference that, instead of returning the matching rows form both right and left tables plus the rows unique to the left table, it will return the matching rows plus the rows unique to the right table.\n",
    "\n",
    "# Ex. 6\n",
    "\n",
    "We can illustrate this by replacing the LEFT OUTER JOIN statment by the RIGHT OUTER JOIN statment and by switching around the table order in the code of the example four. By doing so, we must get the same result as in the example four"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>film_id</th>\n",
       "      <th>title</th>\n",
       "      <th>inventory_id</th>\n",
       "      <th>store_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>802</td>\n",
       "      <td>Sky Miracle</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>497</td>\n",
       "      <td>Kill Brotherhood</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>801</td>\n",
       "      <td>Sister Freddy</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>359</td>\n",
       "      <td>Gladiator Westward</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>325</td>\n",
       "      <td>Floats Garden</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>33</td>\n",
       "      <td>Apollo Teen</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>198</td>\n",
       "      <td>Crystal Breaking</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>419</td>\n",
       "      <td>Hocus Frida</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>332</td>\n",
       "      <td>Frankenstein Stranger</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>712</td>\n",
       "      <td>Raiders Antitrust</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   film_id                  title inventory_id store_id\n",
       "0      802            Sky Miracle         None     None\n",
       "1      497       Kill Brotherhood         None     None\n",
       "2      801          Sister Freddy         None     None\n",
       "3      359     Gladiator Westward         None     None\n",
       "4      325          Floats Garden         None     None\n",
       "5       33            Apollo Teen         None     None\n",
       "6      198       Crystal Breaking         None     None\n",
       "7      419            Hocus Frida         None     None\n",
       "8      332  Frankenstein Stranger         None     None\n",
       "9      712      Raiders Antitrust         None     None"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_6 = '''\n",
    "          SELECT film.film_id, title, inventory_id, store_id \n",
    "          FROM inventory RIGHT OUTER JOIN film\n",
    "          ON film.film_id = inventory.film_id\n",
    "          WHERE inventory_id IS NULL\n",
    "          '''\n",
    "only_film_items = get_data(query_6)\n",
    "only_film_items "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## UNION Statement\n",
    "\n",
    "The UNION statement is used to direclty concatenate the results of two SELECT statement. We have to keep in mind that the format of the tables should be such that it makes sense to stack one on top of another. That is, the tables must have the same number of columns, the columns must have similat datatypes, etc.\n",
    "\n",
    "## Ex. 7\n",
    "\n",
    "We can concatenate the first_name column form the actor table and the first_name column from the customer table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>first_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Warren</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Luis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Danny</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Amber</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ewan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Johnnie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Edward</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Cindy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Amy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Earl</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  first_name\n",
       "0     Warren\n",
       "1       Luis\n",
       "2      Danny\n",
       "3      Amber\n",
       "4       Ewan\n",
       "5    Johnnie\n",
       "6     Edward\n",
       "7      Cindy\n",
       "8        Amy\n",
       "9       Earl"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_7 = '''\n",
    "          SELECT first_name FROM actor\n",
    "          UNION\n",
    "          SELECT first_name FROM customer\n",
    "          '''\n",
    "union = get_data(query_7)\n",
    "union"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## To conclude this notebook we must close the connection to the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.3 64-bit ('sql': conda)",
   "language": "python",
   "name": "python38364bitsqlcondac46ff261eb2d405f8ac307a3c81d2935"
  },
  "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.8.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}