SQL 入门 3：从内连接到外连接的全面解析

以下为 SQL 连接操作的用法解析，涵盖内连接、外连接、多表连接等。

学习内容

1. 内连接（INNER JOIN）
• 基于条件匹配两表记录，INNER 可省略。
• 结果为匹配成功的记录，未匹配的被排除。
• customers 表存客户信息，orders 表存订单信息，连接后订单信息在前，客户信息附后。
• 列名重复时需用 表名.列名 或别名区分。
2. 跨数据库连接
• 连接不同数据库的表，需指定数据库名，如 数据库名.表名。
3. 自连接
• 同一表内连接，需用不同别名区分实例。
• 示例：员工表含员工信息及上级 ID，上级也在表中。
4. 多表连接
• 连接超过两表，依次使用 JOIN 和 ON 指定条件。
5. 复合连接条件
• 单列无法唯一标识时，使用多列条件匹配。
• 示例：order_items 需用 order_id 和 product_id 共同确定唯一记录。
6. 隐式连接
• 用 WHERE 替代 JOIN，语法简洁但易出错。
• 遗漏 WHERE 会导致交叉连接，不推荐。
7. 外连接（LEFT JOIN）
• 内连接仅返回匹配记录，外连接保留未匹配记录。
• LEFT JOIN 输出左表全部记录，右表无对应则为 NULL。
• 解决内连接局限：如需查询所有客户（含无订单者），需用外连接。

---

示例代码与讲解

1. 内连接

SELECT *
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

• 连接 orders 和 customers，基于 customer_id。
• 结果为订单信息+对应客户信息，未下单客户被排除。

SELECT order_id, first_name, last_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

• 查询订单 ID 及对应客户姓名。

SELECT order_id, orders.customer_id, first_name, last_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

• customer_id 在两表均存在，用 orders.customer_id 指定。

SELECT order_id, o.customer_id, first_name, last_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

• 表别名 o 和 c 简化写法。

2. 跨数据库连接

SELECT *
FROM order_items oi
JOIN sql_inventory.products p ON oi.product_id = p.product_id;

• 连接 sql_store.order_items 和 sql_inventory.products，需指明数据库名。

3. 自连接

USE sql_hr;
SELECT e.first_name, e.last_name, m.first_name AS manager
FROM employees e
JOIN employees m ON e.reports_to = m.employee_id;

• employees 表自连接，e 为员工，m 为上级，基于 reports_to 和 employee_id。

4. 多表连接

USE sql_store;
SELECT o.order_id, o.order_date, c.first_name, c.last_name, o.customer_id, os.name AS status
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_statuses os ON o.status = os.order_status_id;

• 连接 orders（基于 customer_id）、customers 和 order_statuses（基于 status）。

5. 复合连接条件

SELECT *
FROM order_items oi
JOIN order_item_notes oin
    ON oi.order_id = oin.order_id
    AND oi.product_id = oin.product_id;

• order_items 单列无法唯一标识，用 order_id 和 product_id 复合连接。

6. 隐式连接

SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

• 显式内连接。

SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id;

• 隐式连接，等效但不推荐，遗漏 WHERE 会生成交叉连接。

7. 外连接

SELECT c.customer_id, c.first_name, order_id
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id;

• 内连接，仅返回有订单的客户。

SELECT c.customer_id, c.first_name, order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id;

• 左连接，返回所有客户，无订单时 order_id 为 NULL。

---

作业

1. 订单项与产品

SELECT order_id, oi.product_id, p.name, oi.quantity, oi.unit_price
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id;

• 查询订单项及产品信息，单价以 order_items.unit_price 为准，非 products。

2. 支付与客户

USE sql_invoicing;
SELECT p.client_id, p.invoice_id, c.name, p.date, p.amount, pm.name
FROM payments p
JOIN clients c ON c.client_id = p.client_id
JOIN payment_methods pm ON p.payment_method = pm.payment_method_id;

• 连接 payments（基于 client_id）、clients 和 payment_methods（基于 payment_method）。

3. 产品与订单项

USE sql_store;
SELECT p.product_id, p.name, oi.quantity
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id;

• 查询所有产品及订单数量，无订单时 quantity 为 NULL。

---

总结

本次解析了 SQL 连接操作：内连接基于匹配记录，外连接保留全部左表记录，自连接处理表内关系，多表和复合连接应对复杂场景。基于 sql_store、sql_hr 和 sql_invoicing 数据库。后续将探讨更复杂查询。