Set theory and SQL tables

Set theory is a branch of mathematics that discusses the definition of sets and the relationships between sets. It is mostly used as a logical framework for representing ideas about how we can classify things. Set theory is theoretical by its nature, but it has practical applications in fields such as machine learning and SQL. In this article, we will explore the connection between set theory and SQL table joins.

What is a set?

A set by its definition is a well-defined collection of objects, known as elements or members. Sets are used to represent collections of objects in mathematics, and they are defined by the objects that they contain.

For example, we have the set A = {1,2,3} we now have a set called A, and the members of this set are 1, 2, and 3. we can also give a short definition to this with the following syntax: A = { x | x ∈ ?1 AND x < 4 }. we simply said that we have a member x of group A such that x is a member of the natural number (starting from 1) and is less than 4. Of course that this is only the basis of the set theory but this example will help you to follow along with the examples that will connect this theory and the real-world implementation of SQL table joins.

Union - Full outer join

In set theory, we have an operator called "Union" and the mathematical symbol of such an operator is the ? sign. The result as a function from that operation is the following logic: C = A ? B, C = { x | x ∈ A OR x ∈ B }, and in words - "Any member of C is a member of A or a member of B" which in this case A and B are groups.

Let's say we have those two groups: A = {1,2,3}, B = {3,5,6} if we will implement the union operation we will get C = {1,2,3,4,5}, in this case, any member of C can be either a member of A or a member of B. Note that in a set we don't care about repetitive members, so the conclusion is that every member of a set is unique

So what about SQL? well, we have the same implementation in SQL and the same logic just like in set theory. In my perspective is that we can still filter the columns that will be "part of the game" which is the operation. The following image is an abstract example of the following operation in SQL.

The result that we will get (Filtered by the columns) all the rows of table1 and table2 that the result lets call it table3 will be rows of table1 or table2. Need to note that there are some RDBMs that support a "union" operation per se but the downfall is that the DBA or the developer is not in control of the columns so it's safer to use the full outer join so you can be sure you get only the relevant columns.

Intersection - Inner join

Another operation in set theory is the intersection one ?, don't confuse it with union because the syntax is pretty similar (Union starts with ?). Well for this operation the logic is the intersection of both groups. For example, let's take A = {1,2,3,4,5} and B = {3,4,6,8} the result after A ? B is C = {3,4} = { x | x ?∈ A (And) x ∈ B}.

And for SQL we will choose first the relevant columns (We can use all using *) and filter the rows where they match. In that case, we will get a new table that will contain values from A and B.

With this tool, we can apply statistics to filter only the rows that are applying a specific condition. Like "if else" etc.

Left join

In this case for set theory, there is a simple solution if we have groups A and B such that A = {1,2,3,4,5} and B = {1,4,7,3,9,45} if we take group A we will have some of the members of group B but we don't need to do any other operation. Is the same for SQL but then it's a bit different because you need to specify for table2 to get the rows where they match a condition from table1. In simple words take all from table1 and table2 take by a condition that is going to be dependent on table1

You can simply guess that the right join is the same as the left join but the other way around so we will skip that.

Symmetric difference - Join without intersection

In set theory, you can combine two groups A and B, and get all the members of A or B but in a condition such that the member of C (The combination) can't be from both, also known as the intersection. In simple words "Give me all from both, but the intersection of both". Let's say we have the groups A = {1,2,3,4}, B = {3,4,5,6} in that case C = {1,2,5,6}. We excluded {3,4} because they are our intersection points. The symbol for such a mathematical operation is A ? B?(Or A ? B, only a syntax).

As for SQL, we will retrieve all the rows from A and B but we will filter all the rows that match for a given condition.

We can apply the same logic to exclude only the A group or the B group. We will replace the "FULL JOIN" with "LEFT" or "RIGHT" joins for our given need. In that case, we will take all the rows that are members of A but if the member has any intersection with B we will exclude this row.

Summary

As we can see in the examples above we can take a very theoretical concept and apply it to some real-world tools and skills. I hope that this article will open your eyes to how wonderful and interesting math can be if you try to connect it to everyday life.

Here is a recap to set theory symbols and their meaning:

• A?B - Intersection (objects that belong to set A and set B)
• A?B - Union (objects that belong to set A or set B)
• A?B or A?B - Symmetric difference (objects that belong to A or B but not to their intersection)
• a ∈ A - Element of, belongs to.

Of course, there are way more symbols and more complex logic structures but in my point of view, this is a great overview of the connection between set theory and SQL.