When joining two relations via use of, say,
LEFT joins, it is very important to understand the ramifications of such an undertaking.
These kind of join are powerful and easy to implement due to their friendly syntactic sugar and are used all the time by SQL professionals to great effect.
However, it is not uncommon to see these used improperly. This page will advise on and against the various use cases of cartesian joins.
For each of our examples on this page, we will consider the following two relations: and :
In SQL, we use
CROSS JOIN to compute the cartesian product of two relations.
That is, we compute all possible combinations of tuples.
We can mathematically represent the cartesian product of two relations as follows:
Where represents the cartesian product.
CROSS JOIN in SQL, we would write something like the following:
SELECT * FROM R CROSS JOIN S
Note that unlike
INNER JOIN, we aren’t matching on anything.
As you can see, our SQL query has returned tuples. The result-set of a cartesian product will always be of length , where and represent the respective lengths of the two relations.
In relational algebra, we represent an
INNER JOIN as follows:
In other words, we again compute a cartesian product but return only the tuples that match on the joining fields.
The syntax for
INNER JOIN in SQL resembles something like this:
SELECT alpha , beta FROM R INNER JOIN S ON R.alpha = S.beta
In this case we have returned just the one tuple. But
INNER JOIN can return any number of tuples in the range . It just depends on the number of matching tuples.
Let’s break this down.
So it is more or less accurate to state that a Left Join is the union between an inner join and a left anti semi join.
As such, we could write our SQL like this to return the product of a left join:
WITH cteInner AS ( SELECT * FROM R INNER JOIN S ON R.alpha = S.beta ) , cteAntiJoin AS ( SELECT * FROM R.alpha WHERE NOT EXISTS ( SELECT 1 FROM cteInner WHERE R.alpha = alpha) ) SELECT * FROM cteInner UNION ALL SELECT * FROM cteAntiJoin CROSS JOIN (SELECT NULL AS omega);
But with some syntactic sugar, we need only write:
SELECT * FROM R LEFT JOIN S ON R.alpha = S.beta