sql连接查询
创建两张表
(一)、 INNER JOIN
SELECT * FROM TableA INNER JOIN TableB ON TableA.name = TableB.name
运行结果:
1、select * from employee inner join dept;
2、select * from employee as e inner join dept as d on e.d_id=d.dept_id;
3、select last_name,email,gender,d_id from employee as e inner join dept as d on e.d_id=d.dept_id;
(二)、LEFT [OUTER] JOIN
SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableB.id IS null;
运行结果:
1、select * from employee as e left outer join dept as d on e.d_id=d.dept_id;
2、select * from employee as e left outer join dept as d on e.d_id=d.dept_id where d.dept_id is null;
3、select * from employee as e left outer join dept as d on e.d_id=d.dept_id where d.dept_id is not null;
(三)、RIGHT [OUTER] JOIN
运行结果:
1、select * from employee as e right outer join dept as d on e.d_id=d.dept_id;
2、select * from employee as e right outer join dept as d on e.d_id=d.dept_id where e.emp_id is null;
3、select * from employee as e right outer join dept as d on e.d_id=d.dept_id where e.emp_id is not null;
(四)、full outer join是oracle支持的,mysql不支持,mysql要实现后两种的效果,需要将 left join 和 right join 用union关键字连接起来(union有合并并去重的功效)。
mysql:left join + union (去除重复数据) + right join
运行结果:
1、select * from employee as e left join dept as d on e.d_id=d.dept_id;
2、select * from employee as e right join dept as d on e.d_id=d.dept_id;
3、select * from employee as e left join dept as d on e.d_id=d.dept_id union select * from employee as e right join dept as d on e.d_id=d.dept_id;