MySQL 表连接（内连接与外连接）

🏝️专栏：Mysql_猫咪-9527的博客-CSDN博客

🌅主页：猫咪-9527-CSDN博客 

“欲穷千里目，更上一层楼。会当凌绝顶，一览众山小。”

目录

1、表连接的核心概念

1.1 为什么需要表连接？

2、内连接（INNER JOIN）

2.1 核心概念

2.2 案例：

3、外连接（OUTER JOIN）

3.1 左外连接（LEFT JOIN）

核心概念

案例：查询所有雇员及工资

3.2 右外连接（RIGHT JOIN）

核心概念

案例：查询所有工资，即使无对应雇员

3.3 左外与右外的等价性

4、注意事项

4.1 ON与WHERE子句的区别

4.2 NULL值的处理

1、表连接的核心概念

1.1 为什么需要表连接？

• 问题：两个表的数据需要关联查询（如学生表和成绩表）
• 笛卡尔积：未指定条件时，两个表的所有记录两两组合（效率低下，结果混乱）
• 解决方案：通过连接条件筛选有效组合

  ---

  2、内连接（INNER JOIN）

  2.1 核心概念

  • 定义：仅返回两表中满足连接条件的记录

            内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选，我们前面学习的查询都是内连接，也是在开发过程中使用的最多的连接查询。

    • 语法：

      select 字段 from 表1 inner join 表2 on 表1.id = 表2.id [and 其他筛选条件];
      

      • 等价写法：WHERE子句隐式内连接

        SELECT 字段 FROM 表1, 表2 WHERE 表1.id = 表2.id [and 其他筛选条件];
        

        2.2 案例：

        查询员工及其部门名称

        两者写法不同但是效果一致

        方法一：

        ---隐式内连接写法
        select *from emp,dept where emp.deptno=dept.deptno;

         方法二：

        ---标准内连接写法
        select *from dept inner join emp on emp.deptno=dept.deptno;

        

        ---

        3、外连接（OUTER JOIN）

        外连接分为左外连接和右外连接

        3.1 左外连接（LEFT JOIN）

        核心概念

        • 保留规则：左表（第一个表）的所有记录，右表无匹配时用NULL填充
        • 语法：

          select 字段 from 表1 left join 表2 on 链接条件 [and 其他筛选条件];
          

          案例：查询所有雇员及工资

          -- 创建表
          CREATE TABLE employees (emp_id INT, emp_name VARCHAR(30));
          INSERT INTO employees VALUES (1, 'Alice'), 
          (2, 'Bob'), (3, 'Charlie'), (4, 'David');
          CREATE TABLE salaries (emp_id INT, salary INT);
          INSERT INTO salaries VALUES (1, 5000), (2, 6000), (5, 7000);
          -- 左外连接查询
          select *from employees s1 left join salaries s2 on s1.emp_id=s2.emp_id;

          结果：

          ---

          3.2 右外连接（RIGHT JOIN）

          核心概念

          • 保留规则：右表（第二个表）的所有记录，左表无匹配时用NULL填充
          • 语法：

            select 字段 from 左表 right join 右表 on 链接条件 [and 其他筛选条件];
            

            案例：查询所有工资，即使无对应雇员

            select *from employees s1 right join salaries s2 
            on s1.emp_id=s2.emp_id;

            结果：

            ---

            3.3 左外与右外的等价性

            -- 左外连接（保留左表）
            SELECT * FROM A LEFT JOIN B ON A.id = B.id;
            -- 等价于右外连接（保留右表）
            SELECT * FROM B RIGHT JOIN A ON A.id = B.id;
            

            建议：优先使用左外连接，逻辑更直观。 

            ---

            4、注意事项

            4.1 ON与WHERE子句的区别

            • ON：用于指定连接条件，影响匹配逻辑。
            • on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

              

              • WHERE：用于过滤已连接后的结果，会过滤掉外连接中的NULL值。
              • where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉，on后的条件用来生成左右表关联的临时表，where后的条件对临时表中的记录进行过滤。

                

                ---

                4.2 NULL值的处理

                • ifnull()函数

                  

                   外连接结果中的NULL表示无匹配记录，需在业务逻辑中处理（如显示“无成绩”）。

                  select s1.emp_id,emp_name,ifnull(salary,'无成绩')salary 
                  from employees s1 left join salaries s2 on s1.emp_id=s2.emp_id;