mysql join
use test
drop table A;
drop table B;
create table A(a int not null);
insert into A (a) values(1);
insert into A (a) values(2);
insert into A (a) values(3);
insert into A (a) values(4);
create table B(b int not null);
insert into B (b) values(3);
insert into B (b) values(4);
insert into B (b) values(5);
insert into B (b) values(6);
insert into B (b) values(7);
select * from A;
select * from B;
select * from A where a<4; #子集合
select * from A , B; # 笛卡尔积
select * from A inner join B; # 笛卡尔积
select * from A cross join B; # 笛卡尔积
select * from A nature join B; # 笛卡尔积
select * from A natura join B; # 笛卡尔积
select * from A,B where A.a= B.b; #交集
select * from A inner join B on A.a=B.b; #交集 (内连接)
select A., B. from A left join B on A.a=B.b; #左连接
select A., B. from A right join B on A.a=B.b; #右连接
select A., B. from A left join B on A.a=B.b where B.b is null; #差集
select A., B. from A right join B on A.a=B.b where A.a is null; #差集
select * from A left join B on A.a=B.b union select * from A right join B on A.a=B.b; #并集(mysql没有outer join)
MySQL不支持OUTER JOIN，但是我们可以对左连接和右连接的结果做UNION操作来实现
select * from A left join B on A.a=B.b where B.b is null union select * from A right join B on A.a=B.b where A.a is null; #对称差集(mysql没有outer join)
笛卡尔积
+---+---+
| a | b |
+---+---+
| 1 | 3 |
| 2 | 3 |
| 3 | 3 |
| 4 | 3 |
| 1 | 4 |
| 2 | 4 |
| 3 | 4 |
| 4 | 4 |
| 1 | 5 |
| 2 | 5 |
| 3 | 5 |
| 4 | 5 |
| 1 | 6 |
| 2 | 6 |
| 3 | 6 |
| 4 | 6 |
| 1 | 7 |
| 2 | 7 |
| 3 | 7 |
| 4 | 7 |
+---+---+
打印A表
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+
打印B表
+---+
| b |
+---+
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+---+
打印交集
+---+---+
| a | b |
+---+---+
| 3 | 3 |
| 4 | 4 |
+---+---+
打印交集
+---+---+
| a | b |
+---+---+
| 3 | 3 |
| 4 | 4 |
+---+---+
左连接
+---+------+
| a | b |
+---+------+
| 3 | 3 |
| 4 | 4 |
| 1 | NULL |
| 2 | NULL |
+---+------+
右连接
+------+---+
| a | b |
+------+---+
| 3 | 3 |
| 4 | 4 |
| NULL | 5 |
| NULL | 6 |
| NULL | 7 |
+------+---+
左差集
+---+------+
| a | b |
+---+------+
| 1 | NULL |
| 2 | NULL |
+---+------+
右差集
+------+---+
| a | b |
+------+---+
| NULL | 5 |
| NULL | 6 |
| NULL | 7 |
+------+---+
并集
+------+------+
| a | b |
+------+------+
| 3 | 3 |
| 4 | 4 |
| 1 | NULL |
| 2 | NULL |
| NULL | 5 |
| NULL | 6 |
| NULL | 7 |
+------+------+
对称差集
+------+------+
| a | b |
+------+------+
| 1 | NULL |
| 2 | NULL |
| NULL | 5 |
| NULL | 6 |
| NULL | 7 |
+------+------+
