Java开发网 - [英][转帖] Oracle9i 新的SQL连接语法

Java开发网

您没有登录

» Java开发网 » Database/JDBC/SQL/JDO/Hibernate » Oracle

打印话题 寄给朋友 订阅主题

作者

[英][转帖] Oracle9i 新的SQL连接语法 [精华]

发贴: 0
积分: 0

于 2002-08-17 01:04

Oracle has made some important enhancements to Oracle9i SQL, including a host of exciting new execution plans, support for scalar subqueries, and support for the ISO 99 SQL standard. As implemented by Oracle, this includes the following new TABLE JOIN syntax:

CROSS JOIN—This creates a Cartesian product of the rows in both tables, just like in Oracle8i when the WHERE clause is forgotten.
NATURAL JOIN—This is a useful Oracle9i syntax feature that improves the readability of SQL by removing join criteria from the WHERE clause.
The USING clause—This allows you to specify the join key by name.
The ON clause—This syntax allows you to specify the column names for join keys in both tables.
LEFT OUTER JOIN—This returns all the rows from the table on the left side of the join, along with the values from the right-hand side, or nulls if a matching row doesn't exist.
RIGHT OUTER JOIN—This returns all the rows from the table on the right side of the join, along with the values from the left-hand side, or nulls if a matching row doesn't exist.
FULL OUTER JOIN—This returns all rows from both tables, filling in any blanks with nulls. There is no equivalent for this in Oracle8i.

Most of these enhancements were introduced to allow non-Oracle applications to quickly port onto an Oracle database, and it's important to remember that these are just syntax differences. The ISO 99 standard doesn’t bring any new functionality to Oracle9i SQL.

The CROSS JOIN
In Oracle, the CROSS JOIN syntax produces a Cartesian product, very much the same as forgetting to add a WHERE clause when joining two tables:
select last_name, dept_id
from emp, depts;

In Oracle9i, we can use the CROSS JOIN syntax to achieve the same result:
select last_name, dept_id
from emp
CROSS JOIN dept;

The NATURAL JOIN
I like the NATURAL JOIN syntax because it automatically detects the join keys, based on the name of the matching column in both tables. This simplifies Oracle9i SQL because the WHERE clause will only contain filtering predicates. Of course, the use of NATURAL JOIN requires that both columns have identical names in each table. It‘s interesting to note that this feature works even without primary or foreign key referential integrity.

Oracle8i
Select book_title, sum(quantity)
From book, sales
Where book.book_id = sales.book_id
group by book_title;

Oracle9i
Select book_title, sum(quantity)
from book
natural join sales
group by book_title;

The USING clause
The USING clause is used if several columns share the same name but you don’t want to join using all of these common columns. The columns listed in the USING clause can’t have any qualifiers in the statement, including the WHERE clause:

Oracle8i
select dept_id, city
from departments, locations
where departments.location_id = location.location_id;

Oracle9i
select department_name, city
from departments
JOIN locations
USING (location_id);

The ON clause
The ON clause is used to join tables where the column names don’t match in both tables. The join conditions are removed from the filter conditions in the WHERE clause:

Oracle8i
select department_name, city
from department, location
where department.location_id = location.loc_id;

Oracle9i
select department_name, city
from department d
JOIN location l
ON (d.location_id = l.id);

Mutable joins
Mutable joins are those where more than two tables are joined. The ISO SQL 1999 standard always assumes the tables are joined from the left to the right, with the join conditions only being able to reference columns relating to the current join and any previous joins to the left:

Oracle8i
select emp_id, city_name, dept_name
from location l, department d, emp e
where d.location_id = l.location_id
and d.department_id = e.department_id;

Oracle9i
select emp_id, city_name, dept_name
from locations l
JOIN departments d ON (d.location_id = l.location_id)
JOIN employees e ON (d.department_id = e.department_id);

New OUTER JOIN syntax
The ISO 99 standard removes the onerous plus sign (+) from Oracle outer joins and makes outer join SQL easier to understand.

LEFT OUTER JOIN
In a LEFT OUTER JOIN, all rows in the left-hand table are returned, even if there is no matching column in the joined tables. In this example, all employee last names are returned, even those employees who are not yet assigned to a department:

Oracle8i
select last_name, dept_id
from emp e, dept d
where e.department_id = d.department_id(+);

Oracle9i
select last_name, dept_id
from emp
LEFT OUTER JOIN Dept
ON e.dept_id = d.dept_id;

RIGHT OUTER JOIN
In a RIGHT OUTER JOIN, all rows in the right-hand table are returned, even if there is no matching column in the joined tables. In this example, all department IDs are returned, even for those departments without any employees:

Oracle8i
select last_name, d.dept_id
from employees e, departments d
where e.department_id(+) = d.department_id;

Oracle9i
select last_name, d.dept_id
from employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);

Join the party
The ISO 99 standard is another example of Oracle’s commitment to enhancing its implementation of SQL. The most popular of these enhancements will be the NATURAL JOIN, which simplifies SQL syntax, and the LEFT OUTER JOIN and RIGHT OUTER JOIN, which eliminate the need for the clumsy (+) syntax.

九佰 edited on 2003-07-25 17:41

作者	回复： [英][转帖] Oracle9i 新的SQL连接语法 [Re:C]
jmd7 CJSDN高级会员发贴: 167 积分: 110	于 2002-08-17 01:54 老大，贴一个connect by的使用说明吧，看你满有心得的样子。我觉得这个新语法比较有意思。另外cube和rollup有的时候也能有救命稻草的作用。有一次我们做报表，那个该死的crystal report不支持这些语法，累死人了，还好是我的同事，当时真可怜他。

作者	回复： [英][转帖] Oracle9i 新的SQL连接语法 [Re:C]
C 发贴: 0 积分: 0	于 2002-08-17 02:13 楼下itsyh的帖子帖的就是Connect by的使用说明呢。

已读帖子

新的帖子

被删除的帖子