内连接和外连接的区别

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

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

什么时候用外连接?

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

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);

最后

以上就是自信可乐为你收集整理的内连接和外连接的区别的全部内容，希望文章能够帮你解决内连接和外连接的区别所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。