Cartesian product using SELF JOINs and filters
Let’s say we want to select a panel of 2 authors,
author2, out of all available authors. In that case, a query to get all combinations of names of author1 and author2, will look something like the query below.
Notice that we are applying a cartesian product on the table
authors with itself. So, this essentially is a cartesian product over a
SELF JOIN, which works the same way as it works in a standard JOIN.
However, observe that the results are not exactly what we want. There are cases where both
author2 are the same. This is bound to be in a SELF JOIN, because there is no check restricting the same. A filter in addition to the existing query can solve this problem.
This goes on to explain that like any other JOIN, we can always add a filter/set of filters to results of a cartesian product.