SQL中的笛卡儿积问题和多表连接操作

（scott账户）

 

SELECT * FROM scott.dept;--4
SELECT * FROM scott.emp;--14

/**
笛卡尔积
内连接（等值连接）
外连接（非等值连接）
自连接
*/

--笛卡尔积
--当查询数据时没有使用连接条件，会查出所有关联数据
--4*14=56
SELECT * FROM scott.dept,scott.emp;

--注意：多表连接查询一定要带关联条件，否则就会出现笛卡尔积
--总结：多表连接第一步就是找关联条件
--emp.deptno = dept.deptno
--student.clazz_id = clazz.id
--多表连接的关联条件时N-1

--内连接（等值连接）
SELECT *
FROM scott.dept,scott.emp
WHERE scott.dept.deptno = scott.emp.deptno
AND scott.emp.empno = '7369';

SELECT *
FROM scott.dept,scott.emp
WHERE scott.dept.deptno = scott.emp.deptno;

--使用别名简化开发
SELECT *
FROM scott.dept d,scott.emp e
WHERE d.deptno = e.deptno
AND e.empno = '7369';

SELECT *
FROM scott.dept d,scott.emp e
WHERE d.deptno = e.deptno;

--筛选查询的列
--查询e表所有列 和 d表dname列，d表loc列
SELECT e.*, d.dname,d.loc
FROM scott.emp e,scott.dept d
WHERE d.deptno = e.deptno;

--内连接（等值连接） 标准sql的写法
SELECT *
FROM scott.dept d INNER JOIN scott.emp e
ON d.deptno = e.deptno;

--相同的结果 from where语句
SELECT *
FROM scott.dept,scott.emp
WHERE scott.dept.deptno = scott.emp.deptno;

--外连接（非等值连接）
SELECT * FROM scott.dept;--4
SELECT * FROM scott.emp;--14

--查询所有部门和员工信息
SELECT *
FROM scott.dept d,scott.emp e
WHERE d.deptno = e.deptno;

--因为部门40没有员工，当使用等值连接时，部门40的信息没有显示
--左外连接：+号在右边，左边的数据要全部显示，如果右边没有和左边表数据匹配的，则补null
SELECT *
FROM scott.dept d,scott.emp e
WHERE d.deptno = e.deptno(+);

--右外连接：+号在左边，右边的数据要全部显示，如果左边没有和右边数据匹配的，则补null
SELECT *
FROM scott.dept d,scott.emp e
WHERE e.deptno(+) = d.deptno;

--注意：+号的外连接写法只有Oracle支持

--标准sql语句
--左外连接
SELECT *
FROM scott.dept d LEFT OUTER JOIN scott.emp e
ON d.deptno = e.deptno;

--右外连接
SELECT *
FROM scott.emp e RIGHT OUTER JOIN scott.dept d
ON d.deptno = e.deptno;

SELECT *
FROM scott.dept d RIGHT OUTER JOIN scott.emp e
ON d.deptno = e.deptno;

--全外连接
--左边的表和右边的表数据都要全部显示，如果没有对应的，则补null
SELECT *
FROM scott.dept d FULL OUTER JOIN scott.emp e
ON d.deptno = e.deptno;

SELECT *
FROM scott.dept d LEFT OUTER JOIN scott.emp e
ON d.deptno = e.deptno;

--自连接（重要，面试经常出现）
/**
在emp中的每一个员工都有自己的mgr(经理)，并且每一个经理自身也是公司的员工，
经理自身也有自己的经理。下面我们需要将每一个员工自己的名字和经理的名字都找出来。
这时候我们该怎么做呢？
*/
SELECT * FROM scott.emp;
--empno ename mgr mgrname
--7369 SMITH 7902 FORD
--empno ename mgr mgrname
--7902 FORD 7566 JONES

--多表连接
--最快备份一张表数据的方法，注意，该方法只是备份数据，不复制约束
CREATE TABLE tb_emp AS SELECT * FROM scott.emp;
SELECT * FROM tb_emp;
SELECT * FROM scott.emp;

--e的员工编号 e的员工姓名 e的经理编号 t的员工姓名
SELECT e.empno,e.ename,e.mgr,t.ename
FROM scott.emp e,scott.tb_emp t
WHERE e.mgr = t.empno
AND e.empno = '7369';

--自连接
SELECT e.empno,e.ename,e.mgr,t.ename
FROM scott.emp e,scott.emp t
WHERE e.mgr = t.empno
AND e.empno = '7369';

/**
自连接(self join)是SQL语句中经常要用到的连接方式，
使用自连接可以将自身表的一个镜像当作另一个表来对待，
即将一张表看成多张表来做连接，从而能够得到一些特殊的数据。
关键之处在于为同一个表指定两个不同的别名。
这样就能通过在一张表内寻找到相互关联的数据再找到有继承关系的数据。
*/