{ "cells": [ { "cell_type": "markdown", "id": "af2e89d6", "metadata": {}, "source": [ "# Sets\n", "\n", "## Basics\n", "SQL supports four types of operations `UNION`, `UNION ALL`, `INTERSECT`, and `EXCEPT`. The difference between `UNION` and `UNION ALL` is that the latter includes duplicates. \n", "Given set A = {L, M, N, O, P} and set B = {P, Q, R, S, T}, the four operations will return\n", "1. A UNION B = {L, M, N, O, P, Q, R, S, T}\n", "2. A UNION ALL = {L, M, N, O, P, P, Q, R, S, T} -- Note the two Ps\n", "3. A INTERSECT B = {P}\n", "4. A EXCEPT B = {L, M N, O}\n", "\n", "When doing set operations, you should use the same column names so you can order the results. \n", "\n", "## The `UNION ALL` and `UNION` Operator\n", "\n", "```SQL\n", "SELECT \n", " 'Teacher' typ,\n", " t.first_name\n", "FROM Teachers t\n", "WHERE t.first_name LIKE 'Jo%'\n", "UNION ALL\n", "SELECT \n", " 'Student' typ,\n", " s.first_name\n", "FROM Students s\n", "WHERE s.first_name LIKE 'Jo%'\n", "```" ] }, { "cell_type": "code", "execution_count": 1, "id": "84114a8b", "metadata": { "tags": [ "hide-input", "output_scroll" ] }, "outputs": [ { "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
 typfirst_name
0TeacherJohn
1TeacherJoseph
2TeacherJorge
3TeacherJonathan
4TeacherJonathan
5TeacherJoseph
6TeacherJoshua
7TeacherJoshua
8TeacherJorge
9TeacherJoseph
10TeacherJohn
11TeacherJoseph
12TeacherJoseph
13TeacherJohn
14TeacherJonathan
15TeacherJohn
16TeacherJohn
17TeacherJohn
18TeacherJoshua
19TeacherJordan
20TeacherJoshua
21TeacherJohn
22TeacherJohnny
23TeacherJonathan
24TeacherJoseph
25TeacherJonathan
26TeacherJoshua
27TeacherJordan
28TeacherJordan
29TeacherJordan
30TeacherJohn
31TeacherJohn
32StudentJohn
33StudentJon
34StudentJohn
35StudentJoshua
36StudentJose
37StudentJoshua
38StudentJoseph
39StudentJoan
40StudentJoan
41StudentJohn
42StudentJohnny
43StudentJoel
44StudentJohn
45StudentJohn
46StudentJoseph
47StudentJohnny
48StudentJoseph
49StudentJonathan
50StudentJohn
51StudentJoseph
52StudentJoseph
53StudentJohn
\n" ], "text/plain": [ "" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "conn = sqlite3.connect('students_teachers.db')\n", "\n", "sql_statement = \"\"\"\n", "SELECT \n", " 'Teacher' typ,\n", " t.first_name\n", "FROM Teachers t\n", "WHERE t.first_name LIKE 'Jo%'\n", "UNION ALL\n", "SELECT \n", " 'Student' typ,\n", " s.first_name\n", "FROM Students s\n", "WHERE s.first_name LIKE 'Jo%'\n", "\"\"\"\n", "df = pd.read_sql_query(sql_statement, conn)\n", "df.style.set_table_attributes('style=\"font-size: 12px\"')" ] }, { "cell_type": "markdown", "id": "cdcec7c6", "metadata": {}, "source": [ "```SQL\n", "SELECT \n", " t.first_name\n", "FROM Teachers t\n", "WHERE t.first_name LIKE 'Jo%'\n", "UNION ALL\n", "SELECT \n", " s.first_name\n", "FROM Students s\n", "WHERE s.first_name LIKE 'Jo%'\n", "ORDER BY first_name\n", "```" ] }, { "cell_type": "code", "execution_count": 2, "id": "1e41acc9", "metadata": { "tags": [ "hide-input", "output_scroll" ] }, "outputs": [ { "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
 first_name
0Joan
1Joan
2Joel
3John
4John
5John
6John
7John
8John
9John
10John
11John
12John
13John
14John
15John
16John
17John
18John
19Johnny
20Johnny
21Johnny
22Jon
23Jonathan
24Jonathan
25Jonathan
26Jonathan
27Jonathan
28Jonathan
29Jordan
30Jordan
31Jordan
32Jordan
33Jorge
34Jorge
35Jose
36Joseph
37Joseph
38Joseph
39Joseph
40Joseph
41Joseph
42Joseph
43Joseph
44Joseph
45Joseph
46Joseph
47Joshua
48Joshua
49Joshua
50Joshua
51Joshua
52Joshua
53Joshua
\n" ], "text/plain": [ "" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "conn = sqlite3.connect('students_teachers.db')\n", "\n", "sql_statement = \"\"\"\n", "SELECT \n", " t.first_name\n", "FROM Teachers t\n", "WHERE t.first_name LIKE 'Jo%'\n", "UNION ALL\n", "SELECT \n", " s.first_name\n", "FROM Students s\n", "WHERE s.first_name LIKE 'Jo%'\n", "ORDER BY first_name\n", "\"\"\"\n", "df = pd.read_sql_query(sql_statement, conn)\n", "df.style.set_table_attributes('style=\"font-size: 12px\"')" ] }, { "cell_type": "markdown", "id": "afb56a7c", "metadata": {}, "source": [ "```SQL\n", "SELECT \n", " t.first_name\n", "FROM Teachers t\n", "WHERE t.first_name LIKE 'Jo%'\n", "UNION \n", "SELECT \n", " s.first_name\n", "FROM Students s\n", "WHERE s.first_name LIKE 'Jo%'\n", "ORDER BY first_name\n", "```" ] }, { "cell_type": "code", "execution_count": 3, "id": "78768333", "metadata": { "tags": [ "hide-input", "output_scroll" ] }, "outputs": [ { "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", "
 first_name
0Joan
1Joel
2John
3Johnny
4Jon
5Jonathan
6Jordan
7Jorge
8Jose
9Joseph
10Joshua
\n" ], "text/plain": [ "" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "conn = sqlite3.connect('students_teachers.db')\n", "\n", "sql_statement = \"\"\"\n", "SELECT \n", " t.first_name\n", "FROM Teachers t\n", "WHERE t.first_name LIKE 'Jo%'\n", "UNION\n", "SELECT \n", " s.first_name\n", "FROM Students s\n", "WHERE s.first_name LIKE 'Jo%'\n", "ORDER BY first_name\n", "\"\"\"\n", "df = pd.read_sql_query(sql_statement, conn)\n", "df.style.set_table_attributes('style=\"font-size: 12px\"')" ] }, { "cell_type": "markdown", "id": "929f2d3d", "metadata": {}, "source": [ "## The `INTERSECT` Operator\n", "\n", "```SQL\n", "SELECT \n", " t.first_name\n", "FROM Teachers t\n", "WHERE t.first_name LIKE 'Jo%'\n", "INTERSECT \n", "SELECT \n", " s.first_name\n", "FROM Students s\n", "WHERE s.first_name LIKE 'Jo%'\n", "ORDER BY first_name\n", "```" ] }, { "cell_type": "code", "execution_count": 4, "id": "602e1c16", "metadata": { "tags": [ "hide-input", "output_scroll" ] }, "outputs": [ { "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", "
 first_name
0John
1Johnny
2Jonathan
3Joseph
4Joshua
\n" ], "text/plain": [ "" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "conn = sqlite3.connect('students_teachers.db')\n", "\n", "sql_statement = \"\"\"\n", "SELECT \n", " t.first_name\n", "FROM Teachers t\n", "WHERE t.first_name LIKE 'Jo%'\n", "INTERSECT \n", "SELECT \n", " s.first_name\n", "FROM Students s\n", "WHERE s.first_name LIKE 'Jo%'\n", "ORDER BY first_name\n", "\"\"\"\n", "df = pd.read_sql_query(sql_statement, conn)\n", "df.style.set_table_attributes('style=\"font-size: 12px\"')" ] }, { "cell_type": "markdown", "id": "5c644b26", "metadata": {}, "source": [ "## The `EXCEPT` Operator\n", "\n", "```SQL\n", "SELECT \n", " t.first_name\n", "FROM Teachers t\n", "WHERE t.first_name LIKE 'Jo%'\n", "EXCEPT \n", "SELECT \n", " s.first_name\n", "FROM Students s\n", "WHERE s.first_name LIKE 'Jo%'\n", "ORDER BY first_name\n", "```" ] }, { "cell_type": "code", "execution_count": 5, "id": "0452858a", "metadata": { "tags": [ "hide-input", "output_scroll" ] }, "outputs": [ { "data": { "text/html": [ "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
 first_name
0Jordan
1Jorge
\n" ], "text/plain": [ "" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "conn = sqlite3.connect('students_teachers.db')\n", "\n", "sql_statement = \"\"\"\n", "SELECT \n", " t.first_name\n", "FROM Teachers t\n", "WHERE t.first_name LIKE 'Jo%'\n", "EXCEPT\n", "SELECT \n", " s.first_name\n", "FROM Students s\n", "WHERE s.first_name LIKE 'Jo%'\n", "ORDER BY first_name\n", "\"\"\"\n", "df = pd.read_sql_query(sql_statement, conn)\n", "df.style.set_table_attributes('style=\"font-size: 12px\"')" ] } ], "metadata": { "jupytext": { "formats": "md:myst", "text_representation": { "extension": ".md", "format_name": "myst" } }, "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.9.7" }, "source_map": [ 11, 41, 63, 79, 100, 117, 138, 156, 177, 196 ] }, "nbformat": 4, "nbformat_minor": 5 }