select语句(5)--多表查询

第四章：多表查询
多表查询，又称表联合查询，即一条sql语句涉及到的表有多张，数据通过特定的连接进行联合显示.  
一、笛卡尔积
        在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y.
        假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
        在数据库中,如果直接查询俩张表,那么其查询结果就会产生笛卡尔积
        例如:
        select *
        from s_emp,s_dept;     
二、连接查询
        为了在多表查询中避免笛卡尔积的产生,我们可以使用连接查询来解决这个问题.
        连接查询分为:
            1.等值连接
            2.不等值连接
            3.外连接
                左外连接
                右外连接
                全连接
            4.自连接     
  1、等值连接
        利用一张表中某列的值和另一张表中某列的值相等的关系,把俩张表连接起来。
        例如:查询员工的名字、部门编号、部门名字       

 select last_name,dept_id,name from s_emp,s_dept where s_emp.dept_id=s_dept.id; 

         

        为了表述的更加清楚,可以给每张表起别名      

  select se.last_name,se.dept_id,sd.id,sd.name from s_emp se,s_dept sd  where se.dept_id=sd.id; 

   2、不等值连接

        假设数据库中还有一张工资等级表:salgrade
        工资等级表salgrade:
            gradeName列表示等级名称
            losal     列表示这个级别的最低工资数
            hisal    列表示这个级别的最高工资数 
        表中的数据类似于下面内容:
        id  salgrade    losal hisal
        1  初级程序员   2000  4000
        2  中级程序员   4000  6000         
        例如:
        查询出员工的名字、职位、工资、工资等级名称        

        SELECT  e.last_name, e.title, e.salray, s.gradeName        FROM    s_emp e, salgrade s        WHERE   e.salray BETWEEN s.losal AND s.hisal   

3、外连接
        外连接分为:左外连接 右外连接 全连接
        先分别在俩s_emp和s_dept表中插入新的数据
        特点:新员工tom不在任何部门,新增部门st下面没有任何员工
        insert into s_emp(id,last_name) values(26,'tom');
        insert into s_dept(id,name) values(60,'st');
        commit; 
        下面sql语句可以把上面插入的数据给删除掉
        delete from s_emp where id=26;
        delete from s_dept where id=60;
        commit;         
        这个时候再使用等值连接的话,查询出来的数据就会少,因为新增的员工tom和部门表中的数据连接不上,当然新增的部门st也和员工表中的数据连接不上.那么这俩条数据都是在等值连接中查询不出来.  
    3.1 左外连接
        例如:
        查询所有员工 以及对应的部门的名字,没有部门的员工也要显示出来      

        select last_name,dept_id,name          from s_emp,s_dept        where s_emp.dept_id=s_dept.id(+);          

        或者 俩者是等价的        

        select last_name,dept_id,name          from s_emp left outer join s_dept        on s_emp.dept_id=s_dept.id;         

        注意:outer可以省去不写      
    3.2右外连接
        例如:
        查询所有员工 以及对应的部门的名字,没有任何员工的部门也要显示出来        

select last_name,dept_id,name          from s_emp,s_dept        where s_emp.dept_id(+)=s_dept.id; 

       或者 俩者是等价的    

   select last_name,dept_id,name          from s_emp right outer join s_dept        on s_emp.dept_id=s_dept.id; 

        注意:outer可以省去不写      
    全连接
        例如:
        查询所有员工 以及对应的部门的名字,没有任何员工的部门也要显示出来,没有部门的员工也要显示出来       

 select last_name,dept_id,name ,s_dept.id        from s_emp full outer join s_dept          on s_emp.dept_id=s_dept.id;  

        注意:outer可以省去不写      
4、自连接
        一张表,自己和自己连接
        例如:
        查询每个员工的名字以及员工对应的管理者的名字        

select s1.last_name,s2.last_name manager_name        from s_emp s1,s_emp s2        where s1.manager_id = s2.id;   

三、对查询结果集的操作
        如果有俩条sql语句,每一条sql都可以查询出一个结果,这个被称之为结果集。那么我们可以使用下面的关键字对俩个结果集进行操作
        union        获得俩个结果集的并集  
        union all    把俩个结果集 合在一起显示出来    
        minus        第一个结果集除去第二个结果集和它相同的部分
        intersect    获得俩个结果集的交集          
    3.1 union  获得俩个结果集的并集  
        例如:        

select last_name,dept_id,name          from s_emp,s_dept        where s_emp.dept_id=s_dept.id(+)        union        select last_name,dept_id,name          from s_emp,s_dept        where s_emp.dept_id(+)=s_dept.id;  

    3.2 union all  把俩个结果集 合在一起显示出来    
        例如:        

select last_name,dept_id,name          from s_emp,s_dept        where s_emp.dept_id=s_dept.id(+)        union all        select last_name,dept_id,name          from s_emp,s_dept        where s_emp.dept_id(+)=s_dept.id; 

    3.3 minus  第一个结果集除去第二个结果集和它相同的部分
        例如:      

  select last_name,dept_id,name          from s_emp,s_dept        where s_emp.dept_id=s_dept.id(+)        minus        select last_name,dept_id,name          from s_emp,s_dept        where s_emp.dept_id(+)=s_dept.id; 

         对比俩种情况的结果       

  select last_name,dept_id,name          from s_emp,s_dept        where s_emp.dept_id(+)=s_dept.id        minus        select last_name,dept_id,name          from s_emp,s_dept        where s_emp.dept_id=s_dept.id(+);

从上面的对比能够看出结果的不同，前者是用s_emp表除去s_dept表，而后者是使用s_dept表除去s_emp表。

      3.4  intersect  求俩个结果集的交集      

 select last_name,dept_id,name          from s_emp,s_dept        where s_emp.dept_id=s_dept.id(+)        intersect        select last_name,dept_id,name          from s_emp,s_dept        where s_emp.dept_id(+)=s_dept.id; 

交集只显示具有相同值得部分，也就是不显示s_emp表中的tom,tom2（没有部门）和是——dept表中的st,st2（没有员工）。