create table t1 (id number, col_a number)
Table created.
create table t2 (id number, col_b number)
Table created.
insert into t1 values (10, 2)
1 row(s) inserted.
insert into t1 values (20, 2)
1 row(s) inserted.
insert into t2 values (10, 3)
1 row(s) inserted.
insert into t2 values (20, 4)
1 row(s) inserted.
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 |
|---|
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 |
|---|
insert into t2 values (10, 3)
1 row(s) inserted.
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 |
|---|
... 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 |
|---|
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 |
|---|