{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# JOINS\n", "---\n", "\n", "A `join` clause in SQL is used to combine rows from two or more tables, based on a related column between them." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Connecting with the database" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%load_ext sql\n", "# replace with your own credentials\n", "%sql ibm_db_sa://my-username:my-password@dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net:50000/BLUDB" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Tables on which all the join operations will be applied are as follows :" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB\n", "Done.\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", "
countryfirst_namelast_nametest_score
MexicoNatashaMartinax48
MexicoRachaelGrate45
RomaniaAlexandraBotez57
RomaniaAlinaSmolyar52
" ], "text/plain": [ "[('Mexico', 'Natasha', 'Martinax', 48),\n", " ('Mexico', 'Rachael', 'Grate', 45),\n", " ('Romania', 'Alexandra', 'Botez', 57),\n", " ('Romania', 'Alina', 'Smolyar', 52)]" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from NEW_STUDENTS" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB\n", "Done.\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", "
countryfirst_namelast_nametest_score
RussiaBailGoodwin46
SwedenEmlynErricker55
PolandBarnyIngerson57
RomaniaAlinaSmolyar52
" ], "text/plain": [ "[('Russia', 'Bail', 'Goodwin', 46),\n", " ('Sweden', 'Emlyn', 'Erricker', 55),\n", " ('Poland', 'Barny', 'Ingerson', 57),\n", " ('Romania', 'Alina', 'Smolyar', 52)]" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from SPORTS_STUDENTS" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 1. INNER JOIN\n", "Inner join creates a new result table by combining cloumn values of two tables (A and B) based upon the join-predicate. It is similar to cross join or cartesian product." ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB\n", "Done.\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
countryfirst_namelast_nametest_scorecountry_1first_name_1last_name_1test_score_1
MexicoNatashaMartinax48RussiaBailGoodwin46
MexicoRachaelGrate45RussiaBailGoodwin46
RomaniaAlexandraBotez57RussiaBailGoodwin46
RomaniaAlinaSmolyar52RussiaBailGoodwin46
MexicoNatashaMartinax48SwedenEmlynErricker55
MexicoRachaelGrate45SwedenEmlynErricker55
RomaniaAlexandraBotez57SwedenEmlynErricker55
RomaniaAlinaSmolyar52SwedenEmlynErricker55
MexicoNatashaMartinax48PolandBarnyIngerson57
MexicoRachaelGrate45PolandBarnyIngerson57
RomaniaAlexandraBotez57PolandBarnyIngerson57
RomaniaAlinaSmolyar52PolandBarnyIngerson57
MexicoNatashaMartinax48RomaniaAlinaSmolyar52
MexicoRachaelGrate45RomaniaAlinaSmolyar52
RomaniaAlexandraBotez57RomaniaAlinaSmolyar52
RomaniaAlinaSmolyar52RomaniaAlinaSmolyar52
" ], "text/plain": [ "[('Mexico', 'Natasha', 'Martinax', 48, 'Russia', 'Bail', 'Goodwin', 46),\n", " ('Mexico', 'Rachael', 'Grate', 45, 'Russia', 'Bail', 'Goodwin', 46),\n", " ('Romania', 'Alexandra', 'Botez', 57, 'Russia', 'Bail', 'Goodwin', 46),\n", " ('Romania', 'Alina', 'Smolyar', 52, 'Russia', 'Bail', 'Goodwin', 46),\n", " ('Mexico', 'Natasha', 'Martinax', 48, 'Sweden', 'Emlyn', 'Erricker', 55),\n", " ('Mexico', 'Rachael', 'Grate', 45, 'Sweden', 'Emlyn', 'Erricker', 55),\n", " ('Romania', 'Alexandra', 'Botez', 57, 'Sweden', 'Emlyn', 'Erricker', 55),\n", " ('Romania', 'Alina', 'Smolyar', 52, 'Sweden', 'Emlyn', 'Erricker', 55),\n", " ('Mexico', 'Natasha', 'Martinax', 48, 'Poland', 'Barny', 'Ingerson', 57),\n", " ('Mexico', 'Rachael', 'Grate', 45, 'Poland', 'Barny', 'Ingerson', 57),\n", " ('Romania', 'Alexandra', 'Botez', 57, 'Poland', 'Barny', 'Ingerson', 57),\n", " ('Romania', 'Alina', 'Smolyar', 52, 'Poland', 'Barny', 'Ingerson', 57),\n", " ('Mexico', 'Natasha', 'Martinax', 48, 'Romania', 'Alina', 'Smolyar', 52),\n", " ('Mexico', 'Rachael', 'Grate', 45, 'Romania', 'Alina', 'Smolyar', 52),\n", " ('Romania', 'Alexandra', 'Botez', 57, 'Romania', 'Alina', 'Smolyar', 52),\n", " ('Romania', 'Alina', 'Smolyar', 52, 'Romania', 'Alina', 'Smolyar', 52)]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from NEW_STUDENTS inner join SPORTS_STUDENTS on True" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2. OUTER JOIN" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### • Left outer join\n", "The result of a left outer join for tables A and B always contain all the records of the `left` table (A), even if the join condition does not find any matching record in the `right` table (B). " ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB\n", "Done.\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", "
countryfirst_namelast_nametest_scorecountry_1first_name_1last_name_1test_score_1
RomaniaAlexandraBotez57PolandBarnyIngerson57
RomaniaAlinaSmolyar52RomaniaAlinaSmolyar52
MexicoNatashaMartinax48NoneNoneNoneNone
MexicoRachaelGrate45NoneNoneNoneNone
" ], "text/plain": [ "[('Romania', 'Alexandra', 'Botez', 57, 'Poland', 'Barny', 'Ingerson', 57),\n", " ('Romania', 'Alina', 'Smolyar', 52, 'Romania', 'Alina', 'Smolyar', 52),\n", " ('Mexico', 'Natasha', 'Martinax', 48, None, None, None, None),\n", " ('Mexico', 'Rachael', 'Grate', 45, None, None, None, None)]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from NEW_STUDENTS left outer join SPORTS_STUDENTS on NEW_STUDENTS.test_score=SPORTS_STUDENTS.test_score" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### • Right outer join\n", "Every row from the `right` table (B) will appear in the joined table atleast once. If no matching row from the `left` table (A) exists, NULL will appear in the columns from A for those records that have no match on B." ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB\n", "Done.\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", "
countryfirst_namelast_nametest_scorecountry_1first_name_1last_name_1test_score_1
RomaniaAlexandraBotez57PolandBarnyIngerson57
RomaniaAlinaSmolyar52RomaniaAlinaSmolyar52
NoneNoneNoneNoneSwedenEmlynErricker55
NoneNoneNoneNoneRussiaBailGoodwin46
" ], "text/plain": [ "[('Romania', 'Alexandra', 'Botez', 57, 'Poland', 'Barny', 'Ingerson', 57),\n", " ('Romania', 'Alina', 'Smolyar', 52, 'Romania', 'Alina', 'Smolyar', 52),\n", " (None, None, None, None, 'Sweden', 'Emlyn', 'Erricker', 55),\n", " (None, None, None, None, 'Russia', 'Bail', 'Goodwin', 46)]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from NEW_STUDENTS right outer join SPORTS_STUDENTS on NEW_STUDENTS.test_score=SPORTS_STUDENTS.test_score" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### • Full outer join\n", "The full outer join combines the result of both left and right outer join and returns all rows from both the tables." ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB\n", "Done.\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", "
countryfirst_namelast_nametest_scorecountry_1first_name_1last_name_1test_score_1
RomaniaAlexandraBotez57PolandBarnyIngerson57
RomaniaAlinaSmolyar52RomaniaAlinaSmolyar52
NoneNoneNoneNoneSwedenEmlynErricker55
NoneNoneNoneNoneRussiaBailGoodwin46
MexicoNatashaMartinax48NoneNoneNoneNone
MexicoRachaelGrate45NoneNoneNoneNone
" ], "text/plain": [ "[('Romania', 'Alexandra', 'Botez', 57, 'Poland', 'Barny', 'Ingerson', 57),\n", " ('Romania', 'Alina', 'Smolyar', 52, 'Romania', 'Alina', 'Smolyar', 52),\n", " (None, None, None, None, 'Sweden', 'Emlyn', 'Erricker', 55),\n", " (None, None, None, None, 'Russia', 'Bail', 'Goodwin', 46),\n", " ('Mexico', 'Natasha', 'Martinax', 48, None, None, None, None),\n", " ('Mexico', 'Rachael', 'Grate', 45, None, None, None, None)]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from NEW_STUDENTS full outer join SPORTS_STUDENTS on NEW_STUDENTS.test_score=SPORTS_STUDENTS.test_score" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Thanks for reading :)\n", "Created by [Tarun Kamboj](https://www.linkedin.com/in/kambojtarun/)." ] } ], "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.7.6" } }, "nbformat": 4, "nbformat_minor": 4 }