mysql各种连接查询
一、 建表
数据库表: a_table, b_table
连接包括:内连接、左连接(左外连接)、右连接(右外连接)、全连接(全外连接)、左表独有、右表独有
建表语句:
CREATE TABLE `a_table` (
`a_id` int(11) DEFAULT NULL,
`a_name` varchar(10) DEFAULT NULL,
`a_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `b_table` (
`b_id` int(11) DEFAULT NULL,
`b_name` varchar(10) DEFAULT NULL,
`b_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
填充测试数据:
a_table:
aid
a_name
a_part
1
张三
总裁部
2
李四
秘书部
3
王五
设计部
4
赵六
运营部
b_table:
b_id
b_name
b_part
2
李四
秘书部
3
王五
设计部
5
小七
人事部
6
小八
生产部
二、 内连接
关键字:inner join on
语句:
select *from a_table a inner join b_table b on a.a_id = b.b_id;
结果:
说明: 内连接是两个表的交集部分。
三、 左(外)连接
关键字:left join on 或 **left outer join on **
语句:
select *from a_table a left outer join b_table b on a.a_id = b.b_id;
结果:
说明:
左连接=左外连接,left join on = left outer join on。右表记录不足的地方为null
四、 右(外)连接
关键字:right join on 或 **right outer join on **
语句:
select *from a_table a right outer join b_table b on a.a_id = b.b_id;
结果:
说明:
右连接=右外连接,right join on = right outer join on。左表记录不足的地方为null
五、 左表独有
关键字:left outer join on 、 is null
语句:
select *from a_table a left outer join b_table b on a.a_id = b.b_id where b.b_id is null;
结果:
说明:
两表关联,查询左表独有的数据。(注意:左表独有,右表 id 为空)
六、 右表独有
关键字:right outer join on、 is null
语句:
select *from a_table a right outer join b_table b on a.a_id = b.b_id where a.a_id is null;
结果:
说明:
两表关联,查询右表独有的数据。(注意:右表独有,左表 id 为空)
七、 全连接
关键字:left outer join on 、 union、 right outer join on
语句:
select *from a_table a left outer join b_table b on a.a_id = b.b_id
UNION
select *from a_table a right outer join b_table b on a.a_id = b.b_id;
结果:
说明:
在oracle中 有 full join 进行全连接,mysql中没有,所以可以用 一个左连接 并上 一个右连接
八、 并集去交集(左右表独有)
关键字:left outer join on 、 — 、 union、 right outer join on
语句:
(select *from a_table a left join b_table b on a.a_id = b.b_id
UNION
select *from a_table a RIGHT JOIN b_table b on a.a_id = b.b_id)
-
select *from a_table a where a.a_id = b.b_id
结果:
说明:
减号 前面求并集,减号 后面求交集, 相减就是结果。
但是还可以有简便做法,逻辑上等价于
左表独有 并上 右表独有
那么语句就是
select *from a_table a left outer join b_table b on a.a_id = b.b_id where b.b_id is null
UNION
select *from a_table a right outer join b_table b on a.a_id = b.b_id where a.a_id is null;
九、 笛卡尔积
假设现在有两集合:
A = {0,1} B = {2,3,4}
集合 A×B 和 B×A的结果集就可以分别表示为以下这种形式:
A×B = {(0,2),(1,2),(0,3),(1,3),(0,4),(1,4)};
B×A = {(2,0),(2,1),(3,0),(3,1),(4,0),(4,1)};
以上A×B和B×A的结果就可以叫做两个集合相乘的‘笛卡尔积’。
从以上的数据分析我们可以得出以下两点结论:
1,两个集合相乘,不满足交换率,既 A×B ≠ B×A;
2,A集合和B集合相乘,包含了集合A中元素和集合B中元素相结合的所有的可能性。既两个集合相乘得到的新集合的元素个数是 A集合的元素个数 × B集合的元素个数;
(A × B ≠ B × A)
先来看一下笛卡尔积的图示,
关键字:join 或 cross join
A * B 笛卡尔积:
语句:
select *from a_table a cross join b_table b;
结果:
B * A 笛卡尔积:
语句:
select *from a_table a join b_table b;