Summary: in this tutorial, you will learn how to use the Db2 cross join to make a Cartesian product of two sets.
Introduction to Db2
CROSS JOIN clause
Here is the syntax of the
CROSS JOIN of two tables in Db2:
CROSS JOIN T2;
CROSS JOIN combines every row of the first table (
T1) with every row of the second table (
T2). It returns a result set that includes the combination of every row in both tables. If the joined tables have
m rows, the
CROSS JOIN will return
nxm rows. This result set is also known as a Cartesian product.
The result becomes huge even with a small number of rows in each table e.g., the result set will have 1 million rows if the each joined table has only 1,000 rows.
CROSS JOIN can be useful for generating a large number of rows for performance testing purpose.
CROSS JOIN example
Let’s set up two tables
t2 for the demonstration.
CREATE TABLE t1
c1 INT NOT NULL
INSERT INTO t1(c1)
CREATE TABLE t2
c2 CHAR(1) NOT NULL
INSERT INTO t2(c2)
The following example uses the
CROSS JOIN clause to join the
t1 table to
CROSS JOIN t2;
Here is the output:
CROSS JOIN common mistakes
CROSS JOIN clause should be used with extra cautious due to its resource consumption and huge volume rows returned.
A common mistake that when you join a table to another table using the join condition in the
WHERE clause as shown in the following query:
FROM T1, T2
However, if you forget the join_condition, the join becomes a
FROM T1, T2;
Therefore, you should avoid writing the join using this style as much as possible.
In this tutorial, you have learned how to use the Db2
CROSS JOIN clause to make a Cartesian product of two result sets.