数据准备
数据表1：
CREATE TABLE FDM_SOR.mytest_department
(
dept_no int,
dept_name string
) row format delimited fields terminated by ‘,’
101,’it’
102,’finance’
103,’mannager’
104,’dining’
105,’boss-office’
107,’enjoy’
108,’fun’
数据表2：
CREATE TABLE FDM_SOR.mytest_staffinfo
(
id int,
name string,
sex string,
dept_no int
) row format delimited fields terminated by ‘,’
001,’tom’,’man’,101
002,’jerry’,’man’,103
003,’marry’,’woman’,101
004,’tom’,’man’,104
005,’jebby’,’man’,102
006,’smiiss’,’man’,101
007,’dosos’,’man’,102
008,’tiny’,’woman’,102
009,’feyy’,’woman’,104
010,’feyy’,’woman’,103
011,’cake’,’man’,101
012,’dogy’,’man’,102
013,’gaayy’,’man’,105
014,’kety’,’man’,
015,’caggyy’,’man’,104
016,’addy’,’man’,106
017,’nancy’,’man’,104
018,’tom’,’man’,101
数据表3：
CREATE TABLE FDM_SOR.mytest_deptaddr
(
dept_no int,
addr string,
tel string
)
row format delimited fields terminated by ‘,’
109,’new york109’,’931232323’
108,’new york108’,’431232323’
107,’new york107’,’531232323’
106,’new york106’,’531232323’
101,’new york101’,’431232323’
104,’new york104’,’131232323’
103,’new york103’,’131232323’
102,’new york102’,’131232323’
105,’new york105’,’131232323’
查询实践
1.内连接：inner join或join ：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被留下来
select a.id ,a.name,b.dept_no,b.dept_name
from FDM_SOR.mytest_staffinfo a
inner join
FDM_SOR.mytest_department b
on a.dept_no = b.dept_no
1 ‘tom’ 101 ‘it’
2 ‘jerry’ 103 ‘mannager’
3 ‘marry’ 101 ‘it’
4 ‘tom’ 104 ‘dining’
5 ‘jebby’ 102 ‘finance’
6 ‘smiiss’101 ‘it’
7 ‘dosos’ 102 ‘finance’
8 ‘tiny’ 102 ‘finance’
9 ‘feyy’ 104 ‘dining’
10 ‘feyy’ 103 ‘mannager’
11 ‘cake’ 101 ‘it’
12 ‘dogy’ 102 ‘finance’
13 ‘gaayy’ 105 ‘boss-office’
15 ‘caggyy’104 ‘dining’
17 ‘nancy’ 104 ‘dining’
18 ‘tom’ 101 ‘it’
- 左外连接：left join或left outer join
A left join B是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
意思说,左表(A)的记录将会全部表示出来(不管右边的表中是否存在与它们匹配的行),而右表(B)只会显示符合搜索条件的记录，比如符合on,where中的条件。B表记录不足的地方均为NULL. A left join B 等价B right join A 。
select a.id ,a.name,b.dept_no,b.dept_name
from FDM_SOR.mytest_staffinfo a
left join FDM_SOR.mytest_department b
on a.dept_no = b.dept_no
1 ‘tom’ 101 ‘it’
2 ‘jerry’ 103 ‘mannager’
3 ‘marry’ 101 ‘it’
4 ‘tom’ 104 ‘dining’
5 ‘jebby’ 102 ‘finance’
6 ‘smiiss’101 ‘it’
7 ‘dosos’ 102 ‘finance’
8 ‘tiny’ 102 ‘finance’
9 ‘feyy’ 104 ‘dining’
10 ‘feyy’ 103 ‘mannager’
11 ‘cake’ 101 ‘it’
12 ‘dogy’ 102 ‘finance’
13 ‘gaayy’ 105 ‘boss-office’
14 ‘kety’ NULL NULL
15 ‘caggyy’104 ‘dining’
16 ‘addy’ NULL NULL
17 ‘nancy’ 104 ‘dining’
18 ‘tom’ 101 ‘it’
3.右外连接：right join或right outer join
同理和left join相反，A right join B ,则显示B表中所有的记录，A表不足的用null填充
select a.id ,a.name,b.dept_no,b.dept_name
from FDM_SOR.mytest_staffinfo a
right outer join FDM_SOR.mytest_department b
on a.dept_no = b.dept_no
1 ‘tom’ 101 ‘it’
3 ‘marry’ 101 ‘it’
6 ‘smiiss’101 ‘it’
11 ‘cake’ 101 ‘it’
18 ‘tom’ 101 ‘it’
5 ‘jebby’ 102 ‘finance’
7 ‘dosos’ 102 ‘finance’
8 ‘tiny’ 102 ‘finance’
12 ‘dogy’ 102 ‘finance’
2 ‘jerry’ 103 ‘mannager’
10 ‘feyy’ 103 ‘mannager’
4 ‘tom’ 104 ‘dining’
9 ‘feyy’ 104 ‘dining’
15 ‘caggyy’104 ‘dining’
17 ‘nancy’ 104 ‘dining’
13 ‘gaayy’ 105 ‘boss-office’
NULL NULL 107 ‘enjoy’
NULL NULL 108 ‘fun’
4.全外连接： full join : full outer join
包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行 在功能上，它等价于对这两个数据集合分别进行左外连接和右外连接，然后再使用消去重复行的并操作将上述两个结果集合并为一个结果集。
select a.id ,a.name,b.dept_no,b.dept_name
from FDM_SOR.mytest_staffinfo a
full join FDM_SOR.mytest_department b
on a.dept_no = b.dept_no
order by a.id
NULL NULL 107 ‘enjoy’
NULL NULL 108 ‘fun’
1 ‘tom’ 101 ‘it’
2 ‘jerry’ 103 ‘mannager’
3 ‘marry’ 101 ‘it’
4 ‘tom’ 104 ‘dining’
5 ‘jebby’ 102 ‘finance’
6 ‘smiiss’ 101 ‘it’
7 ‘dosos’ 102 ‘finance’
8 ‘tiny’ 102 ‘finance’
9 ‘feyy’ 104 ‘dining’
10 ‘feyy’ 103 ‘mannager’
11 ‘cake’ 101 ‘it’
12 ‘dogy’ 102 ‘finance’
13 ‘gaayy’ 105 ‘boss-office’
14 ‘kety’ NULL NULL
15 ‘caggyy’ 104 ‘dining’
16 ‘addy’ NULL NULL
17 ‘nancy’ 104 ‘dining’
18 ‘tom’ 101 ‘it’