oracle多表查询

朴灿烈づ我的快乐病毒、 2022-08-01 11:16 236阅读 0赞

# 多表查询基本语法 #

笛卡尔积在SQL中的实现方式既是交叉连接(Cross Join)。所有连接方式都会先生成临时笛卡尔积表，笛卡尔积是关系代数里的一个概念，表示两个表中的每一行数据任意组合。

-- 笛卡尔积 
    select * from emp, dept;

![SouthEast][]

-- 使用公共字段，去掉笛卡尔积
    select * from emp, dept where emp.deptno = dept.deptno;

![SouthEast 1][]

-- 查询出每个雇员的姓名，工作，雇员的直接上级领导的姓名
    -- 在emp表中的MGR表示一个雇员的上级领导的编号，如果现在要想查询一个雇员的上级领导姓名，则需要用emp表与emp表进行自连接操作
    select e.ename, e.job, s.ename from emp s, emp e where s.empno = e.mgr; 
    
    -- 查询出每个雇员的姓名，工作，雇员的直接上级领导的姓名，雇员所在的部门名称
    select e.ename, e.job,s.ename mgr_name, d.dname from emp e, emp s, dept d where e.mgr = s.empno and e.deptno = d.deptno;
    
    -- 查询出每个雇员的姓名，工资，部门名称，工资在公司的等级及其领导的姓名，领导的工资，以及领导所对应的等级
    SELECT e.ename, e.sal, d.dname,
    decode(g.grade,5,'第一等工资',4,'第二等工资',3,'第三等工资',2,'第四等工资',1,'第五等工资')
    e_grade, s.ename mgr_name, s.sal mgr_sal,
    decode(g2.grade,5,'第一等工资',4,'第二等工资',3,'第三等工资',2,'第四等工资',1,'第五等工资') m_grade
    FROM emp e,dept d,salgrade g,emp s,salgrade g2
    WHERE (e.deptno=d.deptno) AND (e.sal BETWEEN g.losal AND g.hisal)
    AND (s.empno=e.mgr) AND (s.sal BETWEEN g2.losal AND g2.hisal);

# 左右连接 #

-- 利用emp 和 dept 做一个连接查询，查询结果包括雇员编号，雇员姓名，部门编号，部门名称，部门所在位置
    select e.empno, e.ename, d.deptno, d.dname, d.loc
    from emp e,dept d
    where e.deptno = d.deptno;

![SouthEast 2][]

-- 通过观察不难发现，在以上结果中部门编号40没有出现,这是因为在进行普通连接时两边都有的才出现在结果中,在emp表中没有部门40,所以结果中也就没有40
    select e.empno, e.ename, d.deptno, d.dname, d.loc
    from emp e, dept d
    where e.deptno(+) = d.deptno;

![SouthEast 3][]

部门40就出现在结果中了.此时我们使用的是右连接。

**      (+)在=左边 表示右连接  
      (+)在=右边 表示左连接**

**      左连接以左表为基准，右连接以右表为基准**

-- 左连接以左表为基准，右连接以右表为基准
    select e.empno, e.ename, d.deptno, d.dname, d.loc
    from emp e, dept d
    where e.deptno = d.deptno(+);

-- 查询雇员的编号，姓名，及其领导的编号，姓名
    select e.empno, e.ename, s.empno, s.ename
    from emp e, emp s
    where e.mgr = s.empno;

-- 但我们经查询知道emp表中应该有14条记录,在以上结果中缺少了KING,因为KING是最高领导，所以他的领导编号为空，故没办法查询出来.为了显示出来，我们用左/右连接来实现
    select e.empno, e.ename, s.empno, s.ename
    from emp e, emp s
    where e.mgr = s.empno(+);

![SouthEast 4][]

-- 左右连接的连接方式不是固定的，具体采用左连接还是右连接取决于基准条件在等式的左边还是右边，上面的左连接把基准条件放在右边，就变为了右连接
    select e.empno, e.ename, s.empno, s.ename
    from emp e, emp s
    where s.empno(+) = e.mgr;

得到的同样是上图的结果值，这里就不重复贴图了。

# SQL:1999SQL定义 #

语法格式

SELECT table1.column,table2.column

FROM table1 \[CROSS JOIN table2\]|

\[NATURAL JOIN table2\]|

\[JOIN table2 USING(column\_name)\]|

\[JOIN table2 ON(table1.column\_name=table2.column\_name)\]|

\[LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column\_name=table2.column\_name)\]

-- 交叉连接(CROSS JOIN):迪卡尔积运算
    select * from emp cross join dept;

![SouthEast 5][]

select * from emp cross join dept where emp.empno = 7369;

![SouthEast 6][]

-- 自然连接 NATURAL JOIN
    select * from emp natural join dept;

![SouthEast 7][]

-- 根据结果可以发现，自然连接按公共字段相等进行连接，并且产生的结果会自动消除重复的列
    select * from emp e, dept d
    where e.deptno = d.deptno;

![SouthEast 8][]

-- USING(column_name) 用于指定两个表之间的连接字段
    select * from emp join dept using(deptno);

![SouthEast 9][]

-- ON(tab1.column_name=tab2.column_name) 用于指定两表的连接条件
    select * from emp join dept on emp.deptno = dept.deptno;

![SouthEast 10][]

-- LEFT[OUTER] JOIN 左连接 OUTER可有可无
    select e.empno, e.ename, d.deptno, d.dname, d.loc
    from emp e left join dept d
    on e.deptno = d.deptno;
    select e.empno, e.ename, d.deptno, d.dname, d.loc
    from emp e left outer join dept d
    on e.deptno = d.deptno;

![SouthEast 11][]

-- RIGHT JOIN 右连接
    select e.empno, e.ename, d.deptno, d.dname, d.loc
    from emp e right join dept d
    on e.deptno = d.deptno;

![SouthEast 12][]

-- INNER JOIN 取交集
    create table empbak as select * from emp where emp.empno in (7369, 7499, 7521, 7566, 7654, 7698);
    select e1.empno, e1.empno, e1.job, e1.mgr, e1.hiredate, e1.sal, e1.comm, e1.deptno 
    from emp e1 inner join empbak e2 on e1.empno = e2.empno;

![SouthEast 13][]

-- FULL OUTER JOIN 取并集
    update empbak e set e.empno = 8369 where e.empno = 7369;
    update empbak e set e.empno = 8499 where e.empno = 7499;
    update empbak e set e.empno = 8521 where e.empno = 7521;
    select * from emp e full join empbak s on e.empno = s.empno;

![SouthEast 14][]

-- 对于没有匹配的记录，则会以null做为值
    select * from emp e full join empbak s on e.empno = s.empno
    where e.empno is not null and s.empno is not null;

![SouthEast 15][]

-- UNION 取并集 去掉重复记录
    select * from emp
    union
    select * from empbak;

![Center][]

-- UNION ALL 取并集 不去除重复记录
    select * from emp
    union all
    select * from empbak;

![Center 1][]

最下面的三条记录是重复记录，在上面的union连接中，多余的三条重复记录是被去掉的。

[SouthEast]: /images/20220731/617204d869474bafa6a986ede950246c.png
[SouthEast 1]: /images/20220731/888e36c48dcf463bb79d95fe94f469c1.png
[SouthEast 2]: /images/20220731/faf25b9b7cc446719d6d45fbaad39df4.png
[SouthEast 3]: /images/20220731/0d330f4a9e0444d9880af7dc41663e05.png
[SouthEast 4]: /images/20220731/eab636d19c0841b990de0bf72a2d555e.png
[SouthEast 5]: /images/20220731/28949eefd3394a6c95fe31ba21672a1e.png
[SouthEast 6]: /images/20220731/c246c116d1a14f5098909640db7bec33.png
[SouthEast 7]: /images/20220731/23f1f488979a4a6a8281e7169b7e8a98.png
[SouthEast 8]: /images/20220731/bb47985208974b72b05e6c0a4840ed07.png
[SouthEast 9]: /images/20220731/02914f955a474fb682b5d812d16e832c.png
[SouthEast 10]: /images/20220731/34a8778aa0904bccab5894878c68ec74.png
[SouthEast 11]: /images/20220731/a3a3a563e2174dc8a7f8d577091abee7.png
[SouthEast 12]: /images/20220731/470011cb7a744ea9a4d276d0543fc948.png
[SouthEast 13]: /images/20220731/836021add0e74071b5159b62100e2e53.png
[SouthEast 14]: /images/20220731/9d680d0c181045fd840737a3977fe3db.png
[SouthEast 15]: /images/20220731/d6f969102445434792c49dc304bf6aad.png
[Center]: /images/20220731/2035645dd44245f4b6adbf3246011af7.png
[Center 1]: /images/20220731/8ec34e30a7d3479fa09f5c342cb5f6fe.png