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:
SELECT select_list FROM T1 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) VALUES(1),(2),(3); CREATE TABLE t2 ( c2 CHAR(1) NOT NULL ); INSERT INTO t2(c2) VALUES('A'),('B'),('C'); SELECT c1 FROM t1; SELECT c2 FROM t2;
The following example uses the
CROSS JOIN clause to join the
t1 table to
SELECT c1, c2 FROM t1 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:
SELECT select_list FROM T1, T2 WHERE join_condition;
However, if you forget the join_condition, the join becomes a
SELECT select_list 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.