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://: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')

列印頁面

上一個

下一個