Oracle多表查询

多表连接查询

SQL查询的基本原理
第一、单表查询：
根据WHERE条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据SELECT的选择列选择相应的列进行返回最终结果。

第二、两表连接查询：
对两表求积（笛卡尔积）并用ON条件和连接连接类型进行过滤形成中间表；然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。

第三、多表连接查询：
先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。
理解SQL查询的过程是进行SQL优化的理论依据。
ON后面的条件（ON条件）和WHERE条件的区别
ON条件：

是过滤两个链接表笛卡尔积形成中间表的约束条件。

WHERE条件：

在有ON条件的SELECT语句中是过滤中间表的约束条件。
在没有ON的单表查询中，是限制物理表或者中间查询结果返回记录的约束。
在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。
从这里可以看出，将WHERE条件移入ON后面是不恰当的。
推荐的做法是：ON只进行连接操作，WHERE只过滤中间表的记录。

连接查询

连接查询分两大类，一是使用连接谓词进行连接；二是使用关键字JION进行连接。
连接谓词
使用连接谓词连接表的基本格式为：

SELECT  <输出字段列表>
FROM  表1，表2 [，...n]
WHERE  <表1.字段名1>  <连接谓词>  <表2.字段名2>

连接字段：必须是可比较的
连接谓词包括：=、<、<=、>、>=、!=、<>等，当比较符是=时称为等值连接。
以JOIN关键字连接
以JOIN关键字来连接表的方式，增强了表的连接能力和连接的灵活性。
使用JOIN关键字连接表的的基本格式为：

SELECT  <输出字段列表>
FROM  表名1 <连接类型> 表名2  ON <连接条件>
             [ <连接类型> 表名3  ON <连接条件>]......

参数说明：

• 表名1，表名2，表名3等用来指明需要连接的表。

• 连接类型有：[ INNER | { LEFT | RIGHE | FULL } OUTER ] JOIN。其中INNER JOIN表示内连接；OUTER JOIN表示外连接，外连接又分：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）。

• ON：用来指明连接条件。
  内连接 INNER JOIN
  分类：

    等值连接
    不等值连接
    自然连接
    自连接
  

• 等值连接和不等值连接

    用连接谓词进行连接分两种情况，当连接谓词是“=”时的连接，称为等值连接。
    当连接谓词是不等运算符时的连接，称为不等连接。
  

--查询所有有部门员工信息，部门名称,使用等值连接方式
select e.*, d.department_name
  from dept d, emp e
 where e.department_id = d.department_id;

• 自然连接

    （1）自然连接(Natural join)是一种特殊的等值连接，
    它要求两个关系中进行比较的分量必须是相同的属性组，
    并且在结果中把重复的属性列去掉。
    （2）在针对多表进行查询时。如果所引用的列为被查询的表所共有，
    则引用列时必须用表名来指定列是来源于哪个表，
    指定方法为：表名.列名。反之，不一定需要表名来指定来源。
  

等值连接：
SELECT  *  FROM  student a, score b
WHERE  a.student_id = b.student_id
自然连接：
select * from tab_student a natural join tab_score b;

不常用，因为不安全，如果不小心向表中插入了一条记录刚好跟连接的表相同就会出现问题

• 自连接
  如果在一个连接查询中，设计到的两个表都是同一个表，这种查询称为自连接查询。
  （1）一个表与自身进行连接称为自连接。
  （2）在同一个表中查找具有相同字段值的行，则可以使用自连接。
  （3）在使用自连接时需要为表指定两个别名，且对所有引用的列均要用别名来指定其来源。
  简单来说就是将一张表当做两张来用，自己连接自己

--查询员工姓名以及直接领导姓名(自连接)
select a.first_name || a.last_name 员工姓名,
       b.first_name || b.last_name 领导姓名
  from emp a, emp b
 where a.manager_id = b.employee_id;

外链接OUTER JOIN
内连接只返回满足连接条件的数据行，外连接不只列出与连接条件相匹配的行，而是列出左表（左外连接时）、右表（右外连接时）或两个表（全外连接时）中所有符合搜索条件的数据行。
分类：

	左外连接（left outer join）
	右外连接（right outer join）
	全外连接（full outer join）

• 左外连接（left outer join）
  （1）LEFT JOIN或LEFT OUTER JOIN

  左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，
  而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，
  则在相关联的结果集行中右表的所有选择列表列均为空值。 
  

左外连接的连接格式为：

FROM 左表名 LEFT  [OUTER]  JOIN  右表名 ON 连接条件

--使用左外关联，outer可以省略
select  e.*, d.department_name
from emp e left outer join dept d
on e.department_id = d.department_id;

（2）RIGHT JOIN 或 RIGHT OUTER JOIN

	右向外联接是左向外联接的反向联接。
	将返回右表的所有行。
	如果右表的某行在左表中没有匹配行，则将为左表返回空值。 

右外连接的连接格式为：

FROM 左表名 RIGHT  [OUTER]  JOIN  右表名 ON 连接条件

--使用右外关联查询，outer可以省略
select  e.*, d.department_name
from emp e right outer join dept d
on e.department_id = d.department_id;

（3）FULL JOIN 或 FULL OUTER JOIN

	完整外部联接返回左表和右表中的所有行。
	当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。
	如果表之间有匹配行，则整个结果集行包含基表的数据值。

全外连接的格式为：

FROM 左表名 FULL [OUTER] JOIN 右表名 ON 连接条件

--使用全外关联查询，outer可以省略
select  e.*, d.department_name
from emp e full outer join dept d
on e.department_id = d.department_id
where e.employee_id=100;

oracle外链接的简写方式：

--oracle外链接的简写方式
select  e.*, d.department_name
from emp e ,dept d
where e.department_id = d.department_id(+);--左外关联，在两张表关联条件中添加（+）号，
（+）号另一边的表记录全部查询出来

左外关联，在两张表关联条件中添加（+）号，
（+）号另一边的表记录全部查询出来
交叉连接

	 交叉连接也叫非限制连接，它将两个表不加任何约束地组合起来。
	 在数学上，就是两个表的笛卡尔积。
	 交叉连接后得到的结果集的行数是两个被连接表的行数的乘积。

语句1：隐式的交叉连接，没有CROSS JOIN。
SELECT O.ID, O.ORDER_NUMBER, C.ID, C.NAME
FROM ORDERS O , CUSTOMERS C
WHERE O.ID=1;

语句2：显式的交叉连接，使用CROSS JOIN。
SELECT O.ID,O.ORDER_NUMBER,C.ID,
C.NAME
FROM ORDERS O CROSS JOIN CUSTOMERS C
WHERE O.ID=1;
语句1和语句2的结果是相同的

嵌套子查询
1、子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句中的WHERE 或 HAVING 子句内，或嵌套在其他子查询中的SELECT查询。
2、子查询可以嵌套，它能将比较复杂的查询分解为几个简单的查询。一个SELECT—FROM—WHERE语句称为一个查询块。将一个查询块嵌套在另一个WHERE子句或HAVING短语的条件中的查询称为嵌套查询。
语法：

           SELECT   class_name
外部查询    FROM     class
           WHERE   class_id    
           IN(  
内部查询（子查询） SELECT   class_id
          		FROM   student
          		WHERE  student_name=‘张小云’ ）

嵌套查询的执行顺序是：

	首先执行最底层的内部查询（即子查询），
	它的查询结果并不显示，而是传递给外层查询，用来做外外部查询的查询条件。
	即按照由里向外的顺序执行。

子查询的使用 ：

	在使用 IN 或NOT IN的集合查询中。
	●在UPDATE、DELETE和INSERT语句中。
	●在使用比较运算符时
	●使用 ANY 或 ALL时。
	●使用 EXISTS或NOT EXISTS 引入的存在测试时。
	●在有表达式的地方。

--子查询插入数据
 insert into emp2(id,name,sal)
 select e.employee_id,concat(e.last_name,e.first_name),e.salary
 from emp e
 where e.department_id=90;

• 带谓词IN或NOT IN的子查询

    IN子查询是把子查询的结果做为外部查询的条件。
    判断外部查询中的某个值是否属于子查询的结果集合。
  

格式：<表达式> [NOT] IN （子查询）

SELECT  student_id, student_name, class_id
FROM   student
WHERE  student_id   IN
     (SELECT  student_id  
      FROM    score
      WHERE   course_id=‘1002’)

• 带有比较运算符的子查询

•   当子查询返回的是单值时，子查询可以由一个比较运算符（ =、<、<=、>、>=、!=或<>）引入。
    当子查询可能返回多个值时则应把比较运算符与ALL和ANY 结合使用。
  

格式为：表达式 { 比较运算符 } { ALL|ANY } (子查询)
ALL（>all 大于全部））：

 表示表达式要与子查询的结果集中的所有值进行比较，
 当表达式与每个值都满足比较关系时，才返回TRUE，否则返回FALSE。

ANY（>any 大于其中一个）：

 表示表达式只要与子查询的结果集中的某个值满足比较关系时，
 就返回TRUE，否则返回FALSE。