MySQL - 左连接

MySQL：理解和使用 LEFT JOIN

在关系型数据库中，数据通常分散在多个表中，以确保效率并减少冗余。JOIN 子句是根据相关列组合来自两个或更多表的行的基本工具。INNER JOIN 只检索两个表中匹配的行，而 OUTER JOIN 允许您包含没有匹配的行。

LEFT JOIN（也写作 LEFT OUTER JOIN）是最常见的外连接类型之一。它返回左表中的所有记录以及右表中匹配的记录。如果没有匹配项，则右侧的结果为 NULL。

LEFT JOIN 可视化

想象两组数据，表 A（左表）和表 B（右表）。LEFT JOIN 会给您表 A 中的所有内容，以及表 B 中任何重叠的数据。

      Table A (Left)             Table B (Right)
+-----------------------+     +-----------------------+
| All rows from Table A |     | Matching rows from B  |
|                       |=====>|                       |
|      (even if no match in B)  |                       |
+-----------------------+     +-----------------------+

Result: All of A, with corresponding B data where a match exists, otherwise NULLs for B's columns.

语法

LEFT JOIN 的基本语法如下：

SELECT
    table1.column1,
    table1.column2,
    table2.column1
FROM
    table1
LEFT JOIN
    table2 ON table1.related_column = table2.related_column;

ON 子句指定了用于匹配两个表之间行的条件。

实际示例：客户和订单

让我们设置两个表：customers 和 orders。这个经典示例有助于说明 LEFT JOIN 的工作原理。

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

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

INSERT INTO customers (id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES
(101, 1, '2023-10-15', 99.50),
(102, 2, '2023-10-16', 150.00),
(103, 1, '2023-10-20', 45.00);

我们有三位客户，但只有 Alice（ID 1）和 Bob（ID 2）下过订单。Charlie（ID 3）没有下过订单。

查询：显示所有客户及其订单

如果我们想要所有客户及其可能下的订单列表，LEFT JOIN 是完美的工具。customers 将作为我们的左表。

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

输出

结果包含了所有客户。对于没有订单的 Charlie，orders 表中的列值为 NULL。

customer_id	name	order_id	order_date	amount
1	Alice	101	2023-10-15	99.50
1	Alice	103	2023-10-20	45.00
2	Bob	102	2023-10-16	150.00
3	Charlie	NULL	NULL	NULL

关键用例：查找不匹配的记录

LEFT JOIN 最强大的应用之一是查找一个表中没有对应另一个表中行的记录。这通过添加 WHERE 子句来过滤右表中键的 NULL 值来实现。

查询：查找从未下过订单的客户

SELECT
    c.id,
    c.name
FROM
    customers AS c
LEFT JOIN
    orders AS o ON c.id = o.customer_id
WHERE
    o.customer_id IS NULL;

输出

此查询有效地寻找了所有未找到匹配订单的客户，只返回了 Charlie。

id	name
3	Charlie

多表 LEFT JOIN

您可以链式使用 LEFT JOIN 子句来组合来自三个或更多表的数据。逻辑保持不变：每个连接都将前一个连接的结果集作为其“左表”。

示例：添加发货数据

让我们添加一个 shipments 表。并非所有订单都已发货。

CREATE TABLE shipments (
    shipment_id INT PRIMARY KEY,
    order_id INT,
    ship_date DATE,
    carrier VARCHAR(50),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

INSERT INTO shipments (shipment_id, order_id, ship_date, carrier) VALUES
(201, 101, '2023-10-16', 'ExpressCo');

现在，让我们连接所有三个表，以查看所有客户、他们的订单以及任何相关的发货信息。

SELECT
    c.name,
    o.order_id,
    s.ship_date,
    s.carrier
FROM
    customers AS c
LEFT JOIN
    orders AS o ON c.id = o.customer_id
LEFT JOIN
    shipments AS s ON o.order_id = s.order_id;

输出

name	order_id	ship_date	carrier
Alice	101	2023-10-16	ExpressCo
Alice	103	NULL	NULL
Bob	102	NULL	NULL
Charlie	NULL	NULL	NULL

在应用程序代码中处理 LEFT JOIN

当您在应用程序中从 LEFT JOIN 获取结果时，必须准备好处理 NULL 值。在像 Java 这样的强类型语言中，这可能意味着使用包装类（例如，Integer 而不是 int）或在访问值之前检查 null。在像 Python 或 JavaScript 这样的动态类型语言中，您将检查 None 或 null。

Python 示例（处理 NULL 值）

import mysql.connector

DB_CONFIG = {
    'user': 'root',
    'password': 'your_password',
    'host': '127.0.0.1',
    'database': 'your_database'
}

def get_customer_orders():
    results = []
    sql = """
        SELECT c.name, o.order_id, o.amount
        FROM customers AS c
        LEFT JOIN orders AS o ON c.id = o.customer_id
        ORDER BY c.name;
    """
    with mysql.connector.connect(**DB_CONFIG) as conn:
        with conn.cursor(dictionary=True) as cursor:
            cursor.execute(sql)
            for row in cursor.fetchall():
                # 优雅地处理可能的 NULL 值
                order_id = row['order_id'] if row['order_id'] is not None else 'No Order'
                amount = row['amount'] if row['amount'] is not None else 0.00

                print(f"Customer: {row['name']}, Order ID: {order_id}, Amount: {amount:.2f}")
                results.append(row)
    return results

get_customer_orders()