多表查询——详解

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
;