In this article about Algebra Relational, we will cover a very rare in SQL operation called division. This operator has not an equivalent implementation in SQL. However, there is a technique to implement division in SQL, and we will explain here.
Relational Algebra: Division, what is it?
As any other algebra, relational algebra has operations (like addition subtraction multiplication division in numeric algebra). The fundamental operations are Selection and Projection. We will base the following examples on a table country with columns name, population, language.
Suppose we want the countries with a population higher than 30 million. In Algebra Relational we use the Selection operator in the following form:
SELECTION population > 30.000.000 (COUNTRY)
Let’s see the equivalent in SQL:
The next simple operation in Algebra Relational is the Projection, suppose we want the names and language of the countries, we can do it with the following Algebra Relational expression:
PROJECTION name, language (COUNTRY)
Let’s see the equivalent in SQL using TeamSQL:
The cartesian product is a relational algebra operation that is present in SQL but not too much used. We use a Cartesian product between 2 tables or relations; the form of the result is by all the records of one table combined with all the records of the other table. So the number of records in the result of the cartesian product is enormous and inefficient to calculate. This is the reason because we don’t use it too much in SQL, however as Relational Algebra is a more technical language, we can use it, without any adverse effect.
We can do it even the Cartesian product of one table with itself. Let’s see an example:
CARTESIAN_PRODUCT (COUNTRY, COUNTRY)
In SQL the cartesian product is straightforward to write, we only need to put both tables in the FROM clause and don’t put any JOIN condition in the WHERE or the ON clause. In TeamSQL the previous cartesian product looks like:
Due to space reasons, we show only the first records in the result, but you can appreciate the combination of France record with all records, (even with itself). Then, as we have nine countries in our database, we will have 81 countries as the result of the cartesian product.
The Question is…
Now is time to leave Relational Algebra for a while and go back to SQL. Is the cartesian product important for a SQL developer? May I apply the cartesian product any time in a SQL business query ?. Let’s see, suppose we want to obtain pairs of countries which speaks the same language, we can do the following query:
Moreover, we can try to obtain pair of countries speaking different languages. Or if we have a BORDER table, we can search pairs of limiting countries where different languages are spoken. In all these cases, we need to use a Cartesian product.
We review some operations of the Relational Algebra like SELECTION, PROJECTION, and CARTESIAN PRODUCT, in the next article, we will cover another process of the relational algebra called DIVISION, it is exciting and implement in SQL is not easy, but as we like challenges, we will do it. Let’s continue learning SQL, and applying with TeamSQL!