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;