MySQL - 交叉连接



MySQL 交叉连接

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

笛卡尔积或叉积是两个集合相乘后得到的结果。这是通过将两个集合的所有可能对相乘来完成的。

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

Cross Join

语法

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

SELECT column_name(s) FROM table1 CROSS JOIN table2

示例

在这个交叉连接示例中,让我们首先创建一个名为CUSTOMERS的表,其中包含客户的个人详细信息,包括他们的姓名、年龄、地址和薪水等。

Open Compiler
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 语句将值插入此表,如下所示:

Open Compiler
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的另一个表,其中包含已下的订单的详细信息以及下单日期。

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

使用 INSERT 语句,将值插入此表,如下所示:

Open Compiler
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表中的每一行。

Open Compiler
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 表:

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

现在,我们可以使用 INSERT 语句将值插入这些空表,如下所示:

Open Compiler
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

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

Open Compiler
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

Learn MySQL in-depth with real-world projects through our MySQL certification course. Enroll and become a certified expert to boost your career.

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

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

语法

要通过 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/Pythonexecute()函数执行带有 CROSS JOIN 子句的 SQL 查询,如下所示:

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

示例

以下是程序:

$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://#: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')
广告