MySQL系列教程 --- 75、MySQL 中的 交叉连接

MySQL CROSS JOIN 用于组合两个或多个表的所有可能性，并返回包含所有贡献表中每一行的结果。CROSS JOIN 也称为 CARTESIAN JOIN，它提供所有关联表的笛卡尔积。笛卡尔积可以解释为第一个表中的所有行乘以第二个表中的所有行。它类似于内部联接，其中联接条件不适用于此子句。

我们可以通过下面的可视化表示来理解它，其中 CROSS JOIN 返回来自 table1 和 table2 的所有记录，并且每一行是两个表的行的组合。

MySQL 交叉连接语法

CROSS JOIN 关键字始终与 SELECT 语句一起使用，并且必须写在 FROM 子句之后。以下语法从两个连接表中获取所有记录：

SELECT column-lists  
FROM table1  
CROSS JOIN table2;  

在上面的语法中，column-lists 是要返回的列或字段的名称，table1 和 table2 是从中获取记录的表名。

MySQL 交叉连接示例

让我们举一些例子来理解 Left Join 或 Left Outer Join 子句的工作：

用于连接两个表的 CROSS JOIN 子句

在这里，我们将创建两个包含以下数据的表“customers”和“contacts”：

Table: customers

Table: contacts

要从两个表中获取所有记录，请执行以下查询：

SELECT *  
FROM customers  
CROSS JOIN contacts;  

成功执行查询后，它将给出以下输出：

当 CROSS JOIN 语句执行时，您将观察到它显示 42 行。这意味着客户表中的七行乘以联系人表中的六行。

注意：为了避免重复列两次的结果，建议使用单个列名而不是 SELECT * 语句。

MySQL CROSS JOIN 中的歧义列问题

有时，我们需要从多个表中获取选定的列记录。这些表可以包含一些类似的列名。在这种情况下，MySQL CROSS JOIN 语句会抛出错误：列名不明确。这意味着列的名称存在于两个表中，并且 MySQL 对您要显示的列感到困惑。下面的例子更清楚地解释了它：

SELECT customer_id, cust_name, income, order_id, price  
FROM customer  
CROSS JOIN orders;  

上面的 CROSS JOIN 抛出一个错误，如下图所示：

这个问题可以通过在列名之前使用表名来解决。上面的查询可以重写为：

SELECT customer.customer_id, customer.cust_name, customer.income, orders.order_id, orders.price  
FROM customer  
CROSS JOIN orders;  

执行上述查询后，我们将得到以下输出：

用 WHERE 子句左连接

WHERE 子句用于从表中返回过滤结果。以下示例使用 CROSS JOIN 子句说明了这一点：

SELECT customers.customer_id, customers.cust_name, customers.income, orders.order_id, orders.price  
FROM customers  
CROSS JOIN orders  
USING(customer_id) WHERE price>1500 AND price<5000;  

该语句给出了以下结果：

MySQL CROSS JOIN 多表

我们已经创建了两个名为“ customers”和“ orders”的表。让我们再创建一张表并将其命名为“ contacts”，其中包含以下数据：

在这里，我们将使用三个表来解释 CROSS JOIN 和 LEFT JOIN。执行以下语句将customers、orders和contacts这三个表连接起来。在该语句中，先完成订单和联系人之间的CROSS JOIN，然后根据指定条件执行LEFT JOIN。

SELECT * FROM customer  
LEFT JOIN(orders CROSS JOIN contacts)  
ON customer.customer_id=contact_id  
ORDER BY income;  

成功执行上述查询后，它将给出以下输出：