1、内连接:把两张表有对应关系的记录连接成一张虚拟表
select * from emp inner join dep on emp.dep_id = dep.id;
#应用:
select * from emp,dep where emp.dep_id = dep.id and dep.name = "技术"; # 不要用where做连表的活
select * from emp inner join dep on emp.dep_id = dep.id
where dep.name = "技术"
;
2、左连接:在内连接的基础上,保留左边没有对应关系的记录
select * from emp left join dep on emp.dep_id = dep.id;
3、右连接:在内连接的基础上,保留右边没有对应关系的记录
select * from emp right join dep on emp.dep_id = dep.id;
4、全连接:在内连接的基础上,保留左、右边没有对应关系的记录
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;
#补充:多表连接可以不断地与虚拟表连接
查找各部门最高工资
select t1.* from emp as t1
inner join
(select post,max(salary) as ms from emp group by post) as t2
on t1.post = t2.post
where t1.salary = t2.ms
;