MySQL - 交叉连接

Table of content

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

上一个

下一个

MySQL 交叉连接

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! \n";
    while ($row = $result->fetch_assoc()) {
        printf("Id: %s, Author: %s, Count: %d", $row["tutorial_id"], $row["tutorial_author"], $row["tutorial_count"]);
        printf("\n");
    }
} 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",
}); //Connecting to 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://127.0.0.1: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
#establishing the connection
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)
# Fetching all the rows that meet the criteria
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')

打印页面

上一个

下一个