MySQL - 内连接

MySQL - 内连接 (INNER JOIN)

理解 MySQL 内连接 (INNER JOIN)

INNER JOIN 是 SQL 中最常见的连接类型。它根据两个或多个表之间的相关列来组合行。结果只包含在两个表中连接列值匹配的行。

您可以将 INNER JOIN 可视化为两个集合的交集。如果将两个重叠的圆圈想象成您的表，INNER JOIN 只返回满足连接条件的重叠区域中的数据。

关键字 INNER 是可选的。如果您只使用 JOIN，MySQL 默认会执行 INNER JOIN。然而，明确地写出 INNER JOIN 被认为是良好的实践，因为它提高了查询的可读性。

语法

INNER JOIN 的基本语法如下：

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

ON 子句指定了链接两个表的条件。这通常是其中一个表的主键与另一个表中相应外键之间的比较。

语法和基本示例

让我们使用一个经典的示例，包含 customers 表和 orders 表。我们希望列出所有订单以及下单客户的姓名。

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATETIME NOT NULL,
    customer_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
      ON DELETE CASCADE
);

请注意 FOREIGN KEY 约束。这强制执行了关系完整性，确保 orders 表中的每个 customer_id 都对应一个实际存在的客户。现在，让我们插入一些数据。

INSERT INTO customers (name, email) VALUES
(1, 'Alice Smith', 'alice@example.com'),
(2, 'Bob Johnson', 'bob@example.com'),
(3, 'Charlie Brown', 'charlie@example.com');

INSERT INTO orders (order_date, customer_id, amount) VALUES
('2023-10-26 10:00:00', 1, 99.50),
('2023-10-26 11:30:00', 2, 45.00),
('2023-10-27 14:00:00', 1, 120.00),
('2023-10-28 09:00:00', 3, 250.75);

要获取包含客户姓名的订单列表，我们通过 customers.id = orders.customer_id 连接这两个表。

SELECT
    c.name AS customer_name,
    o.order_id,
    o.order_date,
    o.amount
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id;

输出

结果将只包含已下单的客户。

customer_name	order_id	order_date	amount
Alice Smith	1	2023-10-26 10:00:00	99.50
Bob Johnson	2	2023-10-26 11:30:00	45.00
Alice Smith	3	2023-10-27 14:00:00	120.00
Charlie Brown	4	2023-10-28 09:00:00	250.75

连接多个表

您可以链式使用 INNER JOIN 子句来组合两个以上的表。例如，我们添加一个 order_items 表，并将这三个表连接起来，以查看每个订单中包含哪些产品。

CREATE TABLE order_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

INSERT INTO order_items (order_id, product_name, quantity) VALUES
(1, 'Laptop', 1),
(2, 'Mouse', 1),
(2, 'Keyboard', 1),
(3, 'Monitor', 1);

-- Join three tables
-- 连接三个表
SELECT
    c.name AS customer_name,
    o.order_id,
    oi.product_name,
    oi.quantity
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
INNER JOIN order_items AS oi ON o.order_id = oi.order_id;

结合内连接 (INNER JOIN) 和 WHERE 子句

您可以在连接子句后添加 WHERE 子句来进一步筛选结果。例如，查找 ‘Alice Smith’ 下的所有订单：

SELECT
    c.name AS customer_name,
    o.order_id,
    o.amount
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
WHERE c.name = 'Alice Smith';

性能和最佳实践

索引的重要性

为了使 INNER JOIN 具有良好性能，ON 子句中使用的列必须被索引。当您定义 PRIMARY KEY 或 UNIQUE 约束时，MySQL 会自动创建索引。FOREIGN KEY 列也应始终被索引。如果没有索引，MySQL 必须对另一个表的每一行执行全表扫描，这在大型数据集上会非常慢。

常见错误

• 模糊列：如果两个表都有相同名称的列（例如 id），您必须使用表名或别名进行限定（例如 customers.id）。否则会导致“ambiguous column”（模糊列）错误。
• 意外的笛卡尔积：完全忘记 ON 子句会将您的 INNER JOIN 变成 CROSS JOIN，产生笛卡尔积，并可能导致不正确的结果。
• 使用 WHERE 而非 ON：虽然 SELECT * FROM t1, t2 WHERE t1.id = t2.id 可以产生相同的结果，但它是一种过时的语法。现代的 JOIN ... ON 语法更受推荐，因为它将 连接逻辑 (ON) 与 过滤逻辑 (WHERE) 分开，使查询更易于阅读和维护。

在应用程序代码中使用内连接 (INNER JOIN)

当从应用程序执行连接时，请使用预处理语句，特别是当 WHERE 子句包含用户输入时。

PHP (PDO)
Node.js (mysql2)
Java (JDBC)
Python (mysql-connector)



Use a prepared statement to securely pass filter values to your join query.

```php
<?php
// ... (PDO connection setup)
// ... (PDO 连接设置)
try {
    $pdo = new PDO($dsn, $user, $pass, $options);

    $sql = "SELECT c.name, o.order_id, o.amount FROM customers AS c " .
           "INNER JOIN orders AS o ON c.id = o.customer_id WHERE c.id = :customer_id";

    $stmt = $pdo->prepare($sql);
    $stmt->execute(['customer_id' => 1]);

    echo "Orders for customer ID 1:\n";
    // 输出客户 ID 为 1 的订单：
    foreach ($stmt->fetchAll() as $row) {
        print_r($row);
    }

} catch (\PDOException $e) { /* ... error handling ... */ }
// ...错误处理...
?>

Use mysql2/promise with async/await for clean, non-blocking database access.

const mysql = require('mysql2/promise');

async function getCustomerOrders(customerId) {
    let connection;
    try {
        connection = await mysql.createConnection({ /* ... connection config ... */ });
        // ...连接配置...

        const sql = 'SELECT c.name, o.order_id, o.amount FROM customers AS c ' +
                    'INNER JOIN orders AS o ON c.id = o.customer_id WHERE c.id = ?';

        const [rows] = await connection.execute(sql, [customerId]);

        console.log(`Orders for customer ID ${customerId}:`);
        // 输出客户 ID 为 ${customerId} 的订单：
        console.log(rows);

    } catch (error) {
        console.error('Query failed:', error);
        // 查询失败：
    } finally {
        if (connection) await connection.end();
    }
}

getCustomerOrders(1);

Use a PreparedStatement to safely include parameters in your query.

import java.sql.*;

public class InnerJoinExample {
    public static void main(String[] args) {
        String sql = "SELECT c.name, o.order_id, o.amount FROM customers AS c " +
                     "INNER JOIN orders AS o ON c.id = o.customer_id WHERE c.id = ?";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, 1); // Set the customer_id parameter
            // 设置 customer_id 参数
            try (ResultSet rs = stmt.executeQuery()) {
                System.out.println("Orders for customer ID 1:");
                // 输出客户 ID 为 1 的订单：
                while (rs.next()) {
                    System.out.printf("Name: %s, Order ID: %d, Amount: %.2f%n",
                        rs.getString("name"),
                        rs.getInt("order_id"),
                        rs.getBigDecimal("amount"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Pass query parameters as a tuple to the cursor’s execute method.

import mysql.connector

config = { /* ... connection config ... */ }
// ...连接配置...

def get_customer_orders(customer_id):
    try:
        with mysql.connector.connect(**config) as cnx:
            with cnx.cursor(dictionary=True) as cursor:
                query = ("SELECT c.name, o.order_id, o.amount FROM customers AS c "
                         "INNER JOIN orders AS o ON c.id = o.customer_id WHERE c.id = %s")

                cursor.execute(query, (customer_id,))

                print(f"Orders for customer ID {customer_id}:")
                // 输出客户 ID 为 {customer_id} 的订单：
                for row in cursor.fetchall():
                    print(row)

    except mysql.connector.Error as err:
        print(err)

get_customer_orders(1)