数据库-SQL多表查询(SQL 92 与SQL 99详解)

一、概述：

1. 多表(连接)查询：

(1) 即查询的时候同时需要多张表（特别是存在外键关系的），此时需要多张表之间的值进行连接；

(2) 目前SQL标准提出过两种连接查询，第一种是较早的SQL92标准，第二种是目前使用广泛的较新的SQL99标准；

(3) 92形式简单，但语句上比较冗杂，外连接不太容易识别；99不仅在底层得到优化，而且形式看上去更加一目了然，逻辑性更强。

(4) 两种方式均可，其中SQL92书写方便阅读困难，SQL99书写较麻烦，但阅读很方便，而且符合逻辑（下文会体现），两种方式都有使用，所以都要学习。

(5) 更推荐SQL99。一般建议使用99标准；

2. SQL语句的历史版本：

SQL89（比较多）
SQL92（标准，又称sql2）
SQL99（标准，又称sql3）

3. 多表连接查询(类型)：

--当所需要查询的数据分布在多张表上时，需要使用多表查询（也称多表关联查询 /联合查询 /连接查询）：

　　--连接有三种基本类型：交叉连接，内连接，外连接。这三种连接的区别是它们采用的逻辑查询处理步骤各不相同：

　　　　--交叉连接：只有一个步骤 — 笛卡尔积；

　　　　--内连接：有两个步骤 — 笛卡尔积，过滤；

　　　　--外连接：有三个步骤 — 笛卡尔积，过滤，添加外部行。

4.多表关联查询包括

交叉连接：  
        --笛卡尔积
内连接：
        --等值关联查询
        --非等值关联查询
        --自然连接
        --自连接
外连接：
        --左外连接
        --右外连接
        --全外连接

5.概念说明：

(1)SQL92标准语法介绍：

　　1) 多张表需要全部放在from之后，

　　2) 多张表之间通过逗号分隔，

　　3) 所有的连接条件都放在where当中，

　　--结论：因此SQL92中的等值连接、非等值链接、外连接等等其实只是where中的条件不同罢了；

(2) SQL99标准语法介绍：

　　0) 通过一系列的关键字 (比如join，on等)，来定义表与表之间的连接类型与连接条件，写法更加符合逻辑，可读性更好，并且底层支持地更好；

　　1) 基本覆盖了全部类型的连接查询（包括外连接），并且各大数据库都能全部支持SQL99的连接查询；

　　2) 直接使用关键字：cross join（交叉连接，即广义笛卡尔积）、natrual join（自然连接）、left join/right join（左右外连接）表示连接类型；

　　3) 直接使用关键字：cross join（交叉连接，即广义笛卡尔积）、natrual join（自然连接）、left join/right join（左右外连接）表示连接类型；

　　注意：

依然可以给表添加别名
如果使用on或者using关键字对结果进行筛选必须使用inner join进行表与表的连接，其中inner可以省略
外连接的outer关键字可以省略不写
依然可以继续使用group by，having，order by等

(3)交叉连接，又称之为笛卡尔积 (即 SQL92的广义笛卡尔积)：

　　--概念：

　　1) 两个表做笛卡尔积，得到的结果集的行数是两个表的行数的乘积 (三表/多表同理)，效率最低。

　　2) 即将两张表的数据拼接在一起，总记录数为两张表记录量(行数)的乘积 ( 表*表 )；

　　--语法：

SELECT * FROM 表1 CROSS JOIN 表2; //SQL99标准

SELECT * FROM 表1，表2; //SQL92标准

select ...
from 表1
cross join 表2
[corss join 表3...]

　　--注1：如果有很多表要交叉连接，那么就继续cross join下去；

　　--注2：如果select选出的列刚好在各个表中有重名，则需要为表取别名以示区别，否则会显示出同名的两列，不易于区分；示例：

select s.col1, t.col2
from t1 as s
cross join t2 as t;

　　--注3：一般区别名还是建议使用as，如果一下用太多空格可读性会大大降低！并且as见名知意；

　　--注4：在实际开发中这种业务需求是很少见的，一般不会使用交叉连接，而是使用具体的条件对数据进行有目的的查询；

　　--注5：数学上，笛卡尔积也称直积，两个集合X和Y的笛卡尓积表示为X × Y，第一个对象是X的成员，而第二个对象是Y的所有可能有序对的其中一个成员。例如集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

　　--例如：department表中有4个部门，employee表中有4个员工，那么交叉连接的结果就有4 * 4 = 16条数据；

(4) 内连接：又称简单连接或自然连接.

　　--概念：返回查询结果集合中的仅是符合查询条件的数据行。

　　--说明：内连接使用比较运算符 (包括 =、>、<、<>、>=、<=、!> 和!< 等) 对两个表 (或多表) 中的数据进行比较，并列出(/查询出) 与连接条件匹配的数据行，组合成新的记录，也就是说在内连接查询中，只有满足条件的记录才能出现在查询结果中。根据所使用的比较方式不同，内连接分为等值连接、非等值连接、自然连接和自连接。

　　--说明：内连接，只查匹配行；内连接是从结果中删除其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。

　　--语法：

select * from 主表 inner join 附表 on 主表.字段=附表.字段 [where条件] //SQL99标准

　　--注：用 * 号表示查询两个表中的所有列

 select 内容 from 表名 inner join 表名 on 连接条件 where 普通筛选条件 //SQL99标准

　　--注：普通筛选条件使用where进行筛选，不要使用on进行。好处：SQL语句的阅读性变强。

　　--例如：已知表结构与测试数据如下

　　--查询studentInfo表和scoreInfo表中的所有列

select * from studentInfo inner join scoreInfo on studentInfo.stuID = scoreInfo.stuId //SQL99标准

　　--查询studentInfo表和scoreInfo表中的部分列

SELECT 
　　stu.stuName 姓名, 
　　stu.stuSex 性别, 
　　stu.stuAge 年龄, 
　　stu.className 班级, 
　　sco.score 成绩, 
　　sco.createTime 考试时间 
FROM 
　　studentInfo as stu
INNER JOIN 
　　scoreInfo as sco
ON 
　　stu.stuID = sco.stuId

其中as是可以省略的，但一般为了增强可读性，不会对其省略，或者：

SELECT 
　　studentInof.stuName 姓名, 
　　studentInfo.stuSex 性别, 
　　studentInfo.stuAge 年龄, 
　　studentInfo.className 班级, 
　　scoreInfo.score 成绩, 
　　scoreInfo.createTime 考试时间 
FROM 
　　studentInfo 
INNER JOIN 
　　scoreInfo 
ON 
　　studentInfo.stuID = scoreInfo.stuId

(5) 等值连接，也称等值关联查询：

　　--概念：先做表的笛卡尔积，然后筛选，筛选条件为等值筛选；

　　--例如：

 SELECT 查询字段 FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段 = 表2.关系字段; //SQL99标准

　　--注：在上述使用了SQL99标准，语法格式中，INNER JOIN用于连接两个表，ON来指定连接条件，其中INNER可以省略。

SELECT * FROM 表1 inner join 表2 on 表1.关系字段 = 表2.关系字段; //SQL99标准
SELECT * FROM 表1 join 表2 on 表1.关系字段 = 表2.关系字段; //SQL99标准

　　--注：SQL99标准，有时也被称为显示内连接：显示的就是在语句中明显的调用了inner join的关键字。

SELECT * FROM 表1,表2 where 表1.关系字段 = 表2.关系字段； //SQL92标准

　　--注：SQL92标准，有时也被称为隐式内连接：顾名思义隐式的就是我们看不到inner join的关键字。而使用where关键字替代。

(6) 不等值连接，非等值关联查询：

　　--概念：先做表的笛卡尔积，然后筛选，筛选条件为不等值连接；

　　--例如：

SELECT * FROM 表1 inner join 表2 on 表1.关系字段 = 表2.关系字段; //SQL99标准
SELECT * FROM 表1 join 表2 on 表1.关系字段 = 表2.关系字段; //SQL99标准
SELECT * FROM 表1,表2 where 表1.关系字段 = 表2.关系字段； //SQL92标准

　　--注1：SQL92标准，有时也被称为隐式内连接：顾名思义隐式的就是我们看不到inner join的关键字。而使用where关键字替代。

--注2：SQL99标准，有时也被称为显示内连接：显示的就是在语句中明显的调用了inner join的关键字。

(7) 自连接：

(8) 外连接：

　　1) 与内连接相比，即使没有匹配行，也会返回一个表的全集。

　　2) 内连接只返回满足连接条件的数据行，外连接除了能显示满足连接条件的数据以外, 还用于显示不满足连接条件的数据；

　　3) 重点：至少有一方保留全集，另一方没有匹配行用NULL代替。

　　4) 外连接分为左外连接、右外链接、全外连接三种。

　　5) 外连接不仅要把符合条件的连接输出，也要把不符合条件的连接也输出，但不是完整的输出！！！

(9) 左外连接：

(10) 右外连接：

(11) 全外连接：

二、SQL92与SQL99语法详解：

1. SQL92 语法

笛卡尔积

select * from emp e,dept d

等值连接 ( 在where后的条件为等值条件 , 即: “=” )

select * from emp t, dept d where t.deptno = d.deptno;

非等值连接 ( 在where后的条件为非等值条件 , 例如:> , < , >= , <= 等 )

select e.ename,s.grade from emp e, salgrade s where e.sal between s.losal and s.hisal;

自连接 ( 在where后的条件 , 用自身字段之间的关连作为条件 )

select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;

外连接 (在满足一张表的内容都显示的基础上，连接另外一张表，如果连接匹配则正常显示，连接不匹配，另外一张表补null)

select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno(+) ;

2. SQL 99 语法 :

语法形式 : select a.column,b.column from 表a 连接类型 JOIN 表b on 连接条件

交叉连接 (CROSS JOIN) : 可理解为92语法中的笛卡尔积

select * from emp cross join dept;

自然连接 (NATURAL JOIN):根据两张表中相同字段名称,进行等值连接

select * from emp natural join dept;

内连接 (INNER JOIN)(INNER关键字可以省略)
–(1)等值连接

select e.ename, d.dname
    from emp e
   INNER JOIN dept d
      on e.deptno = d.deptno
   where e.sal > 2000

–(2)非等值连接

  select e.ename, s.grade
    from emp e
   INNER JOIN salgrade s
      on e.sal between s.losal and s.hisal

–(3)自连接

    select e1.ename || '的上司是' || e2.ename
    from emp e1
   INNER JOIN emp e2
      on e1.mgr = e2.empno

外连接 (OUTER关键字可以省略)
–(1)LEFR OUTER JOIN(左外连接)

select * from emp e LEFT OUTER JOIN dept d on e.deptno = d.deptno

–(2)RIGHT OUTER JOIN(右外连接)

select * from emp e RIGHT OUTER JOIN dept d on e.deptno = d.deptno

–(3)FULL OUTER JOIN(全外连接)

select * from emp e FULL OUTER JOIN dept d on e.deptno = d.deptno

接下来详细讲解各种连接

交叉连接

交叉联接是一种最简单的联接，交叉联接只实现一个笛卡尔积即可。这一步就是对两张表进行操作，把它们联接起来，生成两者的笛卡尔积。也就是将一个输入表的每行和另一张表的每行进行匹配。如果一张表有n行，另一张表有m行，将得到m * n行。

ANSI SQL-99：Cross Join

ANSI SQL-92：在两张表间放置一个逗号

内连接（ANSI SQL-99标准提出来的概念）

–内连接（整合了ANSI SQL-92的等值连接，不等值连接，自连接）

   (1)
        --等值连接
          --不等值连接
   (2)
          --非自连接
           --自连接

内连接要应用两个逻辑查询处理步骤：它首先像交叉联接一样，对两个输入表进行笛卡尔积运算：然后根据用户指定的谓词对结果进行过滤。和交叉联接一样，内联接也有两种标准语法。

ANSI SQL-99：Inner Join 或者 Join（由于内连接是默认的联接方式，所以也可以只写Join）

ANSI SQL-92：在两张表间放置一个逗号，在查询的where子句中写过滤条件。

强烈推荐使用Inner Join，因为假如你忘记写过滤条件(on)，执行时SQL会报错。而如果使用SQL-92语法，则不会报错。那么这个内连接就变成交叉连接了! 而且实际开发中SQL语句会非常的复杂，写错的概率就很大了。

补充一下：

1.组合连接

有时候过滤条件不止一个，这时候就要用and来连接多个条件，这就叫做组合连接。例如：Table2表中定义了一个外键（col1和col2），引用了Table1表的col1和col2列，现在要写一个根据主外键关系来连接两个表的查询语句。该条件如下：

from table1 as t1
join table2 as t2
on t1.col1 = t2.col1 and t1.col2 = t2.col2

2.多表连接

一个联接表运算符只对两个表进行操作，而一条查询语句可以包含多个联接。通常，当FROM子句中包含多个表运算符时，表运算符在逻辑上是按从左到右的顺序处理的。也就是说，第一个表运算符的结果将作为第二个表运算符的输入，第二个表运算符的结果将作为第三个运算符左边的输入，以此类推。

外连接

外连接会应用内连接所应用的两个逻辑处理步骤（笛卡尔积和On过滤），此外还多一个外连接特有的第三步：添加外部行。

ANSI SQL-92：在原有等值连接的基础上加上左边或者右边加上（+），实现外连接查询

ANSI SQL-99：

INNER JOIN(也可简写为JOIN): 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行

解释：在外连接中，要把一个表标记为“保留的”表，可以在表名之间使用关键字LEFT OUT JOIN、RIGHT OUTER JOIN ,以及 FULL OUTER JOIN,其中 OUTER 失键字是可选的。 LEFT关键字表示左边表的行是保留的关键字表示右边表的行是保留的，而FULL 关键字则表示左右两边表的行都是保留的。外联接的第三个逻辑杏询处理步骤就是要识别保留表中按照ON条件在另一个表找不到与之匹配的那些行，再把这些行添加到联接的前两个步骤生成的结果表中：对于来自联接的非保留表的那些列，追加的外部行中的这些列则用NULL作为占位符。

补充一下：自然连接:

自然连接是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果。

自然连接无需指定连接列，sql会检查两个表中是否相同名称的列，且假设他们在连接条件中使用，并且在连接条件中仅包含一个连接列。不允许使用on语句，不允许指定显示列，显示列只能用*表示（oracle环境下测试的）。对于每种连接类型（除了交叉连接外），均可指定natural。

最后用下面例子来总结一下知识点：

---多表联合查询
  ---当需要获取的数据分布在多张表中使用多表联合查询
  ---SQL92方式
      ---笛卡尔积:将多个表的数据进行一一对应,所得到结果为多表的笛卡尔积.
                  ---结果数量为所有表数量的乘积
                  seelct * from emp,dept;
      ---等值链接
             ----先做表的笛卡尔积,然后筛选,筛选条件为等值筛选.
               ---查询员工的姓名,工作,薪资,部门名称
               select * from emp,dept where dept.deptno=emp.deptno;---等值连接查询
                      ---可以直接在select子句中使用字段直接获取数据,但是效率低,建议字段前加上表名
                      ---注意:如果是公共字段必须声明表名
                       select * from emp e,dept d where d.deptno=e.deptno;
      ---不等值链接
             ----查询员工的姓名,工作,工资,工资等级
             select * from emp e,salgrade s where e.sal>=s.losal and e.sal<= s.hisal; 
      ---自连接
             ----相当于自己和自己做笛卡尔积
             ----查询员工姓名,工作,薪资,以及上级领导姓名
            select e1.ename,e1.job,e1.sal,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;
      ---外链接
            ----左外链接
                   ---查询员工姓名,工作,薪资,部门名称以及没有部门的员工信息
                   select * from emp e,dept d where e.deptno=d.deptno(+); 
            ----右外链接
                   ---查询员工姓名,工作,薪资,部门名称以及没有员工的部门
                   select * from emp e,dept d where e.deptno(+)=d.deptno;

----SQL99多表查询
----注意1:依然可以给表添加别名
----注意2;如果使用on或则usering关键字必须对结果进行筛选,必须使用inner join关键字组员于表与表的连接,其中inner可以省去
----注意3:外连接的outer关键字可以不写
----注意4:依然可以使用排序等关键字
             
     ----1.笛卡尔积:使用cross join关键字
           -----select 内容 from 表名 cross join
           -----查询员工及其部门
           select * from emp cross join dept;
     ----2.筛选
           -----自然连接:使用关键字natrual join
                  ----使用:select 内容 from 表名 natural join 表名
                  select* from emp natural join dept;
                  ---特点1:底层是笛卡尔积,按照同名同值字段自动进行等值筛选     
                  ---缺点1:如果想按照字段名不同,但是字段值不同筛选怎么办?
                  ----缺点2:如果只想按照部分字段结果筛选怎么办?    
                  ----解决1:使用using关键字
                          --作用1:指明使用指定的字段对联合查询的结果进行等值筛选
                          --注意:指明的字段必须是两边同名同值字段
                          --使用:select 内容 from 表名 inner join 表名 using(字段名)
                  select * from emp inner join dept using(deptno,ename);--底层相当于先做了一个笛卡尔积,再进行等值筛选
                  ------解决2:使用on关键字,进行自定义链接查询
                         ----注意:普通筛选使用where筛选不使用on  好处:便于阅读
                         ----使用:select 内容 from 表名 inner join 表名 on 连接条件 where 普通筛选条件
                         select * from emp inner join dept on emp.deptno=dept.deptno where job='SALESMAN';
          ------内连接
               ---1.等值链接
                     ----先做表的笛卡尔积,然后筛选,筛选条件为等值筛选.
                     ---查询员工的姓名,工作,薪资,部门名称
                        select * from emp inner join dept on dept.deptno=emp.deptno;---等值连接查询
                      ---可以直接在select子句中使用字段直接获取数据,但是效率低,建议字段前加上表名
                      ---注意:如果是公共字段必须声明表名
                        select * from emp e inner join dept d on d.deptno=e.deptno;
               ---2.不等值链接
                     ----查询员工的姓名,工作,工资,工资等级
                        select * from emp e,salgrade s where e.sal>=s.losal and e.sal<= s.hisal; 
               ---3.自连接
                       -----查询员工姓名,工作,薪资,以及上级领导姓名
                        select e1.*,e2.ename from emp e1 inner join emp e2 on e1.mgr=e2.empno;
           ------外连接
                    ----左外连接:select 内容 from 表名 left outer join 表名 on 筛选条件
                        ---查询员工姓名,工作,薪资,部门名称以及没有部门的员工信息
                        select * from emp e left outer join dept d on d.deptno=e.deptno;
                    ----右外连接:select 内容 from 表名 right outer join 表名 on 筛选条件
                         ---查询员工姓名,工作,薪资,部门名称以及没有员工的部门
                         select * from emp e right outer join dept d on d.deptno=e.deptno;
                    ----全外连接:select 内容 from 表名 fill outer join 表名 on 筛选条件
                         select * from emp e full outer join dept d on d.deptno=e.deptno;

参考文章：

1. https://blog.csdn.net/weixin_43465312/article/details/103094430

2.https://blog.csdn.net/weixin_39722922/article/details/85113692

3. https://blog.csdn.net/weixin_44771312/article/details/98991622

4. https://blog.csdn.net/iteye_5198/article/details/82649372 (Oracle使用Scott用户登录)

5. https://blog.csdn.net/Lirx_Tech/article/details/51111662

6. https://www.toutiao.com/a6761205751114367495?wid=1618136606974

posted @ 2021-03-16 14:04 HarryVan 阅读(3639) 评论(0) 编辑收藏举报

刷新页面返回顶部

HarryVan