cartesian_product

View All Public Scripts Login to Run Script

Script Name cartesian_product
Visibility Unlisted - anyone with the share link can access
Description Demonstration of (lack of) effect of DISTINCT on Cartesian product for StackOverflow question https://stackoverflow.com/q/46131913/146325
Area SQL General / SQL Query
Contributor Andrew Clarke
Created Saturday September 09, 2017

Statement 1

create table t1 (id number, col_a number)

Table created.

Statement 2

create table t2 (id number, col_b number)

Table created.

Statement 3

insert into t1 values (10, 2)

1 row(s) inserted.

Statement 4

insert into t1 values (20, 2)

1 row(s) inserted.

Statement 5

insert into t2 values (10, 3)

1 row(s) inserted.

Statement 6

insert into t2 values (20, 4)

1 row(s) inserted.

Statement 7

The cross join produces a Cartesian product

select t1.id as t1_id, t1.col_a, t2.id as t2_id, t2.col_b   
from t1 cross join t2    
order by t1.col_a, t2.col_b


T1_ID COL_A T2_ID COL_B
20 2 10 3
10 2 10 3
20 2 20 4
10 2 20 4

4 rows selected.

T1_ID	COL_A	T2_ID	COL_B
20	2	10	3
10	2	10	3
20	2	20	4
10	2	20	4

Statement 8

the Cartesian product consists of a combination of unique records so DISTINCT doesn't have an effect

select distinct t1.id as t1_id, t1.col_a, t2.id as t2_id, t2.col_b   
from t1 cross join t2    
order by t1.col_a, t2.col_b


T1_ID COL_A T2_ID COL_B
10 2 10 3
20 2 10 3
10 2 20 4
20 2 20 4

4 rows selected.

T1_ID	COL_A	T2_ID	COL_B
10	2	10	3
20	2	10	3
10	2	20	4
20	2	20	4

Statement 10

The cross join still produces a Cartesian product

select t1.id as t1_id, t1.col_a, t2.id as t2_id, t2.col_b   
from t1 cross join t2    
order by t1.col_a, t2.col_b


T1_ID COL_A T2_ID COL_B
10 2 10 3
10 2 10 3
20 2 10 3
20 2 10 3
20 2 20 4
10 2 20 4

6 rows selected.

T1_ID	COL_A	T2_ID	COL_B
10	2	10	3
10	2	10	3
20	2	10	3
20	2	10	3
20	2	20	4
10	2	20	4

Statement 11

... and this time DISTINCT does filter the result set

select distinct t1.id as t1_id, t1.col_a, t2.id as t2_id, t2.col_b   
from t1 cross join t2    
order by t1.col_a, t2.col_b


T1_ID COL_A T2_ID COL_B
10 2 10 3
20 2 10 3
10 2 20 4
20 2 20 4

4 rows selected.

T1_ID	COL_A	T2_ID	COL_B
10	2	10	3
20	2	10	3
10	2	20	4
20	2	20	4

Statement 12

The result set with a join on ID

select t1.id as t1_id, t1.col_a, t2.id as t2_id, t2.col_b   
from t1 inner join t2  on t1.id  = t2.id   
order by t1.col_a, t2.col_b


T1_ID COL_A T2_ID COL_B
10 2 10 3
10 2 10 3
20 2 20 4

3 rows selected.

T1_ID	COL_A	T2_ID	COL_B
10	2	10	3
10	2	10	3
20	2	20	4

Additional Information