首页
超值活动
产品中心
物理机租用
物理机租用
物理机租用
云服务器
云服务器
云服务器
云服务器
虚拟主机
虚拟主机
免费空间
免费空间
云电脑/挂机宝
订购产品
文章公告
关于我们
博客动态
登录
网站首页
    超值活动

      帮助中心/最新通知

      质量为本、客户为根、勇于拼搏、务实创新

      所有文章
      < 返回文章列表

      【服务器相关】MySQL多表查询的案例详解

      发表时间：2025-06-16 03:46:00 小编：主机乐-Yutio

      多表查询

      案列说明

      笛卡尔积的理解

      中图（内连接）：


      select t1.name,t2.department_name
      from employees t1 JOIN departments t2
      ON t1.department_id = t2.department_id；

      左上图(左外连接)：


      select t1.name,t2.department_name
      from employees t1 LEFT JOIN departments t2
      ON t1.department_id = t2.department_id；

      右上图(右外连接)：


      select t1.name,t2.department_name
      from employees t1 RIGHT JOIN departments t2
      ON t1.department_id = t2.department_id；

      左中图：


      select t1.name,t2.department_name
      from employees t1 LEFT JOIN departments t2
      ON t1.department_id = t2.department_id
      WHERE t2.department_id IS NULL;

      右中图：


      select t1.name,t2.department_name
      from employees t1 RIGHT JOIN departments t2
      ON t1.department_id = t2.department_id
      WHERE t1.department_id IS NULL;

      左下图(满外连接)：


      #方式一：左上图 UNION ALL 右中图
      select t1.name,t2.department_name
      from employees t1 LEFT JOIN departments t2
      ON t1.department_id = t2.department_id
      UNION ALL
      select t1.name,t2.department_name
      from employees t1 RIGHT JOIN departments t2
      ON t1.department_id = t2.department_id
      WHERE t1.department_id IS NULL;

      #方式二：左中图 UNION ALL 右上图
      select t1.name,t2.department_name
      from employees t1 LEFT JOIN departments t2
      ON t1.department_id = t2.department_id
      WHERE t2.department_id IS NULL
      UNION ALL
      select t1.name,t2.department_name
      from employees t1 RIGHT JOIN departments t2
      ON t1.department_id = t2.department_id；

      右下图：


      #左中图 UNION ALL 右中图
      select t1.name,t2.department_name
      from employees t1 LEFT JOIN departments t2
      ON t1.department_id = t2.department_id
      WHERE t2.department_id IS NULL
      UNION ALL
      select t1.name,t2.department_name
      from employees t1 RIGHT JOIN departments t2
      ON t1.department_id = t2.department_id
      WHERE t1.department_id IS NULL;

      SQL语法新特性

      自然连接

      使用关键字：NATURAL JOIN（不灵活），自动查询表中所有相同字段，然后进行等值连接

      USING连接(不适用于自连接)

      使用关键字：USING（同名字段），将表中相同名字的字段自动等值连接


      select t1.name,t2.department_name
      from employees t1 JOIN departments t2
      ON t1.department_id = t2.department_id;
      等价于
      select t1.name,t2.department_name
      from employees t1 JOIN departments t2
      USING(department_id);

      到此这篇关于MySQL多表查询的文章就介绍到这了,更多相关MySQL多表查询内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！


      联系我们
      返回顶部