{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## Joining Tables – Inner\n", "\n", "Let us understand how to join data from multiple tables." ] }, { "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": [ "* We will primarily focus on ANSI style join (**JOIN with ON**).\n", "* There are different types of joins.\n", " * INNER JOIN - Get all the records from both the datasets which satisfies JOIN condition.\n", " * OUTER JOIN - We will get into the details as part of the next topic\n", "* Example for INNER JOIN\n", "\n", "```sql\n", "SELECT o.order_id,\n", " o.order_date,\n", " o.order_status,\n", " oi.order_item_subtotal\n", "FROM orders o JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", "LIMIT 10\n", "```\n", "\n", "* We can join more than 2 tables in one query. Here is how it will look like.\n", "\n", "```sql\n", "SELECT o.order_id,\n", " o.order_date,\n", " o.order_status,\n", " oi.order_item_subtotal\n", "FROM orders o JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", " JOIN products p\n", " ON p.product_id = oi.order_item_product_id\n", "LIMIT 10\n", "```\n", "\n", "* If we have to apply additional filters, it is recommended to use WHERE clause. ON clause should only have join conditions.\n", "* We can have non equal join conditions as well, but they are not used that often.\n", "* Here are some of the examples for INNER JOIN:\n", " * Get order id, date, status and item revenue for all order items.\n", " * Get order id, date, status and item revenue for all order items for all orders where order status is either COMPLETE or CLOSED.\n", " * Get order id, date, status and item revenue for all order items for all orders where order status is either COMPLETE or CLOSED for the orders that are placed in the month of 2014 January." ] }, { "cell_type": "code", "execution_count": 52, "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": 53, "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": 54, "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", "
order_idorder_dateorder_statusorder_item_subtotal
12013-07-25 00:00:00CLOSED299.98
22013-07-25 00:00:00PENDING_PAYMENT199.99
22013-07-25 00:00:00PENDING_PAYMENT250.0
22013-07-25 00:00:00PENDING_PAYMENT129.99
42013-07-25 00:00:00CLOSED49.98
42013-07-25 00:00:00CLOSED299.95
42013-07-25 00:00:00CLOSED150.0
42013-07-25 00:00:00CLOSED199.92
52013-07-25 00:00:00COMPLETE299.98
52013-07-25 00:00:00COMPLETE299.95
" ], "text/plain": [ "[(1, datetime.datetime(2013, 7, 25, 0, 0), 'CLOSED', 299.98),\n", " (2, datetime.datetime(2013, 7, 25, 0, 0), 'PENDING_PAYMENT', 199.99),\n", " (2, datetime.datetime(2013, 7, 25, 0, 0), 'PENDING_PAYMENT', 250.0),\n", " (2, datetime.datetime(2013, 7, 25, 0, 0), 'PENDING_PAYMENT', 129.99),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 'CLOSED', 49.98),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 'CLOSED', 299.95),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 'CLOSED', 150.0),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 'CLOSED', 199.92),\n", " (5, datetime.datetime(2013, 7, 25, 0, 0), 'COMPLETE', 299.98),\n", " (5, datetime.datetime(2013, 7, 25, 0, 0), 'COMPLETE', 299.95)]" ] }, "execution_count": 54, "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_subtotal\n", "FROM orders o JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 55, "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
68883
" ], "text/plain": [ "[(68883,)]" ] }, "execution_count": 55, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT count(1) FROM orders" ] }, { "cell_type": "code", "execution_count": 56, "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": 56, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT count(1) FROM order_items" ] }, { "cell_type": "code", "execution_count": 57, "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": 57, "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": 58, "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", "
order_idorder_dateorder_statusorder_item_subtotal
12013-07-25 00:00:00CLOSED299.98
42013-07-25 00:00:00CLOSED49.98
42013-07-25 00:00:00CLOSED299.95
42013-07-25 00:00:00CLOSED150.0
42013-07-25 00:00:00CLOSED199.92
52013-07-25 00:00:00COMPLETE299.98
52013-07-25 00:00:00COMPLETE299.95
52013-07-25 00:00:00COMPLETE99.96
52013-07-25 00:00:00COMPLETE299.98
52013-07-25 00:00:00COMPLETE129.99
" ], "text/plain": [ "[(1, datetime.datetime(2013, 7, 25, 0, 0), 'CLOSED', 299.98),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 'CLOSED', 49.98),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 'CLOSED', 299.95),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 'CLOSED', 150.0),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 'CLOSED', 199.92),\n", " (5, datetime.datetime(2013, 7, 25, 0, 0), 'COMPLETE', 299.98),\n", " (5, datetime.datetime(2013, 7, 25, 0, 0), 'COMPLETE', 299.95),\n", " (5, datetime.datetime(2013, 7, 25, 0, 0), 'COMPLETE', 99.96),\n", " (5, datetime.datetime(2013, 7, 25, 0, 0), 'COMPLETE', 299.98),\n", " (5, datetime.datetime(2013, 7, 25, 0, 0), 'COMPLETE', 129.99)]" ] }, "execution_count": 58, "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_subtotal\n", "FROM orders o JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", "WHERE o.order_status IN ('COMPLETE', 'CLOSED')\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 59, "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
75408
" ], "text/plain": [ "[(75408,)]" ] }, "execution_count": 59, "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\n", "WHERE o.order_status IN ('COMPLETE', 'CLOSED')\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 60, "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", "
order_idorder_dateorder_statusorder_item_subtotal
258822014-01-01 00:00:00COMPLETE299.97
258822014-01-01 00:00:00COMPLETE100.0
258822014-01-01 00:00:00COMPLETE79.98
258822014-01-01 00:00:00COMPLETE399.98
258882014-01-01 00:00:00COMPLETE299.98
258892014-01-01 00:00:00COMPLETE99.96
258892014-01-01 00:00:00COMPLETE19.99
258912014-01-01 00:00:00CLOSED150.0
258912014-01-01 00:00:00CLOSED50.0
258912014-01-01 00:00:00CLOSED119.97
" ], "text/plain": [ "[(25882, datetime.datetime(2014, 1, 1, 0, 0), 'COMPLETE', 299.97),\n", " (25882, datetime.datetime(2014, 1, 1, 0, 0), 'COMPLETE', 100.0),\n", " (25882, datetime.datetime(2014, 1, 1, 0, 0), 'COMPLETE', 79.98),\n", " (25882, datetime.datetime(2014, 1, 1, 0, 0), 'COMPLETE', 399.98),\n", " (25888, datetime.datetime(2014, 1, 1, 0, 0), 'COMPLETE', 299.98),\n", " (25889, datetime.datetime(2014, 1, 1, 0, 0), 'COMPLETE', 99.96),\n", " (25889, datetime.datetime(2014, 1, 1, 0, 0), 'COMPLETE', 19.99),\n", " (25891, datetime.datetime(2014, 1, 1, 0, 0), 'CLOSED', 150.0),\n", " (25891, datetime.datetime(2014, 1, 1, 0, 0), 'CLOSED', 50.0),\n", " (25891, datetime.datetime(2014, 1, 1, 0, 0), 'CLOSED', 119.97)]" ] }, "execution_count": 60, "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_subtotal\n", "FROM orders o JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", "WHERE o.order_status IN ('COMPLETE', 'CLOSED')\n", " AND to_char(order_date, 'yyyy-MM') = '2014-01'\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 61, "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
6198
" ], "text/plain": [ "[(6198,)]" ] }, "execution_count": 61, "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\n", "WHERE o.order_status IN ('COMPLETE', 'CLOSED')\n", " AND to_char(order_date, 'yyyy-MM') = '2014-01'\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 }