sql两张表纵向合并_mysql 表的连接语句(包括纵向合并)

用到的表: t1 t2

t1表

t2表

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;