MySQL中的连接查询

• 条件连接
• 等值连接
• 自然连接
• 内连接
  • using 与 on
  • 内连接与自然连接
• 外连接
  • 左连接
  • 右连接
  • 全连接
    • union与union all
• 交叉连接

条件连接

或叫逗号连接, 在多个表的笛卡尔积中选取满足条件的行的连接, 如select * from A,B where A.a > A.b

等值连接

特殊的条件连接, 当条件为某字段=某字段时, 即为等值连接。如select * from A,B where A.a=B.b

自然连接

对两张表中字段名和数据类型都相同的字段进行等值连接, 再将表中重复的字段去掉, 即为自然连接。

• 写法 : NATURAL JOIN
• 条件 : 列名相同且类型相同
• 不允许带on/using

示例:

-- A中有a,b,c字段, B中有c,d字段
select * from A natural join B;
-- 结果等同于下面
select A.a, A.b, A.c, B.d from A,B where A.c = B.c;

内连接

使用比较运算符(<>=)根据每个表共有的列值匹配两个表中的行

• 写法: (INNER) JOIN
• 可以不写on/using

示例:

-- 等值
select * from A join B on A.c = B.c;
-- 非等值
select * from A join B on A.a > B.d;
-- 带条件
select * from A join B on A.c = B.c where A.a > B.d;
-- using
select * from A join B using (c);

using 与 on

using与on都可以达到等值连接的效果, 但二者直接也有差别

• on可以设置条件, 而using不可设置条件,只能指明使用哪些字段进行等值连接
• on不会去掉重复列, 而using会去掉重列(去重效果与自然连接的效果一样)

内连接与自然连接

• 自然连接只能是同名属性的等值连接, 而内连接可以使用on子句来指定连接条件
• 自然连接的结果会去掉重复列, 而内连接使用on子句进行等值连接时, 重复列不会被去掉
• 当内连接使用using子句时, 若指定所有同名同类型属性, 则与自然连接的结果一样

外连接

外连接包括左连接、右连接、全连接

• 所有外连接必须使用on/using子句提供相应的连接条件

左连接

左表的所有行, 右表的匹配行。如果左表的某行在右表中没有匹配行, 则在结果集中右表的所有列均为空值

• 写法 : LEFT [OUTER] JOIN

示例:

-- on
select * from A left join B on A.a = B.b;
-- using
select * from A left outer join B using (a, b);

右连接

右表的所有行, 左表的匹配行。如果右表的某行在左表中没有匹配行, 则在结果集中左表的所有列均为空值

• 写法 : RIGHT [OUTER] JOIN

全连接

左表和右表中的所有行, 当某行在另一个表中没有匹配行时, 显示为空值

• 写法 : FULL [OUTER] JOIN
• MySQL不支持全连接, 可以采用左外 + union + 右外达到等效目的

-- 使用 左外 + union + 右外 达到 full join 的效果
(select * from A left outer join B on A.id=B.id) union (select * from A right outer join B on A.id=B.id);

union与union all

• UNION操作符用于合并两个或多个SELECT语句的结果集
• UNION内部的SELECT语句必须拥有相同数量的列, 列也必须拥有相似的数据类型
• UNION会去掉重复的值, UNION ALL会保留重复的值

交叉连接

交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积

• 写法 : CROSS JOIN
• 可以不写on/using, 不写是时等同于笛卡尔积

示例:

-- 笛卡尔积
select * from A cross join B;
-- on
select * from A cross join B on A.a = B.b;