Db2 CROSS JOIN

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;
Code language: SQL (Structured Query Language) (sql)

Notice that unlike other join types like INNER JOIN, LEFT JOIN, RIGHT JOIN, or FULL JOIN, the CROSS JOIN does not have a join condition.

The 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 n and 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.

The CROSS JOIN can be useful for generating a large number of rows for performance testing purpose.

Db2 CROSS JOIN example

Let’s set up two tables t1 and 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;
Code language: SQL (Structured Query Language) (sql)

The following example uses the CROSS JOIN clause to join the t1 table to t2 table:

SELECT 
    c1, 
    c2
FROM 
    t1
CROSS JOIN t2;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Db2 CROSS JOIN common mistakes

The 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;
Code language: SQL (Structured Query Language) (sql)

However, if you forget the join_condition, the join becomes a CROSS JOIN:

SELECT  
    select_list
FROM T1, T2;
Code language: SQL (Structured Query Language) (sql)

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.