数据库Oracle之多表查询：笛卡尔积、等值连接、不等值连接、外连接，自连接



1、笛卡尔积

 列数相加
 行数记录相乘
 
   SQL> ed
  已写入 fileafiedt.buf
  
    1  select count(*)
    2* from dept, emp
  SQL> /
  
    COUNT(*)
  ----------
          56
    
通过连接条件可以避免笛卡尔积       
    1  select *
    2  from dept d, emp e
    3* where e.deptno = d.deptno
  SQL> set linesize 160
  SQL> /

2、等值连接

 eg:查询员工信息,员工号,姓名,月薪,部门名称
 
 select e.empno, e.ename, e.sal, d.dname
  from emp e, dept d
  where e.deptno = d.deptno
  /        
3、不等值连接:  

 eg:查询员工信息,员工号,姓名,月薪,薪水级别 
 
  
  select  e.empno, e.ename, e.sal, s.grade
  from emp e, SALGRADE s
  where e.sal >= s.losal and e.sal <= s.hisal 
  -- where e.sal between s.losal and s.hisal 
       EMPNOENAME            SAL      GRADE
  ---------- ---------- ---------- ----------
        7369 SMITH            800          1
        7900JAMES            950          1
        7876ADAMS           1100          1
        7521WARD            1250          2
        7654MARTIN          1250          2
        7934MILLER          1300          2
        7844TURNER          1500          3
        7499ALLEN           1600          3
        7782CLARK           2450          4
        7698BLAKE           2850          4
        7566JONES           2975          4
        7788 SCOTT           3000          4
        7902FORD            3000          4
        7839KING            5000          5
  
  已选择14行。
  
4、外连接

 eg:按部门统计员工人数:部门号部门名称各部门人数
 
 -- 1 确定是多表查询,因为部门表中没有人数,人数和员工表有关
 -- 2 因为按照各个部门人数 ,所以用到分组查询
 
 select d.deptno, d.dname, count(*)
 from dept d, emp e
 where d.deptno = e.deptno
 group by d.deptno, d.dname
 
 
第一阶段 
select d.deptno, d.dname, count(*)
 from dept d, emp e
 where d.deptno = e.deptno(+)
 group by d.deptno, d.dname
/

SQL>/

   DEPTNO DNAME           COUNT(*)
---------- -------------- ----------
        10ACCOUNTING             3
        20RESEARCH               5
        30SALES                  6
       
 ==分析40号部门为什么没有被统计对.....
 ===我们希望把不符合条件的部门(40部门)也要显示出来..
 ===希望把不满足等值条件(whered.deptno = e.deptno)的数据,也显示出来
 
 ======>外连接
 左连接:想把 d.deptno都显示出来,,把(+)写在等号的右边,叫左连接
  右连接:想把 d.deptno都显示出来,,把(+)写在等号的左边,叫右连接
 

   1  select d.deptno, d.dname, count(e.empno)
    2     from dept d, emp e
    3     where d.deptno = e.deptno(+)
    4*    group by d.deptno, d.dname
  SQL> /
  
      DEPTNODNAME          COUNT(E.EMPNO)
  ---------- -------------- --------------
          10ACCOUNTING                 3
          40OPERATIONS                 0
          20RESEARCH                   5
          30 SALES                      6
  
  SQL> ed
 
5、自连接:

--查询员工信息 ,老板信息
 显示:  ****的老板是**** 
 
 --- 员工的 .老板是 ....
 --- 员工表老板表
 -- 等值连接条件
   员工表的老板=老板表的员工
   
   select e.ename || '的老板是'||b.ename
   from emp e, emp b
   where e.mgr = b.empno
   
   select e.ename || '的老板是'||b.ename
   from emp e, emp b
   where e.mgr = b.empno(+)
   
  
  
  
  ----大老板没有出来....通过左连接 ....
     1  select e.ename || '的老板是'|| b.ename
 2                    from emp e, emp b
 3*                   where e.mgr = b.empno(+)
SQL> /
  
  
  
  E.ENAME||'的老板是'||B.ENAME
  ----------------------------
  SMITH的老板是FORD
  ALLEN的老板是BLAKE
  WARD的老板是BLAKE
  JONES的老板是KING
  MARTIN的老板是BLAKE
  BLAKE的老板是KING
  CLARK的老板是KING
  SCOTT的老板是JONES
  KING的老板是
  TURNER的老板是BLAKE
  ADAMS的老板是SCOTT
  JAMES的老板是BLAKE
  FORD的老板是JONES
  MILLER的老板是CLARK
  
  已选择14行。
 
 
 ====== 显示优化  
SQL> ed
已写入 file afiedt.buf

 1  select e.ename || '的老板是'||nvl(b.ename, '他自己')
 2                    from emp e, emp b
 3*                   where e.mgr = b.empno(+)
SQL> /

 E.ENAME||'的老板是'||NVL(B.E
 ----------------------------
 SMITH的老板是FORD
 ALLEN的老板是BLAKE
 WARD的老板是BLAKE
 JONES的老板是KING
 MARTIN的老板是BLAKE
 BLAKE的老板是KING
 CLARK的老板是KING
 SCOTT的老板是JONES
 KING的老板是他自己
 TURNER的老板是BLAKE
 ADAMS的老板是SCOTT
 JAMES的老板是BLAKE
 FORD的老板是JONES
 MILLER的老板是CLARK
 
 已选择14行。