Sets

13.4. Sets#

13.4.1. Basics#

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. Given set A = {L, M, N, O, P} and set B = {P, Q, R, S, T}, the four operations will return

  1. A UNION B = {L, M, N, O, P, Q, R, S, T}

  2. A UNION ALL = {L, M, N, O, P, P, Q, R, S, T} – Note the two Ps

  3. A INTERSECT B = {P}

  4. A EXCEPT B = {L, M N, O}

When doing set operations, you should use the same column names so you can order the results.

13.4.2. The UNION ALL and UNION Operator#

SELECT 
    'Teacher' typ,
    t.first_name
FROM Teachers t
WHERE t.first_name LIKE 'Jo%'
UNION ALL
SELECT 
    'Student' typ,
    s.first_name
FROM Students s
WHERE s.first_name LIKE 'Jo%'
Hide code cell source 
import sqlite3
import pandas as pd

conn = sqlite3.connect('students_teachers.db')

sql_statement = """
SELECT 
    'Teacher' typ,
    t.first_name
FROM Teachers t
WHERE t.first_name LIKE 'Jo%'
UNION ALL
SELECT 
    'Student' typ,
    s.first_name
FROM Students s
WHERE s.first_name LIKE 'Jo%'
"""
df = pd.read_sql_query(sql_statement, conn)
df.style.set_table_attributes('style="font-size: 12px"')

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[1], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('students_teachers.db')
      6 sql_statement = """
      7 SELECT 
      8     'Teacher' typ,
   (...)     17 WHERE s.first_name LIKE 'Jo%'
     18 """

ModuleNotFoundError: No module named 'pandas'

SELECT 
    t.first_name
FROM Teachers t
WHERE t.first_name LIKE 'Jo%'
UNION ALL
SELECT 
    s.first_name
FROM Students s
WHERE s.first_name LIKE 'Jo%'
ORDER BY first_name
Hide code cell source 
import sqlite3
import pandas as pd

conn = sqlite3.connect('students_teachers.db')

sql_statement = """
SELECT 
    t.first_name
FROM Teachers t
WHERE t.first_name LIKE 'Jo%'
UNION ALL
SELECT 
    s.first_name
FROM Students s
WHERE s.first_name LIKE 'Jo%'
ORDER BY first_name
"""
df = pd.read_sql_query(sql_statement, conn)
df.style.set_table_attributes('style="font-size: 12px"')

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[2], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('students_teachers.db')
      6 sql_statement = """
      7 SELECT 
      8     t.first_name
   (...)     16 ORDER BY first_name
     17 """

ModuleNotFoundError: No module named 'pandas'

SELECT 
    t.first_name
FROM Teachers t
WHERE t.first_name LIKE 'Jo%'
UNION 
SELECT 
    s.first_name
FROM Students s
WHERE s.first_name LIKE 'Jo%'
ORDER BY first_name
Hide code cell source 
import sqlite3
import pandas as pd

conn = sqlite3.connect('students_teachers.db')

sql_statement = """
SELECT 
    t.first_name
FROM Teachers t
WHERE t.first_name LIKE 'Jo%'
UNION
SELECT 
    s.first_name
FROM Students s
WHERE s.first_name LIKE 'Jo%'
ORDER BY first_name
"""
df = pd.read_sql_query(sql_statement, conn)
df.style.set_table_attributes('style="font-size: 12px"')

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[3], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('students_teachers.db')
      6 sql_statement = """
      7 SELECT 
      8     t.first_name
   (...)     16 ORDER BY first_name
     17 """

ModuleNotFoundError: No module named 'pandas'

13.4.3. The INTERSECT Operator#

SELECT 
    t.first_name
FROM Teachers t
WHERE t.first_name LIKE 'Jo%'
INTERSECT 
SELECT 
    s.first_name
FROM Students s
WHERE s.first_name LIKE 'Jo%'
ORDER BY first_name
Hide code cell source 
import sqlite3
import pandas as pd

conn = sqlite3.connect('students_teachers.db')

sql_statement = """
SELECT 
    t.first_name
FROM Teachers t
WHERE t.first_name LIKE 'Jo%'
INTERSECT 
SELECT 
    s.first_name
FROM Students s
WHERE s.first_name LIKE 'Jo%'
ORDER BY first_name
"""
df = pd.read_sql_query(sql_statement, conn)
df.style.set_table_attributes('style="font-size: 12px"')

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[4], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('students_teachers.db')
      6 sql_statement = """
      7 SELECT 
      8     t.first_name
   (...)     16 ORDER BY first_name
     17 """

ModuleNotFoundError: No module named 'pandas'

13.4.4. The EXCEPT Operator#

SELECT 
    t.first_name
FROM Teachers t
WHERE t.first_name LIKE 'Jo%'
EXCEPT 
SELECT 
    s.first_name
FROM Students s
WHERE s.first_name LIKE 'Jo%'
ORDER BY first_name
Hide code cell source 
import sqlite3
import pandas as pd

conn = sqlite3.connect('students_teachers.db')

sql_statement = """
SELECT 
    t.first_name
FROM Teachers t
WHERE t.first_name LIKE 'Jo%'
EXCEPT
SELECT 
    s.first_name
FROM Students s
WHERE s.first_name LIKE 'Jo%'
ORDER BY first_name
"""
df = pd.read_sql_query(sql_statement, conn)
df.style.set_table_attributes('style="font-size: 12px"')

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[5], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('students_teachers.db')
      6 sql_statement = """
      7 SELECT 
      8     t.first_name
   (...)     16 ORDER BY first_name
     17 """

ModuleNotFoundError: No module named 'pandas'
Contents