内连接和外连接的区别

内连接：只会把两张表中能够完全匹配上的数据查出来

外连接：分为左外连接和右外连接，相同条件下，外连接比内连接查的多，外连接能够查出残缺的数据。

 

什么时候用外连接?

如果想将其中一张表的数据全部显示出来的时候用表的外连接.

对于没有匹配上的数据，其中一张表会自动模拟出NULL值进行匹配，达到将一张表数据全部显示出来。

drop table employees;
drop table departments;
--创建数据表
--父表
create table departments(
       department_id int(10),
       department_name varchar(15)
);
--子表
create table employees(
       employee_id int(3),
       employee_name varchar(15),
       salary int(15),
       department_id int(10)
);
insert into departments(department_id,department_name) values(10,'开发部');
insert into departments(department_id,department_name) values(20,'测试部');
insert into departments(department_id,department_name) values(30,'财务部');
insert into departments(department_id,department_name) values(40,'人力资源部');
insert into employees(employee_id,employee_name,salary,department_id) values(1,'张三',10000,'10');
insert into employees(employee_id,employee_name,salary,department_id) values(2,'李四',10000,'20');
insert into employees(employee_id,employee_name,salary,department_id) values(3,'王五',10000,'30');
insert into employees(employee_id,employee_name,salary,department_id) values(4,'陈伟',10000,null);
commit;


--第一种写法
--内连接
select e.employee_id,e.employee_name,e.salary,d.department_name
from employees e , departments d
where e.department_id = d.department_id;
--左外连接
select e.employee_id,e.employee_name,e.salary,d.department_name
from employees e , departments d
where e.department_id = d.department_id(+);
--右外连接
select e.employee_id,e.employee_name,e.salary,d.department_name
from employees e , departments d
where e.department_id(+) = d.department_id;


--第二种写法(sql99标准增加的)
--内连接
select e.employee_id,e.employee_name,e.salary,d.department_name
from employees e join departments d
on (e.department_id = d.department_id);
--左外连接
select e.employee_id,e.employee_name,e.salary,d.department_name
from employees e left outer join departments d
on (e.department_id = d.department_id);
--右外连接
select e.employee_id,e.employee_name,e.salary,d.department_name
from employees e right outer join departments d
on (e.department_id = d.department_id);