Joins 可以将多个关系的数据合并到一起。CROSS JOIN返回两个关系到笛卡尔积(所有排列组合的集合)。CROSS JOIN可以通过如下两种方式来指定:

  • 显式的使用CROSS JOIN语法;
  • FROM子句中指定多个关系;

下列两个 SQL 语句是等价的:

--- 显式的使用`CROSS JOIN`语法
SELECT *
FROM nation
CROSS JOIN region;

--- 在`FROM`子句中指定多个关系
SELECT *
FROM nation, region;

示例:

表 nation 包括 25 行记录,表 region 包括 5 行,对这两个表执行 cross join 操作,其结果集为 125 行记录。

SELECT n.name AS nation, r.name AS region
FROM nation AS n
CROSS JOIN region AS r
ORDER BY 1, 2;

     nation     |   region
----------------+-------------
 ALGERIA        | AFRICA
 ALGERIA        | AMERICA
 ALGERIA        | ASIA
 ALGERIA        | EUROPE
 ALGERIA        | MIDDLE EAST
 ARGENTINA      | AFRICA
 ARGENTINA      | AMERICA
...
(125 rows)

如果参与 join 的表中包含相同名称的列,则需要用表名(或别名)加以修饰。

示例:

--- 正确
SELECT nation.name, region.name
FROM nation
CROSS JOIN region;

--- 正确
SELECT n.name, r.name
FROM nation AS n
CROSS JOIN region AS r;

--- 正确
SELECT n.name, r.name
FROM nation n
CROSS JOIN region r;

--- 错误,会抛出"Column 'name' is ambiguous"
SELECT name
FROM nation
CROSS JOIN region;