同一张表上的DB笛卡尔积

Question

我有一个单列表，有三行，如下所示:

col1

team1
team2
team3
team4

我想做一个自笛卡尔连接，结果如下:
team1, team2
team1, team3
team1, team4
team2, team3
team2, team4
team3, team4

Answer 1

cartesian product在DB术语中是cross join，您可以删除where子句中团队相等的行:

select
    t1.col1, t2.col1
from teams as t1
    cross join teams as t2
where
    t1.col1 <> t2.col1

Answer 2

您可以像这样将两个表连接在一起以获得预期的输出:

select t1.col1, t2.col1
from table t1
join table t2
on t1.col1 <> t2.col1