{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## Joining Tables - Outer\n", "\n", "Let us understand how to perform outer joins using SQL. There are 3 different types of outer joins." ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "tags": [ "remove-cell" ] }, "outputs": [ { "data": { "text/html": [ "\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%%HTML\n", "" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* `LEFT OUTER JOIN` (default) - Get all the records from both the datasets which satisfies JOIN condition along with those records which are in the left side table but not in the right side table.\n", "* `RIGHT OUTER JOIN` - Get all the records from both the datasets which satisfies JOIN condition along with those records which are in the right side table but not in the left side table.\n", "* `FULL OUTER JOIN` - left union right\n", "* When we perform the outer join (lets say left outer join), we will see this.\n", " * Get all the values from both the tables when join condition satisfies.\n", " * If there are rows on left side table for which there are no corresponding values in right side table, all the projected column values for right side table will be null.\n", "* Here are some of the examples for outer join.\n", " * Get all the orders where there are no corresponding order items.\n", " * Get all the order items where there are no corresponding orders." ] }, { "cell_type": "code", "execution_count": 62, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The sql extension is already loaded. To reload it, use:\n", " %reload_ext sql\n" ] } ], "source": [ "%load_ext sql" ] }, { "cell_type": "code", "execution_count": 63, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db\n" ] } ], "source": [ "%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db" ] }, { "cell_type": "code", "execution_count": 64, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "10 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
order_idorder_dateorder_statusorder_item_order_idorder_item_subtotal
12013-07-25 00:00:00CLOSED1299.98
22013-07-25 00:00:00PENDING_PAYMENT2129.99
22013-07-25 00:00:00PENDING_PAYMENT2250.0
22013-07-25 00:00:00PENDING_PAYMENT2199.99
32013-07-25 00:00:00COMPLETENoneNone
42013-07-25 00:00:00CLOSED4199.92
42013-07-25 00:00:00CLOSED4150.0
42013-07-25 00:00:00CLOSED4299.95
42013-07-25 00:00:00CLOSED449.98
52013-07-25 00:00:00COMPLETE5299.98
" ], "text/plain": [ "[(1, datetime.datetime(2013, 7, 25, 0, 0), 'CLOSED', 1, 299.98),\n", " (2, datetime.datetime(2013, 7, 25, 0, 0), 'PENDING_PAYMENT', 2, 129.99),\n", " (2, datetime.datetime(2013, 7, 25, 0, 0), 'PENDING_PAYMENT', 2, 250.0),\n", " (2, datetime.datetime(2013, 7, 25, 0, 0), 'PENDING_PAYMENT', 2, 199.99),\n", " (3, datetime.datetime(2013, 7, 25, 0, 0), 'COMPLETE', None, None),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 'CLOSED', 4, 199.92),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 'CLOSED', 4, 150.0),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 'CLOSED', 4, 299.95),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 'CLOSED', 4, 49.98),\n", " (5, datetime.datetime(2013, 7, 25, 0, 0), 'COMPLETE', 5, 299.98)]" ] }, "execution_count": 64, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT o.order_id,\n", " o.order_date,\n", " o.order_status,\n", " oi.order_item_order_id,\n", " oi.order_item_subtotal\n", "FROM orders o LEFT OUTER JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", "ORDER BY o.order_id\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 65, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
count
183650
" ], "text/plain": [ "[(183650,)]" ] }, "execution_count": 65, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT count(1)\n", "FROM orders o LEFT OUTER JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id" ] }, { "cell_type": "code", "execution_count": 66, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
count
172198
" ], "text/plain": [ "[(172198,)]" ] }, "execution_count": 66, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT count(1)\n", "FROM orders o JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id" ] }, { "cell_type": "code", "execution_count": 67, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "10 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
order_idorder_dateorder_statusorder_item_order_idorder_item_subtotal
32013-07-25 00:00:00COMPLETENoneNone
62013-07-25 00:00:00COMPLETENoneNone
222013-07-25 00:00:00COMPLETENoneNone
262013-07-25 00:00:00COMPLETENoneNone
322013-07-25 00:00:00COMPLETENoneNone
402013-07-25 00:00:00PENDING_PAYMENTNoneNone
472013-07-25 00:00:00PENDING_PAYMENTNoneNone
532013-07-25 00:00:00PROCESSINGNoneNone
542013-07-25 00:00:00PENDING_PAYMENTNoneNone
552013-07-25 00:00:00PENDINGNoneNone
" ], "text/plain": [ "[(3, datetime.datetime(2013, 7, 25, 0, 0), 'COMPLETE', None, None),\n", " (6, datetime.datetime(2013, 7, 25, 0, 0), 'COMPLETE', None, None),\n", " (22, datetime.datetime(2013, 7, 25, 0, 0), 'COMPLETE', None, None),\n", " (26, datetime.datetime(2013, 7, 25, 0, 0), 'COMPLETE', None, None),\n", " (32, datetime.datetime(2013, 7, 25, 0, 0), 'COMPLETE', None, None),\n", " (40, datetime.datetime(2013, 7, 25, 0, 0), 'PENDING_PAYMENT', None, None),\n", " (47, datetime.datetime(2013, 7, 25, 0, 0), 'PENDING_PAYMENT', None, None),\n", " (53, datetime.datetime(2013, 7, 25, 0, 0), 'PROCESSING', None, None),\n", " (54, datetime.datetime(2013, 7, 25, 0, 0), 'PENDING_PAYMENT', None, None),\n", " (55, datetime.datetime(2013, 7, 25, 0, 0), 'PENDING', None, None)]" ] }, "execution_count": 67, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT o.order_id,\n", " o.order_date,\n", " o.order_status,\n", " oi.order_item_order_id,\n", " oi.order_item_subtotal\n", "FROM orders o LEFT OUTER JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", "WHERE oi.order_item_order_id IS NULL\n", "ORDER BY o.order_id\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 68, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
count
11452
" ], "text/plain": [ "[(11452,)]" ] }, "execution_count": 68, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT count(1)\n", "FROM orders o LEFT OUTER JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", "WHERE oi.order_item_order_id IS NULL" ] }, { "cell_type": "code", "execution_count": 69, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
count
5189
" ], "text/plain": [ "[(5189,)]" ] }, "execution_count": 69, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT count(1)\n", "FROM orders o LEFT OUTER JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", "WHERE oi.order_item_order_id IS NULL\n", " AND o.order_status IN ('COMPLETE', 'CLOSED')" ] }, { "cell_type": "code", "execution_count": 70, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "10 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
order_idorder_dateorder_statusorder_item_order_idorder_item_subtotal
12013-07-25 00:00:00CLOSED1299.98
22013-07-25 00:00:00PENDING_PAYMENT2199.99
22013-07-25 00:00:00PENDING_PAYMENT2250.0
22013-07-25 00:00:00PENDING_PAYMENT2129.99
42013-07-25 00:00:00CLOSED449.98
42013-07-25 00:00:00CLOSED4299.95
42013-07-25 00:00:00CLOSED4150.0
42013-07-25 00:00:00CLOSED4199.92
52013-07-25 00:00:00COMPLETE5299.98
52013-07-25 00:00:00COMPLETE5299.95
" ], "text/plain": [ "[(1, datetime.datetime(2013, 7, 25, 0, 0), 'CLOSED', 1, 299.98),\n", " (2, datetime.datetime(2013, 7, 25, 0, 0), 'PENDING_PAYMENT', 2, 199.99),\n", " (2, datetime.datetime(2013, 7, 25, 0, 0), 'PENDING_PAYMENT', 2, 250.0),\n", " (2, datetime.datetime(2013, 7, 25, 0, 0), 'PENDING_PAYMENT', 2, 129.99),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 'CLOSED', 4, 49.98),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 'CLOSED', 4, 299.95),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 'CLOSED', 4, 150.0),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 'CLOSED', 4, 199.92),\n", " (5, datetime.datetime(2013, 7, 25, 0, 0), 'COMPLETE', 5, 299.98),\n", " (5, datetime.datetime(2013, 7, 25, 0, 0), 'COMPLETE', 5, 299.95)]" ] }, "execution_count": 70, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT o.order_id,\n", " o.order_date,\n", " o.order_status,\n", " oi.order_item_order_id,\n", " oi.order_item_subtotal\n", "FROM orders o RIGHT OUTER JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 71, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
count
172198
" ], "text/plain": [ "[(172198,)]" ] }, "execution_count": 71, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT count(1)\n", "FROM orders o RIGHT OUTER JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id" ] }, { "cell_type": "code", "execution_count": 72, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "0 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
order_idorder_dateorder_statusorder_item_order_idorder_item_subtotal
" ], "text/plain": [ "[]" ] }, "execution_count": 72, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT o.order_id,\n", " o.order_date,\n", " o.order_status,\n", " oi.order_item_order_id,\n", " oi.order_item_subtotal\n", "FROM orders o RIGHT OUTER JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", "WHERE o.order_id IS NULL\n", "LIMIT 10" ] } ], "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.6.12" } }, "nbformat": 4, "nbformat_minor": 4 }