Oracle连接- -

Oracle连接- -

                                      

      通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型 数据库管理系统的一个标志。
　　在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在 一个表中。当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带 来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，尔后通过连接进行查询。

　　 SQL-92标准所定义的FROM子句的连接语法格式为：
　　　　 FROM join_table join_type join_table
　　　　　　[ON (join_condition)]
　　其中join_table指出参与连接操作的表名，连接可以对同一个表操作，也可以对多表操作，对同一 个表操作的连接又称做自连接。
　　join_type 指出连接类型，可分为三种：内连接、外连接和交叉连接。内连接(INNER JOIN)使用比 较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用 的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。
　　 外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN) 和全外连接(FULL OUTER JOIN或FULL JOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹 配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。
　　 交叉连接(CROSS JOIN)没有WHERE 子句，它返回连接表中所有数据行的笛卡尔积，其结果集合中的 数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。
　　 连接操作中的ON (join_condition) 子句指出连接条件，它由被连接表中的列和比较运算符、逻辑 运算符等构成。
　　 无论哪种连接都不能对text、ntext和image数据类型列进行直接连接，但可以对这三种列进行间接 连接。例如：
　　　　SELECT p1.pub_id,p2.pub_id,p1.pr_info
　　　　　　 FROM pub_info AS p1 INNER JOIN pub_info AS p2
　　　　　　 ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info)
　　(一)　内连接 内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分 三种：
1、等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接 表中的所有列，包括其中的重复列。
2、不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。
这些 运算符包括>、>=、<=、<、!>、!<和<>。
3、自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询 结果集合中所包括的列，并删除连接表中的重复列。
　　 例，下面使用等值连接列出authors和publishers表中位于同一城市的作者和出版社：
　　SELECT * FROM authors AS a INNER JOIN publishers AS p
　　　　 ON a.city=p.city
又如使用自然连接，在选择列表中删除authors 和publishers 表中重复列(city和state)：
　　SELECT a.*,p.pub_id,p.pub_name,p.country
　　　　FROM authors AS a INNER JOIN publishers AS p
　　　　ON a.city=p.city
(二)　外连接 内连接时，返回查询结果集合中的仅是符合查询条件( WHERE 搜索条件或 HAVING 条件)和连接条件 的行。而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外 连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。
如下面使用左外连接将论坛内容和作者信息连接起来：
　　 SELECT a.*,b.* FROM luntan LEFT JOIN usertable as b
　　　　ON a.username=b.username
下面使用全外连接将city表中的所有作者以及user表中的所有作者，以及他们所在的城市：
　　 SELECT a.*,b.* FROM city as a FULL OUTER JOIN user as b
　　　　ON a.username=b.username
传统的连接语法存在一些问题。例如，给定一个复杂的查询，解析其WHERE子句以从对查询结果的其它限制条件中分隔出连接条件通常是很困难的。此外，程序员有时会漏了指定任何连接条件，这将导致一个笛卡儿乘积。

ON子句允许你将任意的布尔表达式指定为一个连接条件。然而，大多数的连接都是相等连接。所谓相等连接，意即在此连接中比较两张表的相关列的相等性。因此，如果定义连接的列在两张表中的命名完全相同，那么你就可以利用简化的语法编写出高清晰度的查询。除了用ON 子句指定某个布尔连接条件外，你还可以用USING子句取代ON来指定连接列。例如，你可以将如下语句：

ON c.course_name = e.course_name
AND c.period = e.period;

代之以：
USING (course_name, period);

上例中的USING 子句指定当两表各自的COURSE_NAME和PERIOD列的值对应相等时，连接这两个表的行。程序清单3 给出了一个使用USING子句执行相等连接的例子。不难发现，程序清单3中的SELECT 语句非常简洁易懂。但是，USING子句对整个查询的语义会有微秒的影响。当编写带有ON子句的连接时，两张表的所有列都是可用的。因而，你可以把这两张表的COURSE_NAME 列都选择出来：

SELECT C.COURSE_NAME, E.COURSE_NAME

如果在USING子句中像上例那样使用列别名，你将收到一个"无效列名"的错误信息。当指定了USING子句，数据库引擎将合并这两个COURSE_NAME 列并且在结果中只识别一个这样的列。此列与任何一个被连接的表都没有关联，所以你不能用别名来限定它。这一规定是合理的，因为，按照定义，一个相等连接意味着对查询返回的每一行只存在唯一的COURSE_NAME 值。使用USING子句可以方便简明地表达出相等联；在我看来，由此生成的查询要比使用ON的查询更容易让人理解。

向左和向右外连接事实上做的是相同的事-- 一张表的所有行将全被包含，再加上另一张表中所有相匹配的行。两者中唯一的区别在于它们列出表的顺序不同。以下示例的三个查询在语义上完全等价，其中第一个使用的是老的语法：
SELECT c.course_name, c.period,
e.student_name
FROM course c, enrollment e
WHERE c.course_name = e.course_name(+)
AND c.period = e.period(+);

SELECT c.course_name, c.period,
e.student_name
FROM course c LEFT OUTER JOIN enrollment e
ON c.course_name = e.course_name
AND c.period = e.period;

SELECT c.course_name, c.period,
e.student_name
FROM enrollment e RIGHT OUTER JOIN course c
ON c.course_name = e.course_name
AND c.period = e.period;

完全外连接则代表了一种全新的功能，它把来自两张表的所有行都返回。行在连接列上进行可能的匹配，对于在另一张表中没有匹配值的任一行都用NULL填充空列。程序清单5 给出了两个示例，其一是Oracle9i出现以前模拟的完全外连接，另一个则是Oracle9i的完全外连接。

在程序清单5 的输出结果中，COURSE 表中课程名为Spanish I 和 U.S. History的行在表ENROLLMENT中没有相对应的行。 因此，这些行的STUDENT_NAME列为NULL.。课程名为Sky Lynn's German I 的行在COURSE 表中没有对应行。但是，请注意，此行的COURSE_NAME 和PERIOD 列却不是NULL。这些列是由USING子句指定的连接列，. 因而，当COURSE表中没有可用的行时，Oracle9i 将表ENROLLMENT 中这些列的值赋给了COURSE表中对应行的对应列。对于相等连接的情况，这样做是有意义的。 如果你倾向于将列视作NULL，可以使用ON子句来指定连接条件，如程序清单6 所示。

由于程序清单6 使用了ON子句， 因此取值Sky Lynn 的行的COURSE_NAME 和 PERIOD 列均为NULL。

多条件连接
你可以在查询中指定多个连接条件以连接两张以上的表。默认情况下， Oracle9i 从左至右处理连接。但是，可以使用圆括号控制连接的顺序。以下两个查询是等价的：
SELECT course_name, period, student_name,
s.grade_level
FROM course c INNER JOIN enrollment e
USING (course_name, period)
INNER JOIN student s USING (student_name);

SELECT course_name, period, student_name,
s.grade_level
FROM (course c INNER JOIN enrollment e
USING (course_name, period))
INNER JOIN student s USING (student_name);
第一个查询将 COURSE 连接到 ENROLLMENT 并将结果连接到STUDENT。第二个查询使用圆括号指定了完全相同的连接次序。

(三)　交叉连接 交叉连接不带WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。
例，titles表中有6类图书，而publishers表中有8家出版社，则下列交叉连接检索到的记录数将等 于6*8=48行。
SELECT type,pub_name FROM titles CROSS JOIN publishers
ORDER BY type