4_Joins_DiagramsForHomework.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "# SQL Server Joins, as Explained By Dogs\r\n",
                "\r\n",
                "Course videos: https://www.red-gate.com/hub/university/courses/t-sql/tsql-for-beginners\r\n",
                "\r\n",
                "Course scripts: https://litknd.github.io/TSQLBeginners \r\n",
                "\r\n",
                "# Joins: an overview\r\n",
                "\r\n",
                "* INNER – Matching pairs\r\n",
                "* OUTER\r\n",
                "    * FULL  - All rows from each side, match where possible\r\n",
                "    * LEFT – All rows from the left, match right where possible\r\n",
                "    * RIGHT - All rows from the right, match left where possible\r\n",
                "* CROSS – Cartesian product (all possible combos)\r\n",
                "\r\n",
                "\r\n",
                "# Setup: Create a database\r\n",
                "\r\n",
                "Let's create a super-simple database to illustrate joins with some helpful doggo friends. 🐶🐕🐕‍🦺"
            ],
            "metadata": {
                "azdata_cell_guid": "fb4c36b6-8063-4608-a49c-8a74290fa104"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "--First, create the database\r\n",
                "USE master;\r\n",
                "GO\r\n",
                "\r\n",
                "IF DB_ID('joins') IS NOT NULL\r\n",
                "    DROP DATABASE joins;\r\n",
                "GO\r\n",
                "CREATE DATABASE joins;\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "a4177fb8-f428-4695-a0fa-bc025ebc3c5f",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.100"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 9
        },
        {
            "cell_type": "code",
            "source": [
                "--Create two (badly named) tables and insert a small amount of data\r\n",
                "--These names are used to keep the code short so it's easy to read on the images\r\n",
                "\r\n",
                "--Use the database \r\n",
                "USE [joins];\r\n",
                "GO\r\n",
                "\r\n",
                "--dbo.t1\r\n",
                "CREATE TABLE dbo.t1 (\r\n",
                "    doggos varchar(128)\r\n",
                ");\r\n",
                "GO\r\n",
                "\r\n",
                "INSERT dbo.t1 (doggos) VALUES \r\n",
                "    ('wat'),\r\n",
                "    ('wat'),\r\n",
                "    ('puppy'),\r\n",
                "    ('puppy'),\r\n",
                "    ('roll');\r\n",
                "GO\r\n",
                "\r\n",
                "--dbo.t2\r\n",
                "CREATE TABLE dbo.t2 (\r\n",
                "    doggos varchar(128)\r\n",
                ");\r\n",
                "GO\r\n",
                "\r\n",
                "INSERT dbo.t2 (doggos) VALUES \r\n",
                "    ('wat'),\r\n",
                "    ('roll'),\r\n",
                "    ('boop');\r\n",
                "GO\r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "78a2b2f2-5d66-4d50-b06a-44e2007df5ac",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(5 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(3 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.003"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 10
        },
        {
            "cell_type": "markdown",
            "source": [
                "# INNER JOIN\r\n",
                "Return all matching pairs from \"left\" table and \"right\" table \r\n",
                "![Inner Join](images/inner_join.png)"
            ],
            "metadata": {
                "azdata_cell_guid": "bd458e47-8d0f-4e29-a400-a535aa11c76f"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT t1.doggos,\r\n",
                "       t2.doggos\r\n",
                "FROM dbo.t1\r\n",
                "    INNER JOIN dbo.t2  \r\n",
                "        ON t1.doggos = t2.doggos;"
            ],
            "metadata": {
                "azdata_cell_guid": "85672e17-c297-44ff-b457-d4d5d5459b9f",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(3 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.004"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 11,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "doggos"
                                    },
                                    {
                                        "name": "doggos"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "wat",
                                    "1": "wat"
                                },
                                {
                                    "0": "wat",
                                    "1": "wat"
                                },
                                {
                                    "0": "roll",
                                    "1": "roll"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>doggos</th><th>doggos</th></tr>",
                            "<tr><td>wat</td><td>wat</td></tr>",
                            "<tr><td>wat</td><td>wat</td></tr>",
                            "<tr><td>roll</td><td>roll</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 11
        },
        {
            "cell_type": "markdown",
            "source": [
                "# LEFT OUTER JOIN\r\n",
                "\r\n",
                "Return all rows for the \"left\" table, including matches from the \"right\" table where possible\r\n",
                "\r\n",
                "![left outer join](images/left_outer_join.png)"
            ],
            "metadata": {
                "azdata_cell_guid": "053f4c36-237e-42cf-a93d-3dea012b885b"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "--Left outer join\r\n",
                "SELECT t1.doggos,\r\n",
                "       t2.doggos\r\n",
                "FROM dbo.t1\r\n",
                "    LEFT OUTER JOIN dbo.t2\r\n",
                "        ON t1.doggos = t2.doggos;\r\n",
                "GO\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "69784896-0e08-4371-a071-36ff7cbc526f",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(5 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.003"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 12,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "doggos"
                                    },
                                    {
                                        "name": "doggos"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "wat",
                                    "1": "wat"
                                },
                                {
                                    "0": "wat",
                                    "1": "wat"
                                },
                                {
                                    "0": "puppy",
                                    "1": "NULL"
                                },
                                {
                                    "0": "puppy",
                                    "1": "NULL"
                                },
                                {
                                    "0": "roll",
                                    "1": "roll"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>doggos</th><th>doggos</th></tr>",
                            "<tr><td>wat</td><td>wat</td></tr>",
                            "<tr><td>wat</td><td>wat</td></tr>",
                            "<tr><td>puppy</td><td>NULL</td></tr>",
                            "<tr><td>puppy</td><td>NULL</td></tr>",
                            "<tr><td>roll</td><td>roll</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 12
        },
        {
            "cell_type": "markdown",
            "source": [
                "# RIGHT OUTER JOIN\r\n",
                "\r\n",
                "Return all rows for the \"right\" table, including matches from the \"left\" table where possible\r\n",
                "\r\n",
                "![right outer join](images/right_outer_join.png)"
            ],
            "metadata": {
                "azdata_cell_guid": "f6d6c055-2487-4bd6-bab1-2733f64e9e86"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "--RIGHT OUTER JOIN\r\n",
                "SELECT t1.doggos,\r\n",
                "       t2.doggos\r\n",
                "FROM dbo.t1\r\n",
                "    RIGHT OUTER JOIN dbo.t2\r\n",
                "        ON t1.doggos = t2.doggos;\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "f0c448c1-6b02-4a9f-820e-a1672d545186",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(4 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.003"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 13,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "doggos"
                                    },
                                    {
                                        "name": "doggos"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "wat",
                                    "1": "wat"
                                },
                                {
                                    "0": "wat",
                                    "1": "wat"
                                },
                                {
                                    "0": "roll",
                                    "1": "roll"
                                },
                                {
                                    "0": "NULL",
                                    "1": "boop"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>doggos</th><th>doggos</th></tr>",
                            "<tr><td>wat</td><td>wat</td></tr>",
                            "<tr><td>wat</td><td>wat</td></tr>",
                            "<tr><td>roll</td><td>roll</td></tr>",
                            "<tr><td>NULL</td><td>boop</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 13
        },
        {
            "cell_type": "markdown",
            "source": [
                "# FULL OUTER JOIN aka FULL JOIN\r\n",
                "\r\n",
                "Return all rows from each \"side\", matching where possible\r\n",
                "\r\n",
                "![full outer join](images/full_outer_join.png)"
            ],
            "metadata": {
                "azdata_cell_guid": "2075a20c-59d0-45f1-bc93-f6fb62ea607e"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "--FULL JOIN\r\n",
                "SELECT t1.doggos,\r\n",
                "       t2.doggos\r\n",
                "FROM dbo.t1\r\n",
                "    FULL OUTER JOIN dbo.t2\r\n",
                "        ON t1.doggos = t2.doggos;\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "8886003a-8d66-4717-9f07-d377b4921c82",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(6 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.004"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 14,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "doggos"
                                    },
                                    {
                                        "name": "doggos"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "wat",
                                    "1": "wat"
                                },
                                {
                                    "0": "wat",
                                    "1": "wat"
                                },
                                {
                                    "0": "puppy",
                                    "1": "NULL"
                                },
                                {
                                    "0": "puppy",
                                    "1": "NULL"
                                },
                                {
                                    "0": "roll",
                                    "1": "roll"
                                },
                                {
                                    "0": "NULL",
                                    "1": "boop"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>doggos</th><th>doggos</th></tr>",
                            "<tr><td>wat</td><td>wat</td></tr>",
                            "<tr><td>wat</td><td>wat</td></tr>",
                            "<tr><td>puppy</td><td>NULL</td></tr>",
                            "<tr><td>puppy</td><td>NULL</td></tr>",
                            "<tr><td>roll</td><td>roll</td></tr>",
                            "<tr><td>NULL</td><td>boop</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 14
        },
        {
            "cell_type": "markdown",
            "source": [
                "# CROSS JOIN\r\n",
                "\r\n",
                "We're going to simplify the data a little for CROSS JOIN\r\n",
                "\r\n",
                "This is only so the picture wasn't TOO complicated 🙃"
            ],
            "metadata": {
                "azdata_cell_guid": "8db1b29c-73f6-42f2-9e43-6d9ab92b4911"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "--TRUNCATE TABLE clears all the data in the table: POOF!\r\n",
                "TRUNCATE TABLE dbo.t1;\r\n",
                "TRUNCATE TABLE dbo.t2;\r\n",
                "GO\r\n",
                "\r\n",
                "\r\n",
                "INSERT dbo.t1\r\n",
                "(\r\n",
                "    doggos\r\n",
                ")\r\n",
                "VALUES\r\n",
                "('wat'),\r\n",
                "('wat'),\r\n",
                "('puppy')\r\n",
                "GO\r\n",
                "\r\n",
                "INSERT dbo.t2\r\n",
                "(\r\n",
                "    doggos\r\n",
                ")\r\n",
                "VALUES\r\n",
                "('roll'),\r\n",
                "('boop')\r\n",
                "GO\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "48dcdb4c-b493-47c2-a14b-8cd28fee3d13",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(3 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(2 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.001"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 15
        },
        {
            "cell_type": "markdown",
            "source": [
                "![cross join](images/cross_join.png)"
            ],
            "metadata": {
                "azdata_cell_guid": "7f61bc5d-dbf3-4bbf-b32e-5c2dd24ad274"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "--CROSS join\r\n",
                "SELECT t1.doggos,\r\n",
                "       t2.doggos\r\n",
                "FROM dbo.t1\r\n",
                "    CROSS JOIN dbo.t2;\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "3592fcf5-0a64-454d-8f1a-5dfc6ee7278d",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(6 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.003"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 16,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "doggos"
                                    },
                                    {
                                        "name": "doggos"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "wat",
                                    "1": "roll"
                                },
                                {
                                    "0": "wat",
                                    "1": "roll"
                                },
                                {
                                    "0": "puppy",
                                    "1": "roll"
                                },
                                {
                                    "0": "wat",
                                    "1": "boop"
                                },
                                {
                                    "0": "wat",
                                    "1": "boop"
                                },
                                {
                                    "0": "puppy",
                                    "1": "boop"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>doggos</th><th>doggos</th></tr>",
                            "<tr><td>wat</td><td>roll</td></tr>",
                            "<tr><td>wat</td><td>roll</td></tr>",
                            "<tr><td>puppy</td><td>roll</td></tr>",
                            "<tr><td>wat</td><td>boop</td></tr>",
                            "<tr><td>wat</td><td>boop</td></tr>",
                            "<tr><td>puppy</td><td>boop</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 16
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Recap of joins\r\n",
                "\r\n",
                "* INNER – Matching pairs\r\n",
                "* OUTER\r\n",
                "    * FULL  - All rows from each side, match where possible\r\n",
                "    * LEFT – All rows from the left, match right where possible\r\n",
                "    * RIGHT - All rows from the right, match left where possible\r\n",
                "* CROSS – Cartesian product (all possible combos)\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "a5cb01e9-745d-4de3-bef4-763a84fe207e"
            }
        }
    ]
}