Oracle笔记05——Oracle多表连接

一、笛卡尔积：第一个表中的所有列和第二个表中的所有行都发生连接
笛卡尔积产生条件：
①连接条件被省略
②连接条件是无效的
为了避免笛卡尔积的产生，通常需要在WHERE子句中包含一个有效的连接条件

 

二、等值连接
1.N张表关联，至少有N-1个关联条件
2.可以使用AND运算符增加其它查询条件(注意：先写关联语句再写其它查询条件，否则效率会降低)
3.使用表明作为前缀，限制歧义列名，也可以提高查询效率
4.多表查询可以通过别名，选择该表中的列

 

三、非等值连接
1.N张表关联，至少有N-1个关联条件
2.不使用等号书写判断语句

 

四、外部连接
在多表连接时，可以使用外部连接来查看哪些行，按照连接条件没有被匹配上
基本语法：
SELECT 表1.列名, 表2.列名 FROM 表1, 表2 WHERE 表1.列名(+) = 表2.列名;
或者
SELECT 表1.列名, 表2.列名 FROM 表1, 表2 WHERE 表1.列名 = 表2.列名(+);

 

五、内部连接
自身连接，也叫自连接，是一个表通过某种条件和本身进行连接的一种方式，就如同多个表连接一样

 

六、交叉连接
交叉连接会产生两个表的交叉乘积，和两个表之间笛卡尔积是一样的
基本语法：
SELECT 表1.列名, 表2.列名, ... FROM 表1 CROSS JOIN 表2;

 

七、自然连接
自然连接是对两个表之间相同名字和数据类型的列进行的等值连接
基本语法：
SELECT 表1.列名, 表2.列名, ... FROM 表1 NATURAL JOIN 表2;
注意：如果两个表之间相同名称的列的数据类型不同，则会产生错误

 

八、ON子句
如果要指定任意连接条件，或指定要连接的列，则可以用ON子句
用ON将连接条件和其它检索条件分隔开，其它检索条件写在WHERE
基本语法：
SELECT 表1.列名, 表2.列名, ... FROM 表1 JOIN 表2 ON(条件);

 

九、USING子句
①指定列名
②要求列名类型一致
③USING子句中的用到的列不能使用表明和别名作为前缀
④NATRUAL JOIN子句和USING子句是相互排斥的，不能同时使用
基本语法：
SELECT 表1.列名, 表2.列名, ... FROM 表1 JOIN 表2 USING(两表之间相同的列名);

 

十、左外连接
左外连接以左边的表为主表，该表所有行数据按照连接条件无论是否与右边表能匹配上，都会显示出来
基本语法：
SELECT 表1.列名, 表2.列名, ... FROM 表1 LEFT OUTER JOIN 表2 ON(条件);

 

十一、右外连接
右外连接以右边的表为主表，该表所有行数据按照连接条件无论是否与左边表能匹配上，都会显示出来
基本语法：
SELECT 表1.列名, 表2.列名, ... FROM 表1 RIGHT OUTER JOIN 表2 ON(条件);

 

十二、全外连接
全外连接返回两个表等值连接结果，以及两个表中所有等值连接失败的记录
基本语法：
SELECT 表1.列名, 表2.列名, ... FROM 表1 FULL OUTER JOIN 表2 ON(条件);

 

十三、练习

--笛卡尔积：
SELECT * FROM emp,dept;

--等值连接
SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;
--等同于
SELECT emp.*, dept.* FROM emp,dept WHERE emp.deptno = dept.deptno;

--三张表的连接
SELECT * FROM customer,ord,item WHERE customer.custid = ord.custid AND ord.ordid = item.ordid;--多表链接
SELECT * FROM customer c,ord o,item i WHERE c.custid = o.custid AND o.ordid = i.ordid;--使用表别名

--查询工作地点在NEW YORK的员工编号，姓名，部门编号，工作地点
SELECT empno,ename,emp.deptno,loc FROM emp,dept WHERE emp.deptno = dept.deptno AND loc = 'NEW YORK';--效率高
SELECT empno,ename,emp.deptno,loc FROM emp,dept WHERE loc = 'NEW YORK' AND emp.deptno = dept.deptno ;--效率低

--限制歧义列名
SELECT emp.empno,emp.ename,emp.deptno,emp.loc FROM emp,dept WHERE emp.deptno = dept.deptno AND dept.loc = 'NEW YORK';--效率高
SELECT empno,ename,emp.deptno,loc FROM emp,dept WHERE emp.deptno = dept.deptno AND loc = 'NEW YORK';--效率低

--非等值连接
--查询每个员工的姓名，工资，工资等级
SELECT ename, sal, grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;

--外部连接
--1.查询所有的员工信息，没有雇员工作的部门(40)也要显示出来
SELECT * FROM emp,dept WHERE emp.deptno(+) = dept.deptno;--emp表为从表 dept表为主表 (+)表示加上空行

--2.查询所有的员工信息，没有部门的员工(打杂的)也要显示出来
SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno(+);--emp表为主表 dept表为从表 (+)表示加上空行

--内部连接
--查询每个员工的姓名和直接上级姓名
SELECT e1.ename 员工姓名, e2.ename 直接上级姓名 FROM emp e1, emp e2 WHERE e1.mgr = e2.empno;

--交叉连接
SELECT * FROM emp CROSS JOIN dept;
--相当于
SELECT * FROM emp, dept;

--自然连接
SELECT * FROM emp NATURAL JOIN dept;

--USING子句
SELECT * FROM emp JOIN dept USING(deptno);

--on子句
--1.查询员工的员工信息与部门信息
SELECT * FROM emp JOIN dept ON emp.deptno = dept.deptno;--两张表
--相当于
SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;

--2.查询员工的员工信息与部门信息、以及经理信息和薪资等级信息
SELECT *
  FROM emp e
  JOIN dept d
    ON e.deptno = d.deptno
  JOIN emp m
    ON e.empno = m.mgr
  JOIN salgrade s
    ON e.sal BETWEEN s.losal AND s.hisal;--多张表

--左外连接
--将没有部门的员工也显示出来
SELECT * FROM emp LEFT JOIN dept ON emp.deptno = dept.deptno;--emp主表 dept从表
--相当于
SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno(+);

--右外连接
--将没有员工的部门也显示出来
SELECT * FROM emp RIGHT JOIN dept ON emp.deptno = dept.deptno;--dept主表 emp从表
--相当于
SELECT * FROM emp, dept WHERE emp.deptno(+) = dept.deptno;

--全外连接
--将没有部门的员工和没有员工的部门显示出来
SELECT * FROM emp FULL OUTER JOIN dept ON emp.deptno = dept.deptno;