SQL: Playing with CROSS JOIN

Last Updated on 14 October 2019 by Roberto De Pedrini

A type of join that is little used and considered of lower rank than the other joins is the CROSS JOIN. This operation is used to create all possible combinations of each row between two (or more) tables; it is certainly an option to be used with extreme attention to the cardinality of the tables involved.

The lines expected from a cross join are given by the product of the rows of the two (or more) tables participating in the join; imagine if by mistake we cross a table of 1,000,000 rows with a table of 2,000,000 rows: the result is a dataset of 2,000,000,000,000 rows.

Precisely for this reason we try to avoid the design of select that produce Cartesian products (Cross Join) because, depending on the number of rows involved, they can create serious problems for the DB.

An example to clarify how to use the CJ: a deck of cards

The cross join helps us to create all the combinations of seeds and values

The syntax to invoke the cross join can be indifferent one of these:

UNPIVOT

Anyone will have ever seen a table so designed, to the detriment of 1NF: an array embedded in a table row:

A table that therefore, for each customer / year, lists a value for each month.

The problem is therefore to normalize then bring the 12 columns back in line. Other DBs have pivot / unpivot functions but on db2 (waiting for the unpivot, and pivot, maybe) it can be done in different ways.

Let’s start by reconstructing the example table and populate it with a few lines

the approach chosen in this post is the creation of views but nothing prevents you from using the SQL statement underlying the view

Unpivot with use of the UNION

A first approach, traditional, functional, less performing than the others, not very elegant for me, is the use of union:

produces a correct dataset:

Unpivot with Cross Join and auxiliary table

The use of an auxiliary table helps us to create the list of months:

the table will then contain

With the Cross Join you can get what you want:

in fact 12 lines (months) are created for each row of the master table and, with the <CASE month> clause we value the Value column with the corresponding month value.

Unpivot with Cross Join without auxiliary table

Without having to create an auxiliary table the dataset corresponding to the FAQ400.MESI can be generated through the VAUES (from 6.1)

Unpivot with Cross Join and connect by

Using recursion it is also possible to use connect by to create a dataset that lists the 12 months: