MySQL - Cross Join 交叉连接

MySQL Cross Join 交叉连接
使用交叉连接连接多个表
使用客户端程序进行交叉连接

MySQL Cross Join 交叉连接

MySQL 交叉连接 将第一个表的每一行与第二个表的每一行合并。它是一种基本的内连接类型，用于检索两个独立表(即排列)的笛卡尔积(或叉积)。

笛卡尔积(或叉积)是两个集合相乘的结果。这是通过将两个集合中所有可能的对相乘来实现的。

下图以简单的方式说明了交叉连接。

语法

以下是 MySQL 中交叉连接查询的基本语法 -

SELECT column_name(s)
FROM table1
CROSS JOIN table2

示例

在此交叉连接示例中，我们首先创建一个名为 CUSTOMERS 的表，其中包含客户的个人信息，包括姓名、年龄、地址和工资等。

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

现在使用 INSERT 语句将值插入到该表中，如下所示 -

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 );

该表将创建为 −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00

让我们创建另一个表 ORDERS，其中包含已下订单的详细信息及其下单日期。

CREATE TABLE ORDERS (
    OID INT NOT NULL,
    DATE VARCHAR (20) NOT NULL,
    CUSTOMER_ID INT NOT NULL,
    AMOUNT DECIMAL (18, 2),
);

使用 INSERT 语句，将值插入此表，如下所示 -

INSERT INTO ORDERS VALUES 
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00);

表显示如下 −

OID	DATE	CUSTOMER_ID	AMOUNT
100	2009-10-08 00:00:00	3	1500.00
101	2009-11-20 00:00:00	2	1560.00

现在，如果我们对上面给出的两个表执行以下交叉连接查询，交叉连接会将 CUSTOMERS 表中的每一行与 ORDERS 表中的每一行合并。

SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
CROSS JOIN ORDERS;

输出

结果表如下 -

ID	NAME	AMOUNT	DATE
2	Khilan	1500.00	2009-10-08 00:00:00
1	Ramesh	1560	2009-11-20 00:00:00
2	Khilan	1560	2009-11-20 00:00:00
1	Ramesh	1500.00	2009-10-08 00:00:00

使用交叉连接连接多个表

我们也可以使用交叉连接来连接两个以上的表。在这种情况下，会显示多向排列，并且预期结果表包含的记录数量将远多于单个表。

语法

以下是在 MySQL 中使用交叉连接连接多个表的语法 -

SELECT column_name(s)
FROM table1
CROSS JOIN table2
CROSS JOIN table3
CROSS JOIN table4
.
.
.

示例

现在让我们合并三个表 CUSTOMERS、ORDERS 和 ORDER_RANGE，以演示如何使用交叉连接合并多个表。

我们将使用以下查询创建 ORDER_RANGE 表 -

CREATE TABLE ORDER_RANGE (
   SNO INT NOT NULL,
   ORDER_RANGE VARCHAR (20) NOT NULL,
);

现在，我们可以使用 INSERT 语句将值插入到这个空表中，如下所示 -

INSERT INTO ORDER_RANGE VALUES
(1, '1-100'),
(2, '100-200'),
(3, '200-300');

ORDER_RANGE 表如下 −

SNO	ORDER_RANGE
1	1-100
2	100-200
3	200-300

现在我们对给定的表使用以下交叉连接查询，

SELECT ID, NAME, AMOUNT, DATE, ORDER_RANGE
FROM CUSTOMERS
CROSS JOIN ORDERS
CROSS JOIN ORDER_RANGE;

输出

结果表如下 -

ID	NAME	AMOUNT	DATE	ORDER_RANGE
2	Khilan	1560	2009-11-20 00:00:00	1-100
1	Ramesh	1560	2009-11-20 00:00:00	1-100
2	Khilan	1500.00	2009-10-08 00:00:00	1-100
1	Ramesh	1500.00	2009-10-08 00:00:00	1-100
2	Khilan	1560	2009-11-20 00:00:00	100-200
1	Ramesh	1560	2009-11-20 00:00:00	100-200
2	Khilan	1500.00	2009-10-08 00:00:00	100-200
1	Ramesh	1500.00	2009-10-08 00:00:00	100-200
2	Khilan	1560	2009-11-20 00:00:00	200-300
1	Ramesh	1560	2009-11-20 00:00:00	200-300
2	Khilan	1500.00	2009-10-08 00:00:00	200-300
1	Ramesh	1500.00	2009-10-08 00:00:00	200-300

使用客户端程序进行交叉连接

我们也可以使用客户端程序对一个或多个表执行交叉连接操作。

语法

PHP NodeJS Java Python

要通过 PHP 程序执行交叉连接，我们需要使用 mysqli 函数 query() 执行带有 CROSS JOIN 子句的 SQL 查询，如下所示 -

$sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a CROSS JOIN tcount_tbl b";
$mysqli->query($sql);

要通过 JavaScript 程序执行交叉连接，我们需要使用 mysql2 库的 query() 函数执行带有 CROSS JOIN 子句的 SQL 查询，如下所示 -

sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a CROSS JOIN tcount_tbl b";
con.query(sql);

要通过 Java 程序执行交叉连接，我们需要使用 JDBC 函数 executeQuery() 执行带有 CROSS JOIN 子句的 SQL 查询，如下所示 -

String sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a CROSS JOIN tcount_tbl b
ON a.tutorial_author = b.tutorial_author";
statement.executeQuery(sql);

要通过 Python 程序执行交叉连接，我们需要使用 MySQL Connector/Python 的 execute() 函数执行带有 CROSS JOIN 子句的 SQL 查询，如下所示 -

cross_join_query = "SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS CROSS JOIN ORDERS"
cursorObj.execute(cross_join_query)

示例

以下是程序 -

PHP NodeJS Java Python

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf('Connected successfully.
');

$sql = 'SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
    FROM tutorials_tbl a CROSS JOIN tcount_tbl b';
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
    echo " following is the details after executing cross join! 
";
    while ($row = $result->fetch_assoc()) {
        printf("Id: %s, Author: %s, Count: %d", $row["tutorial_id"], $row["tutorial_author"], $row["tutorial_count"]);
        printf("
");
    }
} else {
    printf('No record found.
');
}
mysqli_free_result($result);
$mysqli->close();

输出

获得的输出如下 -

following is the details after executing cross join!
Id: 102, Author: Sarika Singh, Count: 20
Id: 101, Author: Aman kumar, Count: 20
Id: 3, Author: Sanjay, Count: 20
Id: 2, Author: Abdul S, Count: 20
Id: 1, Author: John Poul, Count: 20
Id: 102, Author: Sarika Singh, Count: 5
Id: 101, Author: Aman kumar, Count: 5
Id: 3, Author: Sanjay, Count: 5
Id: 2, Author: Abdul S, Count: 5
Id: 1, Author: John Poul, Count: 5
Id: 102, Author: Sarika Singh, Count: 4
Id: 101, Author: Aman kumar, Count: 4
Id: 3, Author: Sanjay, Count: 4
Id: 2, Author: Abdul S, Count: 4
Id: 1, Author: John Poul, Count: 4
Id: 102, Author: Sarika Singh, Count: 20
Id: 101, Author: Aman kumar, Count: 20
Id: 3, Author: Sanjay, Count: 20
Id: 2, Author: Abdul S, Count: 20
Id: 1, Author: John Poul, Count: 20
Id: 102, Author: Sarika Singh, Count: 1
Id: 101, Author: Aman kumar, Count: 1
Id: 3, Author: Sanjay, Count: 1
Id: 2, Author: Abdul S, Count: 1
Id: 1, Author: John Poul, Count: 1
Id: 102, Author: Sarika Singh, Count: 1
Id: 101, Author: Aman kumar, Count: 1
Id: 3, Author: Sanjay, Count: 1
Id: 2, Author: Abdul S, Count: 1
Id: 1, Author: John Poul, Count: 1

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //连接到 MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);
 //Cross Join
  sql =
    "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a CROSS JOIN tcount_tbl b";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

输出

生成的输出如下 -

[
  {
    tutorial_id: 102,
    tutorial_author: 'Sarika Singh',
    tutorial_count: 20
  },
  {
    tutorial_id: 101,
    tutorial_author: 'Aman kumar',
    tutorial_count: 20
  },
  { tutorial_id: 2, tutorial_author: 'Abdul S', tutorial_count: 20 },
  { tutorial_id: 1, tutorial_author: 'John Poul', tutorial_count: 20 },
  {
    tutorial_id: 102,
    tutorial_author: 'Sarika Singh',
    tutorial_count: 5
  },
  {
    tutorial_id: 101,
    tutorial_author: 'Aman kumar',
    tutorial_count: 5
  },
  { tutorial_id: 2, tutorial_author: 'Abdul S', tutorial_count: 5 },
  { tutorial_id: 1, tutorial_author: 'John Poul', tutorial_count: 5 },
  {
    tutorial_id: 102,
    tutorial_author: 'Sarika Singh',
    tutorial_count: 4
  },
  {
    tutorial_id: 101,
    tutorial_author: 'Aman kumar',
    tutorial_count: 4
  },
  { tutorial_id: 2, tutorial_author: 'Abdul S', tutorial_count: 4 },
  { tutorial_id: 1, tutorial_author: 'John Poul', tutorial_count: 4 },
  {
    tutorial_id: 102,
    tutorial_author: 'Sarika Singh',
    tutorial_count: 20
  },
  {
    tutorial_id: 101,
    tutorial_author: 'Aman kumar',
    tutorial_count: 20
  },
  { tutorial_id: 2, tutorial_author: 'Abdul S', tutorial_count: 20 },
  { tutorial_id: 1, tutorial_author: 'John Poul', tutorial_count: 20 },
  {
    tutorial_id: 102,
    tutorial_author: 'Sarika Singh',
    tutorial_count: 1
  },
  {
    tutorial_id: 101,
    tutorial_author: 'Aman kumar',
    tutorial_count: 1
  },
  { tutorial_id: 2, tutorial_author: 'Abdul S', tutorial_count: 1 },
  { tutorial_id: 1, tutorial_author: 'John Poul', tutorial_count: 1 },
  {
    tutorial_id: 102,
    tutorial_author: 'Sarika Singh',
    tutorial_count: 1
  },
  {
    tutorial_id: 101,
    tutorial_author: 'Aman kumar',
    tutorial_count: 1
  },
  { tutorial_id: 2, tutorial_author: 'Abdul S', tutorial_count: 1 },
  { tutorial_id: 1, tutorial_author: 'John Poul', tutorial_count: 1 },
  {
    tutorial_id: 102,
    tutorial_author: 'Sarika Singh',
    tutorial_count: 2
  },
  {
    tutorial_id: 101,
    tutorial_author: 'Aman kumar',
    tutorial_count: 2
  },
  { tutorial_id: 2, tutorial_author: 'Abdul S', tutorial_count: 2 },
  { tutorial_id: 1, tutorial_author: 'John Poul', tutorial_count: 2 }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class CrossJoin {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");
        //MySQL CROSS JOIN...!;
         String sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a CROSS JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author";
         ResultSet resultSet = statement.executeQuery(sql);
         System.out.println("Table records after CROSS Join...!");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+ " "+ resultSet.getString(2)+" "+resultSet.getString(3));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

输出

获得的输出如下所示 -

Connected successfully...!
Table records after CROSS Join...!
1 John Paul 1
3 Sanjay 1

import mysql.connector
#建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
cross_join_query = f"""SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS CROSS JOIN ORDERS"""
cursorObj.execute(cross_join_query)
# 获取所有符合条件的行
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()

输出

以下是上述代码的输出 -

(1, 'Ramesh', 2060, '2008-05-20 00:00:00')
(1, 'Ramesh', 1560, '2009-11-20 00:00:00')
(1, 'Ramesh', 1500, '2009-10-08 00:00:00')
(1, 'Ramesh', 3000, '2009-10-08 00:00:00')
(2, 'Khilan', 2060, '2008-05-20 00:00:00')
(2, 'Khilan', 1560, '2009-11-20 00:00:00')
(2, 'Khilan', 1500, '2009-10-08 00:00:00')
(2, 'Khilan', 3000, '2009-10-08 00:00:00')
(3, 'kaushik', 2060, '2008-05-20 00:00:00')
(3, 'kaushik', 1560, '2009-11-20 00:00:00')
(3, 'kaushik', 1500, '2009-10-08 00:00:00')
(3, 'kaushik', 3000, '2009-10-08 00:00:00')
(4, 'Chaital', 2060, '2008-05-20 00:00:00')
(4, 'Chaital', 1560, '2009-11-20 00:00:00')
(4, 'Chaital', 1500, '2009-10-08 00:00:00')
(4, 'Chaital', 3000, '2009-10-08 00:00:00')
(5, 'Hardik', 2060, '2008-05-20 00:00:00')
(5, 'Hardik', 1560, '2009-11-20 00:00:00')
(5, 'Hardik', 1500, '2009-10-08 00:00:00')
(5, 'Hardik', 3000, '2009-10-08 00:00:00')
(6, 'Komal', 2060, '2008-05-20 00:00:00')
(6, 'Komal', 1560, '2009-11-20 00:00:00')
(6, 'Komal', 1500, '2009-10-08 00:00:00')
(6, 'Komal', 3000, '2009-10-08 00:00:00')
(7, 'Muffy', 2060, '2008-05-20 00:00:00')
(7, 'Muffy', 1560, '2009-11-20 00:00:00')
(7, 'Muffy', 1500, '2009-10-08 00:00:00')
(7, 'Muffy', 3000, '2009-10-08 00:00:00')

MySQL 8 教程

MySQL 8 数据库

MySQL 8 用户

MySQL 8 表

MySQL 8 查询

MySQL 8 视图

MySQL 8 索引

MySQL 运算符和子句

MySQL 连接

MySQL 键

MySQL 触发器

MySQL 8 数据类型

MySQL 正则表达式

MySQL 全文搜索

MySQL8 函数和运算符

MySQL 8 其他概念

MySQL 8 实用资源

MySQL - Cross Join 交叉连接