Set Theory in SQL: UNION, INTERSECT, and EXCEPT

In the third chapter of DataCamp’s “Joining Data in SQL” course, I explored the fascinating world of Set Theory in SQL. Here’s a comprehensive overview of the key concepts, complete with examples and explanations.

Introduction to Venn Diagrams and Set Theory

Set theory is a fundamental concept in mathematics, often visualized using Venn diagrams. In SQL, set theory operations help manipulate and combine data from multiple tables. Here are the core concepts:

Table 1: students

student_id	name
1	Alice
2	Bob
3	Carol
4	Dave

Table 2: enrolled_students

student_id	course
1	Math
2	Science
4	Art
5	History

1. UNION

The UNION operator combines the result sets of two or more SELECT statements and removes duplicates.

Example:

SELECT student_id, name FROM students
UNION
SELECT student_id, course FROM enrolled_students;

Result:

student_id	name
1	Alice
2	Bob
3	Carol
4	Dave
4	Art
5	History

2. UNION ALL

The UNION ALL operator combines the result sets of two or more SELECT statements without removing duplicates.

Example:

SELECT student_id, name FROM students
UNION ALL
SELECT student_id, course FROM enrolled_students;

Result:

student_id	name
1	Alice
2	Bob
3	Carol
4	Dave
1	Math
2	Science
4	Art
5	History

3. INTERSECT

The INTERSECT operator returns the common records between two SELECT statements.

Example:

SELECT student_id FROM students
INTERSECT
SELECT student_id FROM enrolled_students;

Result:

student_id
1
2
4

4. INTERSECT vs INNER JOIN

While INTERSECT returns common rows from two SELECT statements, INNER JOIN combines rows from two tables based on a related column. Here’s a more detailed comparison using the same tables:

INTERSECT Example:

SELECT student_id, name FROM students
INTERSECT
SELECT student_id, course FROM enrolled_students;

Result:

student_id	name
1	Alice
2	Bob
4	Dave

INNER JOIN Example:

SELECT students.student_id, students.name, enrolled_students.course
FROM students
INNER JOIN enrolled_students
ON students.student_id = enrolled_students.student_id;

Result:

student_id	name	course
1	Alice	Math
2	Bob	Science
4	Dave	Art

The key difference is that INTERSECT only returns the common student_id values and their associated columns from each SELECT statement, whereas INNER JOIN returns all columns from both tables for the matched student_id values, allowing for more comprehensive data retrieval.

5. EXCEPT

The EXCEPT operator returns the rows from the first SELECT statement that are not present in the second SELECT statement.

Example:

SELECT student_id FROM students
EXCEPT
SELECT student_id FROM enrolled_students;

Result:

student_id
3

Summary Table

Concept	Description	Example
UNION	Combines result sets and removes duplicates	`SELECT ... UNION SELECT ...`
UNION ALL	Combines result sets without removing duplicates	`SELECT ... UNION ALL SELECT ...`
INTERSECT	Returns common records between two SELECT statements	`SELECT ... INTERSECT SELECT ...`
INTERSECT vs INNER JOIN	INTERSECT returns common rows, INNER JOIN combines rows based on a related column	`SELECT ... INTERSECT SELECT ...` vs `INNER JOIN`
EXCEPT	Returns rows from the first SELECT not in the second	`SELECT ... EXCEPT SELECT ...`

I’ve found learning set theory in SQL both challenging and rewarding. It’s like solving a puzzle where you fit different pieces together to see the bigger picture. These operations, UNION, INTERSECT, and EXCEPT, have made me appreciate how powerful and flexible SQL can be.

Despite the busy end-of-year schedule filled with student evaluations, state exams, teacher assessments, organizing remedial classes and summer camps, and end-of-year concerts, I remain committed to my learning journey. Balancing these responsibilities with raising my rapidly growing child (and enduring the inevitable sleep deprivation all new parents face), I persevere in studying and applying new concepts daily. My ultimate goal is to transition to a software engineer role. Although I don’t plan to delve deeply into SQL for a career as a data scientist, I am confident that I now have the essential SQL foundations needed to revisit and build upon this knowledge in more advanced software development courses.