MariaDB 关键字

MariaDB CROSS JOIN 关键字用于返回两个表(表 1 和表 2)中的所有记录。它有时被称为CARTESIAN JOIN,因为在没有 WHERE 条件的情况下,它的行为类似于笛卡尔积,即结果集中的行数是两个表的行数的乘积。

MariaDB CROSS JOIN关键字

语法

语法在 MariaDB 中使用 CROSS JOIN 关键字的方法如下:

SELECT table1.column1, table1.column2, table2.column1, table2.column2, ...
FROM table1
CROSS JOIN table2; 
注意:CROSS JOIN 可能会返回非常大的结果集。

示例:

考虑名为 Employee 和 Contact_Info 的数据库表,其中包含以下记录:

表 1:Employee 

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

表 2:Contact_Info 表

Phone_NumberEmpIDAddressGender
+1-80540980002Brooklyn, New York, USAF
+33-1479961013Grenelle, Paris, FranceM
+31-2011503194Geuzenveld, Amsterdam, NetherlandsF
+86-10997324586Yizhuangzhen, Beijing, ChinaM
+65-672348247Yishun, SingaporeM
+81-3577990728Koto City, Tokyo, JapanM

要交叉连接 Employee 和 Contact_Info 表,考虑 Name 和 Age 列以及 Contact_Info 表中的 Address 列,如下可以使用查询:

SELECT Employee.Name, Employee.Age, Contact_Info.Address 
FROM Employee
CROSS JOIN Contact_Info; 

这将产生如下所示的结果(请注意,产生的结果包含两个表中记录集的每种可能的组合):

NameAgeAddress
John25Brooklyn, New York, USA
John25Grenelle, Paris, France
John25Geuzenveld, Amsterdam, Netherlands
John25Yizhuangzhen, Beijing, China
John25Yishun, Singapore
John25Koto City, Tokyo, Japan
Marry24Brooklyn, New York, USA
Marry24Grenelle, Paris, France
Marry24Geuzenveld, Amsterdam, Netherlands
Marry24Yizhuangzhen, Beijing, China
Marry24Yishun, Singapore
Marry24Koto City, Tokyo, Japan
Jo27Brooklyn, New York, USA
Jo27Grenelle, Paris, France
Jo27Geuzenveld, Amsterdam, Netherlands
Jo27Yizhuangzhen, Beijing, China
Jo27Yishun, Singapore
Jo27Koto City, Tokyo, Japan
Kim30Brooklyn, New York, USA
Kim30Grenelle, Paris, France
Kim30Geuzenveld, Amsterdam, Netherlands
Kim30Yizhuangzhen, Beijing, China
Kim30Yishun, Singapore
Kim30Koto City, Tokyo, Japan
Ramesh28Brooklyn, New York, USA
Ramesh28Grenelle, Paris, France
Ramesh28Geuzenveld, Amsterdam, Netherlands
Ramesh28Yizhuangzhen, Beijing, China
Ramesh28Yishun, Singapore
Ramesh28Koto City, Tokyo, Japan
Huang28Brooklyn, New York, USA
Huang28Grenelle, Paris, France
Huang28Geuzenveld, Amsterdam, Netherlands
Huang28Yizhuangzhen, Beijing, China
Huang28Yishun, Singapore
Huang28Koto City, Tokyo, Japan

在 CROSS JOIN 中使用 WHERE / ON 子句

添加 WHERE / ON 子句(指定两个表之间的关系),CROSS JOIN 将产生与 INNER JOIN 子句。

语法

使用 CROSS JOIN 的语法MariaDB 中的 WHERE / ON 子句如下:

/* 使用ON子句 */
SELECT table1.column1, table1.column2, table2.column1, table2.column2, ...
FROM table1
CROSS JOIN table2
ON table1.matching_column = table2.matching_column;

/* 使用WHERE子句 */
SELECT table1.column1, table1.column2, table2.column1, table2.column2, ...
FROM table1
CROSS JOIN table2
WHERE table1.matching_column = table2.matching_column; 

示例:

考虑上面讨论的数据库表 Employee 和 Contact_Info.

根据匹配列交叉连接 Employee 和 Contact_Info 表 EmpID,可以使用以下查询:

SELECT Employee.Name, Employee.Age, Contact_Info.Address 
FROM Employee
CROSS JOIN Contact_Info
ON Employee.EmpID = Contact_Info.EmpID; 

这将产生如下所示的结果:

NameAgeAddress
Marry24Brooklyn, New York, USA
Jo27Grenelle, Paris, France
Kim30Geuzenveld, Amsterdam, Netherlands
Huang28Yizhuangzhen, Beijing, China