SQL 多表查询

多表查询的分类

• 等值连接 VS 非等值连接
• 自连接 VS 非自连接
• 内连接 VS 外连接

等值连接

关联的表有连接字段

select ci.vbillno, ci.infodate, ci.bankaccount, ci.oppbankaccount,bb.accname as oppunitname, oo.code as pk_org, ci.moneyy, ci.memo, bc.code as currtypeCode
from cmp_informer ci
left join bd_currtype bc on bc.pk_currtype = ci.pk_currtype
left join org_orgs oo on oo.pk_org = ci.pk_org
left join bd_bankaccbas bb on bb.accnum = ci.oppbankaccount
where ci.dr = 0
	and ci.direction = 'receivemoney'
	and ci.generateflag ='hasrelease'
1
2
3
4
5
6
7
8

非等值连接

关联的表没有连接字段

select e.last_name,e.salary,j.grade_level
from employees e,job_grades j
where e.salary between j.lowest_sal and j.highest_sal
1
2
3

自连接

自己连接自己

select emp.employee_id,emp.last_name,mgr.empliyee_id,mgr.last_name
from employees emp,employees mgr
where emp.manager_id = mgr.employee_id
1
2
3

非自连接

不同的表连接查询

内连接

只查询左表和右表满足where条件的数据，也就是合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行。

select last_name,dapartment_name,city
from employees e 
inner join departments d on e.department_id = d.department_id
inner join locations l on d.locateion_id = l.location_id
1
2
3
4

外连接

只查询左表和右表不满足where条件的数据，也就是合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行之外，还查询到左表或右表中不匹配的行。

左外连接

两个表在连接过程中除了返回满足连接条件的行以外，还返回左表中不满足条件的行

select last_name,dapartment_name,city
from employees e 
left outer join departments d on e.department_id = d.department_id
left outer join locations l on d.locateion_id = l.location_id
1
2
3
4

右外连接

两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行

select last_name,dapartment_name,city
from employees e 
right outer join departments d on e.department_id = d.department_id
right outer join locations l on d.locateion_id = l.location_id
1
2
3
4

满外连接

mysql 不支持 full outer join

-- oracle
select last_name,dapartment_name
from employees e 
full outer join departments d on e.department_id = d.department_id

-- mysql
-- UNION 会执行去重操作  UNION ALL 不会执行去重操作
--左上图 UNION ALL 右中图
select last_name,dapartment_name
from employees e 
left join departments d on e.department_id = d.department_id
union all
select last_name,dapartment_name
from employees e 
right join departments d on e.department_id = d.department_id
where e.department_id IS NULL;

--左中图 UNION ALL 右上图
select last_name,dapartment_name
from employees e 
left join departments d on e.department_id = d.department_id
where d.department_id IS NULL;
union all
select last_name,dapartment_name
from employees e 
right join departments d on e.department_id = d.department_id

--右下图 = 左中图 UNION ALL 右中图
select last_name,dapartment_name
from employees e 
left join departments d on e.department_id = d.department_id
where d.department_id IS NULL;
union all
select last_name,dapartment_name
from employees e 
right join departments d on e.department_id = d.department_id
where e.department_id IS NULL;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

SQL连接 JOIN

SQL99 语法新特性 自然连接 NATURAL JOIN & USING

-- 自然连接 NATURAL JOIN 
-- 可以把自然连接理解为SQL92中的等值连接，它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。
--不适用于两张表中有多个关联字段，你只想要某一个字段关联
select employee_id,last_name,department_name
from employees e
NATURAL JOIN departments d
--等同于
select employee_id,last_name,department_name
from employees e 
join departments d on e.department_id = d.department_id
and e.manager_id = d.manager_id

-- USING连接
--USING(同名字段)，简化JOIN ON
--不适用于自连接，也就是自己引用自己的表
select employee_id,last_name,department_name
from employees e 
join departments d USING(department_id)
--等同于
select employee_id,last_name,department_name
from employees e 
join departments d on e.department_id = d.department_id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22