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"')
  typ first_name
0 Teacher John
1 Teacher Joseph
2 Teacher Jorge
3 Teacher Jonathan
4 Teacher Jonathan
5 Teacher Joseph
6 Teacher Joshua
7 Teacher Joshua
8 Teacher Jorge
9 Teacher Joseph
10 Teacher John
11 Teacher Joseph
12 Teacher Joseph
13 Teacher John
14 Teacher Jonathan
15 Teacher John
16 Teacher John
17 Teacher John
18 Teacher Joshua
19 Teacher Jordan
20 Teacher Joshua
21 Teacher John
22 Teacher Johnny
23 Teacher Jonathan
24 Teacher Joseph
25 Teacher Jonathan
26 Teacher Joshua
27 Teacher Jordan
28 Teacher Jordan
29 Teacher Jordan
30 Teacher John
31 Teacher John
32 Student John
33 Student Jon
34 Student John
35 Student Joshua
36 Student Jose
37 Student Joshua
38 Student Joseph
39 Student Joan
40 Student Joan
41 Student John
42 Student Johnny
43 Student Joel
44 Student John
45 Student John
46 Student Joseph
47 Student Johnny
48 Student Joseph
49 Student Jonathan
50 Student John
51 Student Joseph
52 Student Joseph
53 Student John 
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"')
  first_name
0 Joan
1 Joan
2 Joel
3 John
4 John
5 John
6 John
7 John
8 John
9 John
10 John
11 John
12 John
13 John
14 John
15 John
16 John
17 John
18 John
19 Johnny
20 Johnny
21 Johnny
22 Jon
23 Jonathan
24 Jonathan
25 Jonathan
26 Jonathan
27 Jonathan
28 Jonathan
29 Jordan
30 Jordan
31 Jordan
32 Jordan
33 Jorge
34 Jorge
35 Jose
36 Joseph
37 Joseph
38 Joseph
39 Joseph
40 Joseph
41 Joseph
42 Joseph
43 Joseph
44 Joseph
45 Joseph
46 Joseph
47 Joshua
48 Joshua
49 Joshua
50 Joshua
51 Joshua
52 Joshua
53 Joshua 
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"')
  first_name
0 Joan
1 Joel
2 John
3 Johnny
4 Jon
5 Jonathan
6 Jordan
7 Jorge
8 Jose
9 Joseph
10 Joshua

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"')
  first_name
0 John
1 Johnny
2 Jonathan
3 Joseph
4 Joshua

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"')
  first_name
0 Jordan
1 Jorge
Contents