mysq如何选择使用内连接还是外连接

墨蓝 2022-10-27 01:37 152阅读 0赞

我们都知道mysql数据库学习的时候存在的连接方式，我们可能也比较熟悉每一种连接方式产生的数据集的表现形式或者说是数据集的结果，但是往往在实际的运用中我们会可能存在不知道该选择哪一种连接方式的情况，尤其是内外连接之间的选择，那么接下来，我们就来说一下，如何来正确选择合适的连接方式。

需要查找两张表同时存在的数据，使用内连接；需要查找两张表中一张表存在，另一张表不存在的时候使用左外链接 或 右外链接  
内连接的查询结果都是满足连接条件的元组。但有时我们也希望输出那些不满足连接条件的元组信息。比如，我们想知道每个学生的选课情况，包括已经选课的学生（这部分学生的学号在学生表中有，在选课表中也有，是满足连接条件的），也包括没有选课的学生（这部分学生的学号在学生表中有，但在选课表中没有，不满足连接条件），这时就需要使用外连接。外连接是只限制一张表中的数据必须满足连接条件，而另一张表中的数据可以不满足连接条件的连接方式。

3种外连接：

1)左外连接（LEFTOUTER JOIN）  
如果在连接查询中，连接管子左端的表中所有的元组都列出来，并且能在右端的表中找到匹配的元组，那么连接成功。如果在右端的表中，没能找到匹配的元组，那么对应的元组是空值（NULL）。这时，查询语句使用关键字LEFT OUTERJOIN，也就是说，左外连接的含义是限制连接关键字右端的表中的数据必须满足连接条件，而不关左端的表中的数据是否满足连接条件，均输出左端表中的内容。

例如：要查询所有学生的选课情况，包括已经选课的和还没有选课的学生，查询语句为  
SELECT学生表.学号，姓名，班级，课程号，成绩  
FROM学生表LEFT OUTER JOIN选课表ON学生表.学号=选课表.学号  
左外连接查询中左端表中的所有元组的信息都得到了保留。

2）右外连接（RIGHTOUTERJOIN）

右外连接与左外连接类似，只是右端表中的所有元组都列出，限制左端表的数据必须满足连接条件，而不管右端表中的数据是否满足连接条件，均输出表中的内容。  
例如：同上例内容，查询语句为  
SELECT学生表.学号，姓名，班级，课程号，成绩  
FROM学生表RIGHTOUTERJOIN选课表ON学生表.学号=选课表.学号  
右外连接查询中右端表中的所有元组的信息都得到了保留。

3）全外连接（FULL OUTER JOIN）

全外连接查询的特点是左、右两端表中的元组都输出，如果没能找到匹配的元组，就使用NULL来代替。  
例如：同左外连接例子内容，查询语句为  
SELECT学生表.学号，姓名，班级，课程号，成绩  
FROM学生表FULL OUTER JOIN选课表ON学生表.学号=选课表.学号  
全外连接查询中所有表中的元组信息都得到了保留。

## 性能上的优化： ##

### 1.显示(explicit) inner join VS 隐式(implicit) inner join ###

如：

代码如下:

select * from
    table a inner join table b
    on a.id = b.id;

代码如下:

select a.*, b.*
    from table a, table b
    where a.id = b.id;

我在数据库中比较(10w数据)得之，它们用时几乎相同，第一个是显式的inner join，后一个是隐式的inner join，数据量更大的情况下还是比较偏向于使用显式，性能上也是显式略有优势。

## 2.left join/right join VS inner join ##

**尽量用inner join.避免 LEFT JOIN 和 NULL.**

在使用left join（或right join）时，应该清楚的知道以下几点：

### 2.1 on与 where的执行顺序 ###

ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在**匹配阶段完成以后**，**WHERE 子句条件**才会被使用。**ON**将从**匹配阶段产生的数据**中**检索过滤**。

所以我们要注意：在使用Left (right) join的时候，一定要在先给出**尽可能多的匹配满足条件，减少Where的执行**。如：

代码如下:

select * from A
    inner join B on B.name = A.name
    left join C on C.name = B.name
    left join D on D.id = C.id
    where C.status>1 and D.status=1;

Great

代码如下:

select * from A
    inner join B on B.name = A.name
    left join C on C.name = B.name and C.status>1
    left join D on D.id = C.id and D.status=1

从上面例子可以看出，**尽可能满足ON的条件**，而**少用Where的条件**。从执行性能来看第二个显然更加省时。

### 2.2 注意ON 子句和 WHERE 子句的不同 ###

如作者举了一个列子：

代码如下:

mysql> SELECT * FROM product LEFT JOIN product_details
    ON (product.id = product_details.id)
    AND product_details.id=2;
    +----+--------+------+--------+-------+
    | id | amount | id | weight | exist |
    +----+--------+------+--------+-------+
    | 1 | 100 | NULL | NULL | NULL |
    | 2 | 200 | 2 | 22 | 0 |
    | 3 | 300 | NULL | NULL | NULL |
    | 4 | 400 | NULL | NULL | NULL |
    +----+--------+------+--------+-------+
    4 rows in set (0.00 sec)
     
    
    mysql> SELECT * FROM product LEFT JOIN product_details
    ON (product.id = product_details.id)
    WHERE product_details.id=2;
    +----+--------+----+--------+-------+
    | id | amount | id | weight | exist |
    +----+--------+----+--------+-------+
    | 2 | 200 | 2 | 22 | 0 |
    +----+--------+----+--------+-------+
    1 row in set (0.01 sec)

从上可知，第一条查询使用 ON 条件决定了从 LEFT JOIN的 product\_details表中检索符合的所有数据行。第二条查询做了简单的LEFT JOIN，然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。

### 2.3 尽量避免子查询，而用join ###

往往性能这玩意儿，更多时候体现在数据量比较大的时候，此时，我们应该避免复杂的子查询。如下：

代码如下:

insert into t1(a1) select b1 from t2 where not exists(select 1 from t1 where t1.id = t2.r_id);

代码如下:

insert into t1(a1) 
    select b1 from t2 
    left join (select distinct t1.id from t1 ) t1 on t1.id = t2.r_id 
    where t1.id is null;

当然mysql的优化是一个很复杂也很庞大的问题，这里只是讲一点和本文相关的，后面会再单独总结mysql的优化问题。