欢迎光临
我们一直在努力

SQL 入门 4:多表连接与联合:外连接到 UNION 的应用

以下为 SQL 中多表外连接、USING、NATURAL JOIN、CROSS JOIN 和 UNION 的用法解析。需示例数据可私信获取。

学习内容

  • 多表外连接
    • 基于内连接扩展,使用 LEFT JOIN 保留左表全部记录。
    • 示例:orders 与 customers(基于 customer_id)、order_statuses(基于 status)连接。
    • 自外连接
      • 同一表自连接,使用 LEFT JOIN,保留所有记录。
      • 示例:员工及其上级(可能为空)。
    • USING 语句
      • 替代 ON,用于列名完全一致的连接,简化语法。
      • 支持多列,如 USING (order_id, product_id)。
    • 自然连接(NATURAL JOIN)
      • 数据库自动判断连接依据(相同列名),不推荐使用,仅了解。
    • 交叉连接(CROSS JOIN)
      • 无连接条件,生成笛卡尔积。显式用 CROSS JOIN,隐式用逗号。
      • 应用场景:组合所有可能选项(如颜色与尺码)。
    • UNION 合并结果
      • 垂直合并多个查询结果,列数需相同,数据类型需兼容,自动去重。
      • 可合并同表或不同表查询,列名以第一个 SELECT 为准。

      示例代码与讲解

      1. 多表内连接回顾

      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、customers 和 order_statuses,仅返回匹配记录。

      2. 多表外连接

      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;

      • 基于 customers 和 orders,保留所有客户,无订单时 order_id 为 NULL。

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

      • 加入 shippers,基于 shipper_id,保留所有客户记录。

      3. 自外连接

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

      • employees 自连接,查询员工及上级,无上级时 manager 为 NULL。

      4. USING 语句

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

      • 标准 ON 写法。

      SELECT o.order_id, c.first_name
      FROM orders o
      JOIN customers c USING (customer_id);

      • USING 替代,需列名一致。

      SELECT o.order_id, c.first_name,
      sh.name
      FROM orders o
      JOIN customers c USING (customer_id)
      LEFT JOIN shippers sh USING (shipper_id);

      • 多表连接使用 USING。

      SELECT *
      FROM order_items oi
      LEFT JOIN order_item_notes oin USING (order_id,
      product_id);

      • 复合条件连接。

      5. 自然连接

      SELECT o.order_id, c.first_name
      FROM orders o
      NATURAL JOIN customers c;

      • 自动基于相同列名连接,不推荐。

      6. 交叉连接

      SELECT *
      FROM customers c
      CROSS JOIN products p;

      • 显式交叉连接,若 customers 和 products 各 10 行,结果为 100 行。

      SELECT *
      FROM customers c, products p;

      • 隐式交叉连接,等效。

      7. UNION 合并

      SELECT o.order_id, o.order_date, 'active' AS status
      FROM orders o
      WHERE o.order_date >= '2019-01-01'
      UNION
      SELECT o.order_id, o.order_date, 'archived' AS status
      FROM orders o
      WHERE o.order_date < '2019-01-01';

      • 合并同表查询,2019 年后订单标记 active,之前为 archived。

      SELECT c.first_name
      FROM customers c
      UNION
      SELECT sh.name
      FROM shippers sh;

      • 合并不同表结果,列名以第一个 SELECT 为准。

      作业

      1. 多表外连接

      SELECT o.order_date, order_id,
      c.first_name, sh.name AS shipper, os.name AS status
      FROM orders o
      LEFT JOIN customers c ON o.customer_id = c.customer_id
      LEFT JOIN order_statuses os ON o.status =
      os.order_status_id
      LEFT JOIN shippers sh ON o.shipper_id = sh.shipper_id
      ORDER BY status;

      • 连接 orders、customers、order_statuses 和 shippers,按状态排序。

      2. USING 连接

      USE sql_invoicing;
      SELECT p.date, c.name AS client, p.amount, pm.name
      FROM payments p
      JOIN clients c USING (client_id)
      JOIN payment_methods pm ON p.payment_method =
      pm.payment_method_id;

      • 连接 payments、clients 和 payment_methods。

      3. UNION 合并

      SELECT c.customer_id, c.first_name,
      c.points, 'Bronze' AS Type
      FROM customers c
      WHERE c.points < 2000
      UNION
      SELECT c.customer_id, c.first_name, c.points, 'Silver' AS Type
      FROM customers c
      WHERE c.points BETWEEN 2000 AND 3000
      UNION
      SELECT c.customer_id, c.first_name, c.points, 'Gold' AS Type
      FROM customers c
      WHERE c.points > 3000
      ORDER BY first_name;

      • 根据积分分级(Bronze、Silver、Gold),按姓名排序,列名直接使用 first_name。

      总结

      本次解析了多表外连接、自外连接、USING 的简化语法,以及 CROSS JOIN 和 UNION 的应用。基于 sql_store、sql_hr 和 sql_invoicing 数据库。后续将探讨更复杂查询。

    赞(0)
    未经允许不得转载:171主机测评 » SQL 入门 4:多表连接与联合:外连接到 UNION 的应用
    分享到: 更多 (0)

    评论 抢沙发

    • 昵称 (必填)
    • 邮箱 (必填)
    • 网址