MySQL：区分各种join，连接条件on和过滤条件where

我爱面条

使用的DBMS是MySQL，基于MySQL区分各种连接（join），区分连接条件on和过滤条件where。
有以下两张表：id_name和id_age



表 id_name



表 id_age

如上，表id_name与表id_age都有id=1的行，表id_name独有id=2和id=3的行，表id_age独有id=5的行。
1. 区分各种连接(join)

（1） inner join 和 join

SELECT *
FROM `id_name`
INNER JOIN `id_age`
ON `id_age`.`id` = `id_name`.`id`;


inner join 结果

SELECT *
FROM `id_name`
JOIN `id_age`
ON `id_age`.`id` = `id_name`.`id`;


join 结果

结论：
inner join和join是一样的，join是inner join的简写，只不过关键字inner join中体现了连接的类型，即内连接。在对表id_name和表id_age进行（内）连接时，仅对符合连接条件（on之后语句：两个表的id相等）的行进行连接，因此，只将表id_name和表id_age中的id=1的行进行连接。
（2）left outer join 和 left join

SELECT *
FROM `id_name`
LEFT OUTER JOIN `id_age`
ON `id_age`.`id` = `id_name`.`id`;



left outer join 结果

SELECT *
FROM `id_name`
LEFT JOIN `id_age`
ON `id_age`.`id` = `id_name`.`id`;


left join 结果

结论：
left outer join和left join是一样的，left join是left outer join的简写，只不过关键字left outer join中体现了连接的类型，即外连接（注意：外连接必须指明是左外连接还是右外连接，通过关键字left和right区分）。在对表id_name和表id_age进行左（外）连接时，以左边的表id_name为主，输出左表id_name的所有行，输出右边的表id_age中符合连接条件（两个表的id相等）的行，即id=1的行，但若是右表中没有符合条件的行，则用NULL值替代。如上图结果，右表id_age中没有匹配左表id_name的id=2和id=3的行，因此用NULL值补齐。
（3）right outer join 和 right join

SELECT *
FROM `id_name`
RIGHT OUTER JOIN `id_age`
ON `id_age`.`id` = `id_name`.`id`;


right outer join 结果

SELECT *
FROM `id_name`
RIGHT JOIN `id_age`
ON `id_age`.`id` = `id_name`.`id`;


right join 结果

结论：
right outer join和right join是一样的，right join是right outer join的简写，只不过关键字right outer join中体现了连接的类型，即外连接（注意：外连接必须指明是左外连接还是右外连接，通过关键字left和right区分）。在对表id_name和表id_age进行右（外）连接时，以右边的表id_age为主，输出右表id_age的所有行，输出左边的表id_name中符合连接条件（两个表的id相等）的行，即id=1的行，但若是左表中没有符合条件的行，则用NULL值替代。如上图结果，左表id_name中没有匹配右表id_age的id=5的行，因此用NULL值补齐。
（4）left outer join 和 right outer join

采用左外连接，更换两个表的左右顺序后，再采用右外连接，输出的结果会一样吗？测试如下：
左表：id_name，右表：id_age，采用左外连接：
SELECT *
FROM `id_name`
LEFT OUTER JOIN `id_age`
ON `id_age`.`id` = `id_name`.`id`;


left outer join 结果

左表：id_age，右表：id_name，采用右外连接：
SELECT *
FROM `id_age`
RIGHT OUTER JOIN `id_name`
ON `id_age`.`id` = `id_name`.`id`;


right outer join 结果

结论：
输出的结果还是有些区别的，因为改变了表的左右顺序。但因为都是以表id_name为主，所以具体内容是一样的。若想保证输出结果一模一样，即顺序也是一样的，只需调整检索列的顺序，以表id_name输出在前为例：
# SELECT * ---> SELECT `id_name`.*, `id_age`.*因此，改变表的左右顺序，调整select语句中检索列顺序（如果需要保持输出顺序一样），left outer join 和 right outer join是可以互相转换的。
（5）小结

区分内连接和外连接，外连接分左外连接和右外连接，在使用外连接时必须指明是左外连接还是右外连接，左外连接和右外连接是可以互相转换的！
2. 连接条件on 和 过滤条件where

无论是哪种连接，一般都要有连接条件，即join后跟随on子句。内连接没有连接条件的话，结果将是笛卡尔积，也称为叉连接（cross join）—— 待连接表进行任意组合。（在MySQL中，inner join后可以不加连接条件on，只不过得到的是笛卡尔积，但外连接必须加上连接条件on，不然报错）！
SELECT *
FROM `id_name`
INNER JOIN `id_age`;
-- ON `id_age`.`id` = `id_name`.`id`;


cross join 结果

如上，输出6=3×2行，将表id_name中的3行与表id_age中的2行进行一一组合。因此，针对内连接，连接条件不能少，不然会返回比想要的数据多得多的数据。
连接条件on是在连接表时使用的条件，过滤条件where是在表连接后，对连接好的表进行过滤时使用的条件。看几个例子来理解on和where的区别吧！
（1）例子1：在inner join后分别使用where和on

• 使用where：
  

SELECT *
FROM `id_name`
INNER JOIN `id_age`
WHERE `id_age`.`id` = `id_name`.`id`;


where 结果

• 使用on：
  

SELECT *
FROM `id_name`
INNER JOIN `id_age`
ON `id_age`.`id` = `id_name`.`id`;


on 结果

在inner join后使用where和on的效果是一样的，其实不难理解：例子1中的第一条SQL，是无连接条件的内连接，得到笛卡尔积，即6行数据，再利用where子句的条件过滤出两表id相等的行，输出上述1行数据；例子1中第二条SQL，是有连接条件的内连接，仅连接两表id相等的行，输出上述1行数据。
（2）例子2：在left outer join后分别使用on where和on and

• 使用on where：
  

SELECT *
FROM `id_name`
LEFT OUTER JOIN `id_age`
ON `id_age`.`id` = `id_name`.`id`
WHERE `name` = 'b';


on where 结果

连接条件：两表id相等。先根据该连接条件进行左外连接，得到以下结果


然后对该结果利用过滤条件：name='b'，得到on where 结果。

• 使用on and：
  

SELECT *
FROM `id_name`
LEFT OUTER JOIN `id_age`
ON `id_age`.`id` = `id_name`.`id` AND `name` = 'b';


on and 结果

连接条件：两表id相等，且name为'b'。左表id_name，右表id_age，进行左外连接，右表中不存在符合该连接条件的行，补为NULL值。一定要注意on where 和 on and 区别！
（3）例子3：在left outer join后使用on or

SELECT *
FROM `id_name`
LEFT OUTER JOIN `id_age`
ON `id_age`.`id` = `id_name`.`id` OR `name` = 'b';


on or 结果

连接条件：两表id相等，或name为'b'。该结果中前三行均满足连接条件，最后一行不满足，用NULL值填充。
（4）小结

在MySQL中，inner join后可以不加连接条件on，只不过得到的是笛卡尔积，但外连接必须加上连接条件on，不然报错。连接条件on是在连接表时使用的条件，过滤条件where是在表连接后，对连接好的表进行过滤时使用的条件。inner join后使用where和on的效果是一样的。另外，要注意左（右）外连接中on and 和 on where的区别。
希望有所帮助，欢迎指正！