mysql 表的连接语句(包括纵向合并)

用到的表: t1 t2

t1表

key1	v1
a	1
b	2
c	3
d	4

 

 

 

 t2表

 

key2	v2
a	1
b	2
c	3
d	4

 

 

 1 -- 左连接

select * from t1 left join t2 on key1 = key2;

2 -- 左连接 , t1-t2

select * from t1 left join t2 on key1 = key2
where key2 is null;

3 -- 右连接

select * from t1 right join t2 on key1 = key2;

4 -- 右连接 t2-t1

select * from t1 right join t2 on key1 = key2
where t1 is null;

5 -- 内连接

select * from t1 inner join t2 on key1 = key2;

6 -- 全连

select * from t1 full outer join t2 on key1 = key2; 

7 -- t1＋t2 - t1&t2

select * from t1 full outer join t2 on key1 = key2
where key1 is null or key2 is null;

8 -- 纵向合并 union 合并后删除重复值

select * from t1
union 
select * from t2;

9 -- 纵向合并 union all 合并后保留重复值

select * from t1
union all
select * from t2;