首页 mysql基础教程[basic] 详情

---

join类似于excle中的vlookup，用于匹配另外一张表的字段，对表做横向扩展。union可以把多张表纵向拼接，用于扩充记录

join：匹配字段

横向扩展字段，类似于excel的vlookup匹配 样例数据：

create table student(
    sid varchar(10),
    sname varchar(10),
    cid varchar(10)
);
insert into student values
('001','张三','c01'),
('002','李四','c01'),
('003','王五','c02'),
('004','赵六','c03'),
('005','刘能','c04'),
('006','宋小宝','c04');

create table class(
    cid varchar(10),
    cname varchar(10)
);
insert into class values
('c01','一班'),
('c03','三班'),
('c05','五班');

inner join/join(内连接，inner 可以省略)

-- 相当于两个数据集取交集，也就是只保留匹配到的记录
select t1.*,t2.cname 
from student t1
join class t2 on t2.cid = t1.cid 
;

left join/right join(外连接)

-- left join：保留左表全部记录，和右表匹配到的记录
select t1.*,t2.cname 
from student t1
left join class t2 on t2.cid = t1.cid 
;

-- right join：保留右表全部记录，和左表匹配到的记录
select t1.*,t2.cname 
from student t1
right join class t2 on t2.cid = t1.cid 
;

cross join(笛卡尔积)

select t1.*,t2.cname 
from student t1
cross join class t2

union/union all

将多个具有相同字段并且字段顺序相同的表纵向拼接

union：拼接时，默认对所有的记录去重

select 1 as a,2 as b
union 
select 1 as a,2 as b

union all :拼接时，不对记录去重

select 1 as a,2 as b
union all
select 1 as a,2 as b